Using macros to Import Excel file

[Alex Martinez]

Hello,

I am using Access 2002 and I am not sure if using a macro will be the best
way to go. My users are not techies at all. I have a Excel file in (H:
drive) goes like this H:\Access\User\Upload\Audit Upload.XLS The Excel
file will be properly formatted and will have the correct fields. Can
someone give me the code in importing the Excel file or point to me a
website or reference book. The Excel file will be importing to Access -
H:\Access\User\Audit.mdb. I want to have a command button so the user simply
press the button and the Excel file will be imported and acknowledge that
the import has been done so the user don't press the button again.. Any
tips or help will be appreciated. Thank you.

 

[Michael Shannon]

Create a macro and use the transferspreadsheet function followed by the
MsgBox function to let them know it has worked.

Michael

 

[rob]

Alex,

The TransferSpreadsheet function allows you to create a new TABLE, or will
allow you to APPEND data to a table (If the table already exists). So of
course that means every time you add data (by pressing the COMMAND button)
the table grows (and duplicates information).

expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName,
FileName, HasFieldNames, Range, UseOA)

One way to get around this is to write a function that closes (if open) then
deletes the table prior to appending all of your excel sheets into your
database. My data was derived from 11 different spreadsheets all located in
one folder -- my personnel only had access to their own excel file...it
works perfectly.

Just my 2 cents

Rob F.

 

[Alex Martinez]

Michael/Rob,

Thanks for your help now I have a better understanding and where to go.
Again thanks for the insight.