asked on

Save Excel Template with a different anme

I am using this code to take a query and save to a Excel template. What I would like is it not to save to the actual template but to a new excel file with a different name so the template stays intact.

DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel9, TableName:="qry_SectionOne", FileName:="C:\Test\Template.xlsm", Range:="SectionOne"

Bill Prew

Not sure if I understand, but wouldn't you just change the name of the destination file to something different?

DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel9, TableName:="qry_SectionOne", FileName:="C:\Test\NewName.xls"

Open in new window

And for newer versions of Office you can use:

DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel12Xml, TableName:="qry_SectionOne", FileName:="C:\Test\NewName.xlsx"

Open in new window

»bp

Rgonzo1971

HI,

pls try

DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel9, TableName:="qry_SectionOne", FileName:="C:\Test\NewFile.xlsm"

Open in new window

Regards

Pdeters

ASKER

I want to take the Excel Template as it has format etc. and then save it to a new name. The template will be reused and needs to stay blank.

Pdeters

ASKER

I want it the export to use the format that is in the Excel template (has formating etc.) and then save it as a different file name

Professor J

try to put this code, in the template workbook.

the code should be placed in THisWorkbook

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

  
    
    ThisWorkbook.SaveAs ThisWorkbook.Path & Application.PathSeparator & Format(Date, "yyyymmdd"), ThisWorkbook.FileFormat
    ThisWorkbook.Saved = True
    DoEvents
    Application.EnableEvents = True

End Sub

Open in new window

ASKER CERTIFIED SOLUTION

crystal (strive4peace) - Microsoft MVP, Access

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pdeters

ASKER

Yes I am doing this from Access. Is there not a way to take the template and rename from Access?

als315

Yes, you can open template from Access, open your query and loop across lines of query writing them to Excel template. Save it with new name when finished. Main idea is in sample code from crystal's comment.
I've added MS Access to topics

SOLUTION

crystal (strive4peace) - Microsoft MVP, Access

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

als315

If you like to get code, save results of your query to table (only few lines will be enough) and upload DB with this table only (remove any sensitive data). Upload template file also,

Roy Cox

If you are using a proper Excel Template, i.e saved as *.xltx or *xltm then it will automatically open as a new workbook. Any changes will not affect the template.

Richard Daneke

I believe that if you leave the Filename element of the statement out of the code, you will be prompted for a filename and folder location. So delete
FileName:="C:\Test\Template.xlsm", from you code.

Roy Cox

You should actually be opening the template, which will open as Template1.xlsm, this name can be saved. This process will not affect the original 8.xltm

PatHartman

Copy the spreadsheet to the new name. THEN export to the new name.

als315

Author did not pointed Access as a platform, so many comments were made for excel solutions