topic Re: How to save a excel file with filename as current selection in QlikView

How to save a excel file with filename as current selection

jiwaniakbiar — Fri, 16 Nov 2012 20:13:18 GMT

I need help to save excel with Current Selection as file name. Also if there is nothing selected, it should save as default name

I tried to setup a variable which will hold current selection value and use it in the macro but it doesn't work.

Varialbe declared in Document Properties as:

vSelection = (replace(GetCurrentSelections(),': ','_')

and Macro is:

set v = ActiveDocument.Variables("vSelection")

Selection = v.GetContent.String

Path = "C:\qlikview\"

FileName = Selection & "_Report" & ".xlsx"

ActiveDocument.Sheets("REC Dashboard").Activate

MsgBox(FileName)

objExcelWorkbook.SaveAs Path & FileName

MsgBox("File Saved Successfully in C:\qlikview\"+Selection+".xls")

It works for a single fiield is selected. If I have more than one Field selected, it doesn't work because vSelection sends string in two lines

Re: How to save a excel file with filename as current selection

Gysbert_Wassenaar — Fri, 16 Nov 2012 20:21:37 GMT

Try:

vSelection = replace(replace(GetCurrentSelections(),': ','_'),chr(13)chr(10),'_')

Re: How to save a excel file with filename as current selection

jiwaniakbiar — Sat, 17 Nov 2012 06:56:01 GMT

It worked. Thnx. However can you please help me what exactly you did with chr(13) and chr(10)?

Re: How to save a excel file with filename as current selection

Gysbert_Wassenaar — Sat, 17 Nov 2012 07:01:07 GMT

Getcurrentselections inserts a linebreak between the results of different fields. The linebreak consists of two ascii characters, a Carriage Return (chr 13) and a Line Feed (chr 10). I replaced them with an underscore '_'.

Re: How to save a excel file with filename as current selection

jiwaniakbiar — Sat, 17 Nov 2012 07:14:01 GMT

Also I used following to default 'No Selection' to filename 'Overall'

= IF((Nullcount (SubStringCount( GetCurrentSelections(':'),':'))),'Overall',replace(replace(GetCurrentSelections(),': ','_'),chr(13)&chr(10),'_'))