[AccessD] Excel from Access (COM) - Need to read all rows in all sheets

[Brad Marks]

Rocky,

Thanks for the help.

Brad



-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Monday, April 24, 2017 10:03 PM
To: 'Access Developers discussion and problem solving' <accessd at databaseadvisors.com>
Subject: Re: [AccessD] Excel from Access (COM) - Need to read all rows in all sheets

The easiest way to do it is to import it into an Access Table using TransferSpreadsheet.  Then you can treat it just like any access table of data.

Otherwise you can use automation.  Define some objects

Dim objXLApp As Object ' Excel.Application Dim objXLBook As Object ' Excel.Workbook Dim objXLWS As Object ' Excel.Worksheet

Then Set objXLApp = CreateObject("Excel.Application") And objXLApp.Workbooks.Open & "MySpreadsheet.xls"

You can read the individual cells, switch sheets, cycle through the sheets - I've used a lot of automation and anything you can do in Excel you can do to a spreadsheet from Access using VBA.  

You can get started just by Googling Access Automation of Excel.  Or dig up an Access Developer's Handbook.  Very good explanations with code samples.

The best tricj I ever learned was to use the macro recorder in Excel to generate code to do what you want to do from Access, then just crib out the code from Excel and paste into access.'

HTH

Rocky


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Monday, April 24, 2017 6:34 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Excel from Access (COM) - Need to read all rows in all sheets

All,


I experimented with Access/Excel via COM a couple years ago.


I now have been given a large Excel file that has many sheets.  I need to read each row in each sheet.


Does anyone have a example of how to do this?


Thanks,

Brad
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com