CODEWITHSUNDEEP
vbams-access
Maggie
Maggie

Reputation: 1

DoCmd.TransferSpreadsheet Issues

I'm having issues importing a specific set of Excel workbooks to a table in Access through VBA using DoCmd.TransferSpreadsheet acImport, 8, TableInput, FilePathName, True

One thing that works is when I open each workbook and re-save them, then the code works, not sure what could be the issue. The file type shows that the workbook is 97-2003, so I use value 8 but that still gives the run time error 3274 unless I do the above. And the code doesn't have issues with other excel file sets. I also don't see any differences between files when using the Document Inspector.

Note, the excel files I'm using are provided by third party companies. Thanks for your help!

Upvotes: 0

Views: 392

Answers (1)

iDevlop
iDevlop

Reputation: 25262

You can try to read an Excel directly from a query: 

SELECT  s.*
FROM [Sheet1$] s IN '' [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=@sourceFile]

Just replace Sheet1$ by the sheet name with a trailing $,
and replace @sourcefile by the full file name.

You can even specify the starting row and col and conditions: 

SELECT  s.*
FROM [Fees$a10:h10] s IN...
WHERE [someField] IS NOT NULL

Upvotes: 1

Related Questions