topic SV:Export to excel macro with dynamic name in QlikView

Export to excel macro with dynamic name

Wed, 10 Mar 2010 19:09:36 GMT

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

SV:Export to excel macro with dynamic name

Wed, 10 Mar 2010 23:13:48 GMT

Hi, if I understand your request correctly, you want a macro that sets the name of the saved excel-file dynamically. Hopefully, the code below will do just that and a little more. I have added some comments too, so you may make your own changes accordingly.


sub Export

 set f = activedocument.Fields("YourFieldHere")

 if IsEmpty(f.GetSelectedValues) then
 set pv = f.GetSelectedValues 'adds selected values if any
 else
 set pv = f.GetPossibleValues(1000) 'adds possible values if none selected
 end if

 set Array1 = f.GetNoValues 'empty array
 for i = 0 to pv.count-1 'adds values from the first array
 Array1.Add
 Array1(i).Text = pv.item(i).Text
 Array1(i).IsNumeric = false 'if text in your field
 next

 'Creates a path and a filename of the output xls-file in the same folder as the current document
 Path = ActiveDocument.Evaluate("left(DocumentPath(), index(DocumentPath(), '\', -1))")
 'Path = "C:\Team_Audit\" 'your static path if you dont want a dynamic path
 set obj = ActiveDocument.GetSheetObject("TB01") 'the ID of the object you want to export

 'Loop through each selection in your field
 for i = 0 to Array1.count-1
 f.Select Array1(i).Text 'Selects one value in your field at a time

 'Sets the FileName to be the same as the selection in your chosen field
 FileName = Array1(i).Text & ".xls"

 ' Starts Excel
 set XLApp = CreateObject("Excel.Application")
 ' Makes it run in background
 XLApp.Visible = False
 set XLDoc = XLApp.Workbooks.Add

 ' Set the cell to start in at A1
 Set rngStart = XLDoc.Sheets(1).Range("A1")

 ' Copies the chosen object contents to clipboard
 obj.CopyTableToClipboard true
 ' Paste it into excel
 XLDoc.Sheets(1).Paste()
 ' Save the excel-file with the dynamic path and filename
 XLDoc.SaveAs Path & FileName
 ' Exits the current running Excel
 XLApp.Quit

 next 'goes to the next value in the selection in the chosen field

end sub

Hope this helps!

//Jakob Berglund

SV:Export to excel macro with dynamic name

Thu, 11 Mar 2010 09:17:55 GMT

Hi Jakob,

This is exactly what I wanted and a little more as you suggested!

I will take advantage of the dynamic locations option you have too, tested it and it all worked fantastic!

Many thanks

Peter Lawlor

SV:Export to excel macro with dynamic name

Thu, 13 May 2010 17:31:23 GMT

Jakob,

Thanks for this code it is very useful for what I am trying to do. In addition to this though I would love to be able to paste the active field into an excel cell. I have tried selecting the cell and then copying "FileName" as text but qlikview didn't like that. Do you know of a way to do this? Thanks.

SV:Export to excel macro with dynamic name

Thu, 13 May 2010 19:00:58 GMT

Sean,

Create a folder with an excel file "C:\ExcelFilePaste\ExcelPasteFile.xls".

Load an inline:

LOAD * INLINE [

Code, Number

a, 1

b, 2

c,3];

Create a list box for the "Code" field. Select an item in the code field. Create a button that runs the code below and click the button that runs the code.

Sub SendToExcel

Set oXL=CreateObject("Excel.Application")

set doc = ActiveDocument

set mySelection = doc.fields("Code").GetSelectedValues

for i = 0 to mySelection.Count -1

"C:\ExcelFilePaste\ExcelPasteFile.xls"

set oWB = oXL.Workbooks.Open(f_name)

set oSH = oWB.Worksheets.Item(1)

"A65536"). End(-4162).Offset(1,0).FormulaR1C1 = strIndex

Set oSH=nothing

Set oWB=nothing

Set oXL=nothing

msgbox("Done")

End Sub

Select another item in the list box and click the button again and it will add the new letter selected to the excel file just below the first on you created.

Hope this helps get you started.

Stephen

SV:Export to excel macro with dynamic name

Thu, 13 May 2010 19:13:32 GMT

Stephen,

I'm not sure we are on the same page, right now I am using Jakob's code to spit out 80 or so extract files from a single table with different constraints on it. Each of those constraints has a title which right now is going into an array which will dynamically save the file as that name. All I want to add to this is have that also paste what is becoming the file name into a cell. Right now I am using a workaround in excel but I would prefer to have cleaner code and no macros running in my extracts.

On another note after using this code I have perhaps come across a bug, in this case it will leave the instance of Excel running in my task manager but that will be the only clue that is is running (even if I make it visable) I won't be able to see it. This only happens when I save the file, if I do not save the file then it will close the instance of excel.

SV:Export to excel macro with dynamic name

Thu, 20 May 2010 12:21:20 GMT

Hi, Dear All,

If i want to see all the selected items in a report in the print PDF Preview how can i do it

Please Suggest

Regards,

Pranav

SV:Re: SV:Export to excel macro with dynamic name

Thu, 20 May 2010 13:57:54 GMT

To write additional information in a specific cell inside the newly created document, you may use the code

XLDoc.Sheets(1).Range("A1").Offset(x, y).Value = "CellContentHere"

where A1 represents the cell you want to start your selection and Offset(x, y) represents how many cells away from the start selection you want to write your content. The above sample would first choose A1 as the starting point, then move X cells down and Y cells to the right, and there write CellContentHere.

Of course, you may use

XLDoc.Sheets(1).Range("A1").Offset(0, 10).Value=Array1(i).Text

if you want the "filename" to be written in the cell of your choice as requested. Just change the Offset to suit your needs.

Happy to help!

//Jakob

SV:Re: SV:Export to excel macro with dynamic name

Thu, 20 May 2010 18:53:52 GMT

Thanks Jakob, that was just what I was looking for, this will really help me keep the 600 sheets I need to pull organized.

I do have one more question about your code though. I am not sure if you have noticed this issue and maybe it is just a setting that I have that needs to be changed. 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. This is great however excel doesn't actually quit. It leaves a ghost instance in the task manager that you cannot access in any way but it draws your memory around 50Mb per instance, causing you to force quit after around 30 instances have opened or qlikview will crash due to having no available memory. Any ideas on how to fix this issue?

SV:Export to excel macro with dynamic name

Fri, 21 May 2010 05:24:33 GMT

Thanks Jacob for your Reply,

If i want to print all the selected items of thre reports while printing my PDF at the very bottom is it possibe....

Please Suggest

Regards,

Pranav

SV:Re: SV:Re: SV:Export to excel macro with dynamic name

Fri, 21 May 2010 11:26:28 GMT

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

SV:Re: SV:Re: SV:Export to excel macro with dynamic name

Fri, 21 May 2010 13:45:26 GMT

Jakob,

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

SV:Export to excel macro with dynamic name

Thu, 03 Jun 2010 08:53:12 GMT

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

SV:Export to excel macro with dynamic name

Thu, 03 Jun 2010 09:15:00 GMT

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,

Re: Export to excel macro with dynamic name

Fri, 30 Mar 2012 20:58:54 GMT

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