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

[Rocky Smolin]

I have lots of automation code scattered around that does different stuff -
but mostly formatting and pushing data out of Access and into Excel.  I also
have some code behind a spreadsheet which pushes data into Access.  That was
fun!

But since (IIUC) you want to get data OUT of a spreadsheet and INTO Access,
once you've got the Excel objects set you can read all the stuff by rows or
columns with two nested Do While loops.

But TransferSpreadsheet's still the easiest.  When a client  has this
requirement, and needs to do it on a regular basis, I first import the
spreadsheet into a new table in the database. That insures the columns are
all there. Then fix up the field names and the data types and it's ready for
use.  I call the Open File Dialog box to get the path and file name.

R


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Brad Marks
Sent: Tuesday, April 25, 2017 1:20 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Excel from Access (COM) - Need to read all rows in
all sheets

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

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