Import Excel to Acsess using Macro

[KimTong via AccessMonster.com]

Hi,

I have problem in importing Excel spreadsheet (name is 'C:\data\ADHOC' on
'Page1' tab) to Access db (table 'Agreement') in Macro. Mostly, I know how to
put it in Macro, except the tab on excel file.

Transfer Type: Import
Spreadsheet Type: Microsoft Excel 8-10
Table Name: Agreement
Filename: C:\data\ADHOC
Has Fieldname: Yes
Range:

Is anyone can help me, how to put the Excel 'Page1' tab in macro?. Thank you
in advance

KF

 

[Ken Snell \(MVP\)]

You need to declare the cell range along with the page name. As a generic
setup, specify the entire sheet's range (for EXCEL 2003, 2002, 2000, and 97
versions, it's A1:IV65536):

Page1!A1:IV65536

 

[KimTong via AccessMonster.com]

Thank you for the answer. How about if the range is not fixed? We'll import
the excel file once a week, most the time the row is getting more and more.
Should I still can put a whole entire range (A1..IV65656), and Access will
import the row that has the data only?

 

[Ken Snell \(MVP\)]

Try it and you'll see ;-)

Actually, Jet (ACCESS) asks EXCEL for its UsedRange information, and uses
that actual range to do the import if that range is "smaller" than the range
you specify. By specifying the entire sheet's cell range (as I did), you
just ensure that you have a generic expression in the macro and don't need
to worry about how many rows of data there are in the EXCEL spreadsheet.