Export to excel macro with dynamic name

[#1]

Hi,

We run an audit file for many clients and it produces a team list for each client. From this I hope to have a macro that will export an excel document based upon the client I select and save the document away with a static path eg. C:Team_Audit\ & <field name selected> automatically.

Currently I need several macros and I need to pick the client name from a field then click the corresponding macro so that it is saved away in the same location as the rest, but then saved as the client name hard coded in quotes within the macro. New clients come along frequently and as such I need to re-write a new macro. I would prefer one button that saves same location again and again, but will store away the excel name based upon a specific field record I select.

Does anyone have a solution they could share?

Thanks in advance

Peter

[#2]

Hi again!

The previous code is missing the drop of instanced com-objects which should be like follows(starting from XLDoc.SaveAs so you know where to start copy-paste):

' Save the excel-file with the dynamic path and filename
 XLDoc.SaveAs Path & FileName
 ' Closes the active document
 XLDoc.Close
 ' Close the instanced object references of excel
 set rngStart = nothing
 set XLDoc = nothing
 ' Exits the current running Excel
 XLApp.Quit
 ' Closing final object reference
 set XLApp = nothing
next
end sub

[#3]

Jakob,

This is great. I can't thank you enough this is going to save me tons of time

[#4]

Hi,

I am a little stuck on how to get the Active Object on an Active sheet ... the below works and but would like it to be dynamic according to what the Object the user has selected.

ActiveDocument.GetSheetObject("TB01")

I hope it might be something like ActiveDocument.ActiveSheet.ActiveObject but that does not work.

Thanks

Lee

[#5]

Hi Lee

The problem here is that a sheet can have many objects active at any one time, so you are going to be looking at returning an array of information for this.

SET Sht = ActiveDocument.ActiveSheet
OBJS = Sht.GetActiveSheetObjects.SheetObjects

You could then set up a loop over those objects and do what you need to do, for example:

for i = lbound(objs) to ubound(objs)
objs(i).Minimize
next

You can look through the API Guide (API.QVW) to give you more of an idea on what other things you can do once you have the array available.

Regards,

[#6]

"I have taken your code and modified it to open a specific template that then runs an excel macro to format it into the way I need to look at my numbers for printing, saves as and then quits."

Can you detail how you did this? I am looking to do the exact same thing.

[MiguelSoto123]

What if I want to export multiple charts and save them in the same excel file?

I hope you can help me, thanks !!!