Solved Weird TransferSpreadsheet Issue

[EzGoingKev]

I created a function to export five Access queries out into five sheets in the same Excel 2016 workbook using:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QueryName", C:\Path\ExcelFileName, , "SheetName"

Sometimes I run it and it runs fine. It creates the workbook and makes all five sheets. No issues.

Other times I run it and get "Run-time error '3274': External table is not in the expected format." I hit debug and it usually chokes on exporting query two, three, or four (it varies). In these case it creates the workbook and exports whatever sheets up until it chokes.

Any ideas on why it runs fine sometimes and will not run successfully other times?

 

[Isaac]

Could you post your full code routine?

 

[EzGoingKev]

Option Compare Database

Public Function ExportToExcel()

Dim strPath As String
strPath = "C:\Users\MyName\Desktop\fi_cv_polk_" & Format(Date, "mm_dd_yyyy") & ".xlsx"

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "42_aces_detail_no_ids", strPath, , "vio_by_aces_apps"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "43_vio_all_pns_consolidated_make", strPath, , "vio_all_pns_consolidated_make"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "44_vio_bbb_pns_consolidated_make", strPath, , "vio_bbb_pns_consolidated_make"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "45_vio_all_pns_no_make_info", strPath, , "vio_all_pns_no_make_info"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "46_vio_bbb_pns_no_make_info", strPath, , "bbb_pns_no_make_info"
   

End Function

 

[Isaac]

When you run this code, is the workbook represented in strPath already existing? Was it just created prior to this by other code?

 

[theDBguy]

How large are each query? I wonder if the code is tripping over itself.

 

[EzGoingKev]

When you run this code, is the workbook represented in strPath already existing? Was it just created prior to this by other code?

The workbook is not already existing. It is created by the first TransferSpreadsheet function.

How large are each query? I wonder if the code is tripping over itself.

They are not really that big or complex. When you run them in Access they open right up. Each query has one calculated field where it is calculating the sum of some fields in a table.

The Excel workbook with all the exports is only 24 KB.

 

[Isaac]

The workbook is not already existing. It is created by the first TransferSpreadsheet function.


They are not really that big or complex. When you run them in Access they open right up. Each query has one calculated field where it is calculating the sum of some fields in a table.

The Excel workbook with all the exports is only 24 KB.

Okay, thanks for posting.

Reason I ask, I wonder if it might be a good idea to have some pauses in between each of them, especially the first one.

I use this crude and simple function which works for passed-in number of seconds 1 to 9:

Sub MakecodeWait(strSeconds As String)
Dim dtNow As Date
dtNow = Now()
Do Until Now > dtNow + TimeValue("00:00:0" & strSeconds)
    'do nothing
Loop
End Sub

And call it like this:

...some code
MakeCodeWait "3"
...some code
MakeCodeWait "3"

 

[Minty]

Try adding DoEvents between each transfer.
It may be that the VBA is trying to close or reopen the spreadsheet before it's finished the previous operation.

 

[EzGoingKev]

Thanks for the replies guys.

I changed up some of the queries so they use less resources and it has been running fine. If I have the issue I will add the MakeCodeWait sub.

One question I do have is when you get an error like that is there a way to know if the coding is bad vs too many resources being expended?

 

[Isaac]

One question I do have is when you get an error like that is there a way to know if the coding is bad vs too many resources being expended?

To me the error almost sounded like at the time that line of code executes, the xlsx file is in kind of an uncertain or unstable state, generally. But I am not sure.