Using command button to importing an Excel File

[Alex Martinez]

Hello,

I am using Access 2002. My users are not techies at all. I have a Excel
file in (H: directory - H:\Access\User\Upload\Audit Upload.XLS This Excel
file will be properly formatted and will have the correct fields before
importing. 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.

 

[Allan Murphy]

Alex
Use TransferSpreadsheet command

In the following example
tbl_cie_raw_data is the table to hold the data from the spreadsheet
D:\availability.xls is the file location
a1:g200 is the range, this optional.

' import from the spreadsheet
DoCmd.TransferSpreadsheet acImport, 8, "tbl_cie_raw_data",
"D:\availability.xls", False, "a1:g200"

I suggest you change your excel filename to Audit_Upload.XLS or
AuditUpload.XLS you may encounter problems with the current filename as
the program will be looking for Upload.xls

 

[Kanga]

Hi,
I also tried this command but I get an error message "Field F1 doesn't exist
in destination table". I have no field called F1 and I'm able to manually
import the table.
Do you know by any chance what is causing this? This would be great help!
Thanks,
Kanga

 

[fredg]

Hi,
I also tried this command but I get an error message "Field F1 doesn't exist
in destination table". I have no field called F1 and I'm able to manually
import the table.
Do you know by any chance what is causing this? This would be great help!
Thanks,
Kanga

Set the 'Has Field Names' argument to True.
Also tell it the name of the worksheet to import from, unless you wish
to import the entire spreadsheet.

DoCmd.TransferSpreadsheet acImport, 8, "tbl_cie_raw_data",
"D:\availability.xls", True, "WorksheetName!a1:g200"

 

[Kanga]

Thank you so much! This is great help!

Set the 'Has Field Names' argument to True.
Also tell it the name of the worksheet to import from, unless you wish
to import the entire spreadsheet.

DoCmd.TransferSpreadsheet acImport, 8, "tbl_cie_raw_data",
"D:\availability.xls", True, "WorksheetName!a1:g200"