Access TransferSpreadsheet acExport not working consistently

Peter_W

New Member
Joined
Apr 2, 2018
Messages
29
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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,601
Office Version
2013
Platform
Windows
I believe this is for importing, not exporting:
VBA Code:
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.

 

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,840
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"
 

Forum statistics

Threads
1,089,220
Messages
5,406,928
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top