Easy way to export to Excel with unique file name based on selection?

[#1]

I've seen a couple of examples doing this with macros. Before I get into all that, I wondered "Is there an easy way to specify the file name using the selected value of the "sales rep" field, and the "startdate" variable?"

[#2]

You can refer to the selection using a variable.  See attached example. Note that I have it set so that only one sales rep will be selected at a time in my example.

[#3]

Unfortunately, I have at least six selections active on the document. So, how do I pass the selected value of the field "Sales_Rep" to my macro? I tried:

uFile = "c:\test\qv test\" & Sales_Rep & ".xlsx", but that just exported to:

"c:\test\qv test\.xlsx" - it didn't pick up Sales_Rep at all. How do I pass the selected value of a specific field to the macro?

[#4]

What do you have set in your variable?  It looks like it isn't picking up your variable.  Try setting your variable as follows:

=GetFieldSelections(Sales_Rep,'.')

Also double check that the variable name is the exact same.  I named mine "vSelection", but yours can be different.

[#5]

See if the attached example makes it any clearer. I edited the code to allow for more than one selection in the Sales Rep field; if the user does not make any selections then it defaults to 'All Sales Reps'. 

[#6]

Sales_Rep is not a variable. It is one of the fields on my document. do I have to go to the intermediate step of creating another variable, and setting it equal to the value of Sales_Rep?

I tried what you suggested, but while my system accepted GetFieldSelections(Sales_Rep) (but still didn't do anything), it didn't like it at all when I appended the ,"." to it - that got highlighted in green and when I tried to save told me macro parser failed.

And I looked at the QV API Guide - what wanker designed that? It's the type of tool for someone who already knows what they're doing. All I want is the basic syntax and a few examples of a simple command, and I can't find it anywhere. Here's what I've written

SUB SmartExport
dim obj
dim uFILE
dim repname
set obj = ActiveDocument.GetSheetObject("CH13")
set repname = GetFieldSelections(Sales_Rep)
set uFILE ="E:\ADMIN\USERS\Janie\" & repname &".xlsx"
obj.ExportBiff uFILE
msgbox("Success!")
end sub

It's nowhere near as ambitious as what you're doing, but I'm just trying to help out the admin assistant from having to save 15 separate excel files every morning, where she

1 - selects each rep individually in the listbox "Sales_Rep"

2 - Click on "XL" to export it

3 - Go to the Excel file

4 - Select "enable editing"

5 - Select "save as"

6 - Type in the sales rep's code and the current date

7- Selects "save"

Repeat that 15 times every day, and I'm sure you can appreciate how boring it is. I'm just trying to save her those steps so she click the SmartExport button on the sheet, select the next Sales_Rep, click SmartExport,etc - much faster and simpler. 

[#7]

You will need to make the field selections a variable because it doesn't understand what you are trying to do when you put it in the macro.  Comment out what you have and try the following:

First, go to Settings > Variable Overview > Click 'Add' > Name your variable vSelection:

Then make the variable equal to the following (where it says SalesRepName in red, change it to your field name):

Now enter the following code into Tools > Edit Module:

Hope that helps!! Let me know if you are confused by anything. It's definitely not easy, since there isn't much info out there about macros in QlikView.  My knowledge of it really just comes from writing macros in Excel, and even then my skills are limited.

[#8 · Accepted solution]

Also, don't forget to change the name of the macro, sorry. It would be:

sub SmartExport

set v = ActiveDocument.Variables("vSelection")
Selection=v.GetContent.String

Path = "E:\ADMIN\USERS\Janie\"
FileName = Selection & "_" & ".xlsx"

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = False
set XLDoc = XLApp.Workbooks.Add

ActiveDocument.GetSheetObject("CH13").CopyTableToClipboard true
XLDoc.Sheets(1).Paste()

XLDoc.Sheets(1).Name = "Export"
XLDoc.SaveAs Path & FileName
MsgBox("Success!")

end sub

[#9]

Thanks a lot for your assistance. You went above and beyond!

[#10]

Oh, one thing you didn't mention, and that I found out.. you have to enable "System Access" on requested module security and "Allow system access" on local security, otherwise actions like "CreateObject("Excel.application") aren't allowed. After that, it worked great!