DoCmd.TransferSpreadsheet acOutputQuery to excel formatted template is losing data formats

[08211987]

Hi.
I am using Access 2013 and using DoCmd.TransferSpreadsheet acOutputQuery to output a query of a table to an excel template that has all the columns formatted and I call a routine that I use for many different reports. Each report has it own template but they all use the same export procedure (screen shot attached of code). But for just a few the data format is lost after the export.

Below is the code for 1 of the reports that calls the routine:

Sub GTINs_Already_Synched_Submitted_on_VQT()
DoCmd.SetWarnings False
template_file = "\\msfs05.lowes.com\data1\share\MDCM_Reports\Product_Information\PI Item Setup Tracker\Templates\GTINs Already Synched Submitted on VQT.xlsm" '
report_file = "C:\PI_Edgenet_Reports\GTINs Already Synched Submitted on VQT.xlsm"
sheet_name = "GTINs Already Synched Submitte"
FileCopy template_file, report_file
DoCmd.TransferSpreadsheet acOutputQuery, 10, "qry_tbl_Vendor_Quote_Template_Synched_History", report_file, False, "Data1"
Export_Report_For_All
End Sub

Thanks for any assistance!
CF

 

[kjv1611]

thoughts:
[ol][li]You haven't mentioned WHAT data, WHAT formats are changed/different between Access and Excel.[/li]
[li]You need to provide some examples. So "00001" is changing to "1" or what?[/li]
[li]Idea: Have you considered linking your template to the Access database instead of copying/pasting the data? Is there some reason that will not work? If you're using VBA already, you could setup the link to Access, then have VBA code remove the link once you create a new file from the template.[/li][/ol]

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

If you have formatting requirements, I'd PULL the data from Excel voa MS Query, rather than PUSHING the data to Excel.

In Excel Data > Get External Data > From Other Sources... and choose Access and drill down to your file. Your SQL ought to work in MS Query.

Once the QueryTable has been added to your sheet, it can simply be Refreshed. Any formatting remains.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]