Working with Excel and Access VB coding?

[wkndufr]

I have n workbooks each with 7 worksheets. I have an access db to capture
worksheet data. I want to write a vb macro from Access to prompt for a
workbook name (no problem), open the workbook and loop through the 7 sheets
inserting data in the db (problem getting workbook open and worksheet
forcus). Can anyone help? Simply stating I want to open a workbook and then
"loop" through each workseet.

 

[GeoffG]

Have a look at the following help topics:

Transferspreadsheet Method
and
Transferspreadsheet Action

You can specify the Worksheet name in the Range argument.

For example:

' Name of table to import into:
Const strcTableName As String = "Table1"

' Path and name of workbook:
Const strcWBPathName As String = _
"C:\My Workbook.xls"

' Name of Worksheet to import from.
' Notice the exclamation point.
Const strcWSName As String = "Budget!"

DoCmd.TransferSpreadsheet _
acImport, _
acSpreadsheetTypeExcel9, _
strcTableName, _
strcWBPathName, _
True, _
Range:=strcWSName


Geoff





message
news:[email protected]...

 

[Ken Snell MVP]

You'll find code examples that will show you how to do what you want here:
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm