Issue with TransferSpreadsheet

[bartw72]

Some of my users are experiencing an issue with the TransferSpreadsheet function. I have a form that they use to import a spreadsheet into an existing table. The header of the spreadsheet doesn't always match the names in the table, but the columns are always in the right order. The total number of rows varies, so I have to paste a new header onto the spreadsheet before I import the entire spreadsheet. When I do, sometimes the data in the spreadsheet gets corrupted and the import fails. What's frustrating about this is that it happens only for some users. It does NOT occur if they have the spreadsheet open before I try to run the below code. My guess is that there is some user level macro or security setting in Access or Excel that is causing this. Any thoughts?

' Copy Template header to FilePath Doc and Save before importing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblGlobalShell", strFilePath, False, "A1:W1"

' Imports data from spreadsheet to tblGlobalImport
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "tblGlobalImport", strFilePath, True, "A:W"

 

[mattkorguk]

I went down a similar path.
I then tried importing the entire spreadsheet with no headers and then used an append query to add the list to an existing table with all the headers in place.
Maybe that might give you a little way around it?
Hope that helps.

 

[bartw72]

Append

Great suggestion. Why didn't I think of that!

 

[mattkorguk]

Sometimes it's the simple things...

Hope that works out for you.

 

[gemma-the-husky]

its awkward - sometimes you may need to check the column headers to make sure theyve loaded the correct file!