0

I use some vba to export the results of a select query. The selected data needs to be exported 1 on 1 to Excel. This morning I faced the following error:

3274 on DoCmd.TransferSpreadsheet, External table is not in the expected format

Inspecting the source data, I've found a row containing a leading Apostrophe. Removing this row from the source, solved the error. However, I need this row to be exported as well. Is there any setting to solve this problem?

This is the VBA:

lSQL = ""
lSQL = lSQL & "select a.* from table As e "
Set lQD = lDB.CreateQueryDef("Test", lSQL)

DoCmd.TransferSpreadsheet A_EXPORT, 10, "Test", FileName, True
Sunfile
  • 101
  • 1
  • 4
  • 22

1 Answers1

0

Replace table (or lSQL) with a simple select query.

In this query, sanitize the field contents using my CSql function or similar. It will double single-quotes which, I believe, should be the cure.

Gustav
  • 53,498
  • 7
  • 29
  • 55