Access TransferSpreadsheet acExport not working consistently

[Peter_W]

Hello,

I am using Access VBA TransferSpreadsheet acExport to export multiple queries to new spreadsheets and different sheets within those files. Generally, its been working really well.

However, one of the queries, repeats the same query results, over and over, when each query recordset is unique. It is the qryCostData2, per below.

My hunch is that varying numbers of rows in the different queries are the problem. So, will a query with 20 rows of data, have a problem, when the previous range is only 15 rows? Are the ranges getting mixed up somehow? I've tried numerous attempts with different code and still have one query which repeats itself over and over, within the Excel exports. The VBA is looping through many multiple query recordsets.

Would anyone have idea what's wrong? Thank you in advance for any feedback.

Sub ExportToExcel_Loop()

'This exports Access data to the Excel target file:

Dim strFilename As String

strFilename = "C:\TestTargetFile.xlsx"


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryCostData2", strFilename, True, "CostRange"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryProjMetadata1", strFilename
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryProjMetadata2", strFilename
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryProjMetadata3", strFilename
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryProjMetadata4", strFilename
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryProjMetadata5", strFilename
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryProjMetadata6", strFilename

Call cpyFile_loop

End Sub

 

[xenou]

I believe this is for importing, not exporting:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryCostData2", strFilename, True, "CostRange"

meaning, when I look at the docs for the transferspreadsheet action it states that if you use the last parameter (where you are supplying the value "CostRange") then the export will fail. But it doesn't fail, it sort of fails. So I guess either way I wouldn't trust it or use it.

DoCmd.TransferSpreadsheet method (Access)

Office VBA reference topic

docs.microsoft.com

 

[ranman256]

if the 1st transfer is 50 recs,
then the 2nd transfer to the same sheet is 20 recs, you will get 20 NEW recs, but the other 30 will remain. It does not erase records.
so either:
1. delete the target file and the transfer will be a new file
or
2. transfer to a different sheet in the existing file....strFilename, True, "SHEET n"