Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Export to excel macro with dynamic name

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

15 Replies
Highlighted
Not applicable

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


Not applicable

Jakob,

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

Highlighted
Not applicable

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

Highlighted
Not applicable

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,

Highlighted
Not applicable

"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.

Highlighted
Contributor
Contributor

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

I hope you can help me, thanks !!!