import excel spreadsheet

[hawg1]

Hi,

I have been trying to import and excel spreadsheet into access 2007 via a command button with no luck. If I use the external data wizard it imports Ok.

Can anyone help me with the code? My source excel file is in the documents folder and is titled 'new wucs'. The destination table is called 'new wucs'.

I have used this line of code with no luck:

DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "new wucs", "C:\Users\Kenneth.Hurst\Documents\new wucs.xlsx", True

I appreciate any help on the subject.

Thanks

 

[ghudson]

Import the file into a new table manually, then reuse that table as the template for your new wucs table. Inspect the new table you first created to see if ther are any extra columns being created and/or any unexpected data formats that access defines for the imported fields in your table. Either of those will cause the import to fail.

 

[boblarson]

You should probably add the acImport to the code:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "new wucs", "C:\Users\Kenneth.Hurst\Documents\new wucs.xlsx", True

Oh, and I just saw that you specified acSpreadsheetTypeExcel9 which is NOT a 2007 format. You are importing an xlsx file so you can't use acSpreadsheetTypeExcel9.

It would be

acSpreadsheetTypeExcel12

 

[hawg1]

You should probably add the acImport to the code:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "new wucs", "C:\Users\Kenneth.Hurst\Documents\new wucs.xlsx", True

Oh, and I just saw that you specified acSpreadsheetTypeExcel9 which is NOT a 2007 format. You are importing an xlsx file so you can't use acSpreadsheetTypeExcel9.

It would be

acSpreadsheetTypeExcel12

Boblarson,

I fixed what you suggested/found. It still dis not work. So I decided to delete the database and start over. Once I created a new database, tables and command button, it all worked OK. However, once I saved the database, closed it and reopened it, it did not work anymore.

Any suggestions as to what could be going on?

 

[hawg1]

All,

I think I figured out the problem. My security levels were set to aotmatically disable VBA and macros. Changed the setting and now everythign works fine.

Thansk for the help, I appreciate it!

Hawg1

 

[boblarson]

Cool, glad you figured that out.