Solved Export to Excel Template and Save As (1 Viewer)

pooldead

Registered User.
Local time
Today, 03:38
Joined
Sep 4, 2019
Messages
136
I am trying to export data from a recordset to an Excel template. This Excel file has macros that assist my users in managing the data they receive.

I have no problem doing a straight TransferSpreadsheet to a new Excel file, but I have been unsuccessful in getting the data to the existing template, then saving as a new file so the template can be used throughout the recordset.

I don't really have any code to share at the moment, I just need some advice or guidance.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:38
Joined
Feb 28, 2001
Messages
27,001
To use an existing template, you might need to use an Excel Application Object (which is a topic you can look up using the forum Search feature).

The catch is that I believe you cannot name TWO files here - the template and the desired output file - and therefore, TransferSpreadSheet won't save your template intact.

While it might be a pain in the toches to do this, you might consider something like this:

1. Open the template using an Excel Application Object. I.e. open the object, then tell it to open the template as a workbook, then make the desired spreadsheet become the ActiveSheet.

2. Open a recordset to your data. This can be a query or a table as your source, it doesn't matter.

3. In a loop, fill in the cells of the spreadsheet. In an Excel App Object, you can refer to any .Spreadsheet object (including the one you activated in step 1) which has a collection of .Rows and within each row, you have a collection of .Cells. You can thus random-access the rows and columns. Just remember to keep incrementing the row pointer every time you update the recordset position via .MoveNext so that your data keeps in sync.

4. At the end of the loop, you can Close the workbook with the SaveAs option.

The AWF has lots of articles on this topic with code samples so I won't contrive anything. Just use the forum search, which is on the thin menu bar near the top of the web page, 3rd item from the right.

EDIT: I see Gasman even provided a link that has some sample code to look at for a similar problem.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:38
Joined
Feb 28, 2001
Messages
27,001
Hey, bastanu - good suggestion! It would replace my step 3 in that loop with a non-looping operation. Faster and easier.

It's also an option I never had occasion to use in my Navy projects so I was unaware of it.
 

pooldead

Registered User.
Local time
Today, 03:38
Joined
Sep 4, 2019
Messages
136
Thanks everyone! The_Doc_Man's suggestion did the trick. I appreciate all the help (as always!!)
 

Users who are viewing this thread

Top Bottom