Access TransferSpreadsheet acExport not working consistently

Peter_W

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

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. 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
2,173
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"
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,331
Messages
5,836,683
Members
430,444
Latest member
WrenchBoy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top