Exporting Data to Excel Template But Saves Under a Different Name

ashley25

Registered User.
Local time
Today, 18:41
Joined
Feb 17, 2013
Messages
46
I am able to use DoCmd.TransferSpreadsheet to export data from Access to Excel, however, I want to be able transfer data into a specific sheet within an Excel template (e.g. Tasking.xls), that will then save under a different name (e.g. Tasking 20140429.xls). The other sheets within the Excel template contain pivots etc. so they will need updating during this process. The Excel template should just close down and remain in its original format.

This process will occur once a week so the dates will have to change accordingly.
 
Why not copy the Template file to the desired location and file name and use the newly created file to export the data into?
 
Usually to play with spreadsheets via access you need to bind them together by defining objects in VBA to represent things in excel.

Here is how I would do something like this:

Code:
Dim X As Object
Dim Y As Object
Dim XL As Object
Dim rs As RecordSet

Set X = CreateObject("Excel.Application")
Set Y = X.Workbooks.Open([Filepath to your template])
Set XL = Y.Sheets("[NameofSheettopasteinto]")
Set rs = CurrentDb.OpenRecordset("[Table/Query with info to dump]")

XL.Range("A2").CopyFromRecordset rs ' pastes data to your sheet
' note that this doesn't paste any filed names in, just data, so will want to
' have the field names already there in your template

Y.SaveAs [Path to save to] & "Tasking " & Format(Date,"yyyymmdd") & ".xls"

X.Visible = True

Set X = NOthing
Set Y = Nothing
Set XL = Nothing
rs.Close
Set rs = Nothing
 
It says I need to define external location at this point Y.SaveAs ["G:\My Documents\ICE\Tasking Excel Template\"]
 
great this works. is it possible to run this code without opening the excel file?
 
Change the X.Visible = True to X.Visible = False.
 
this works great. at what point can I hide the worksheet with the new data?
 

Users who are viewing this thread

Back
Top Bottom