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

1 Solution

Accepted Solutions
Highlighted
Not applicable

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

View solution in original post

15 Replies
Highlighted
Not applicable

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

View solution in original post

Highlighted
Not applicable

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

Highlighted
Not applicable

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.

Highlighted
Not applicable

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

next

"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





Highlighted
Not applicable

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.

Highlighted
Not applicable

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

Highlighted
Not applicable

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

Not applicable

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?

Highlighted
Not applicable

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