Working with Excel and Access VB coding?

  • Thread starter Thread starter wkndufr
  • Start date Start date
W

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.
 
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]...
 
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]...
 
Back
Top