Using macros to Import Excel file

  • Thread starter Thread starter Alex Martinez
  • Start date Start date
A

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.
 
Create a macro and use the transferspreadsheet function followed by the
MsgBox function to let them know it has worked.

Michael
 
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.
 
Michael/Rob,

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