INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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

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

thoughts:
  1. You haven't mentioned WHAT data, WHAT formats are changed/different between Access and Excel.
  2. You need to provide some examples. So "00001" is changing to "1" or what?
  3. 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.

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

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close