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 · Accepted solution]

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

[#3]

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

[#4]

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.

[#5]

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

[#6]

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.

[#7]

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

[#8]

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

[#9]

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?

[#10]

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