asked on

Access 2016 Import Excel Data

Hi Experts,

I have been using 'Late Binding' to import data froman Excel File to an Access 2016 Table.

Im now trying to create a new import function.

A while ago John Tsioumpris and Mark Edwards suggested the TransferSpreadsheet method.

I have coded it as follows but it doesn't import anything.

DoCmd.TransferSpreadsheet acImport, 10, RS07ImportDataAddGrp0003, "M:\Web\Att\2020-05-01@17.34-Co-op Camp Registration 35th GA.xlsx", False, ""

The Excel Spreadsheet has 2 Columns (A & B) with 121 Rows although in production the number of rows won't be known

Can you tell me what is wrong with the systax of the command?

Thanks,
Bob C.

ASKER CERTIFIED SOLUTION

John Tsioumpris

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Bob Collison

ASKER

Hi John,

I copied your code in verbatum and it still doesn't load anything.

Thanks,
Bob C.

John Tsioumpris

Bob i did gave it a quick test and it was working
Maybe there is more than one mistake....check the path of the excel..
Take note that i took for granted that RS07ImportDataAddGrp0003 is the name of the table not a variable holding the table's name...if not you don't need the double quotes and probably you have some other kind of issue...e.g. corruption (?)

Bob Collison

ASKER

Hi John,

RS07ImportDataAddGrp0003 is a variable containing the Table Name of 07_IMPORT_DATA.

I have added some code to display any errors.
DoCmd.TransferSpreadsheet acImport, 10, RS07ImportDataAddGrp0003, "M:\Web\Att\2020-05-01@17.34-Co-op Camp Registration 35th GA.xlsx", False, ""

This produces Error: 'An expression you entered is the wrong data type for one of...'

DoCmd.TransferSpreadsheet acImport, 10, "07_IMPORT_DATA", "M:\Web\Att\2020-05-01@17.34-Co-op Camp Registration 35th GA.xlsx", False, ""
This produces Error: "Field 'F1' doesn't exist in destination table '07_IMPORT_DATA'....

The last Error is correct. There is no such field.

Thanks,
Bob C.

John Tsioumpris

I am thinking that something is wrong in the Excel...can you try the code with some other Excel ?

Bob Collison

ASKER

Hi John,

Unfortunately I don't have any way to do that.

Instead of using the TrasferSpreadsheet Method to avaoid unecessary delay I am using Late Binding Code that I have been using in the past.

I will keep a reference to this Question and come back to it sometime in the future.

In the meantime I'm going to close it with your suggestion as the solution.

Thanks,
Bob C.