Saturday, March 14, 2009

Browse-for-File Macro Button

Several times I’ve created QlikView reports that must read data from a file but the name of the file can vary and must be supplied by the report user. Often this is a file that will be used by the loadscript so the user first clicks a macro button that runs a browse-for-file dialog and then the user clicks the standard Reload button.

Here’s an example of a macro that borrows the browse file dialog method from the Microsoft Excel object model. This macro should work on any PC that has MS Excel installed (it does not actually start Excel). You can copy the code from here and paste it into your QlikView report module code:

Sub Browse_for_File
'------------------
' Get the file pathname
Set oXL=CreateObject("Excel.Application")
f_name=oXL.GetOpenFilename("All Files (*.*),*.*",,"Select file",False)
If f_name="False" then
  'user cancelled out of dialog box
  Set oXL=nothing
  Exit sub
End If
'store file pathname in the file_pathname variable
ret=ActiveDocument.GetVariable("file_pathname").SetContent(f_name,false)
Set oXL=nothing
End Sub


This macro stores the file pathname for the file selected by the report user into a document variable named file_pathname (create the new document variable before running the macro). When I create a report that uses this macro I usually also put an input box in the report that shows the file pathname stored in the variable and allows the user an alternative method for specifying the pathname.

A QlikView document example of this technique, named BIG_Text_File.qvw, is available here – http://finmagic.0catch.com/ Sorry about the ads and popups – just click on the specific file download.

9 comments:

Anonymous said...

I try that but get this error: ActiveX component can't create object: 'Excel.Application'

And yes, Excel is installed on this machine

Anonymous said...

same here

Anonymous said...

Sure, it works.
watch your security settings...

got the same message, but after allowing access to external data, it works fine.

Anonymous said...

I can`t find "allow access to external data" option on document properties. Can someone elaborate on this?

Thanks a lot.

-TB said...

for that "allow access to external data": start up the Edit Module dialog (in Version 10 it is under the Tools menu). Then, in the lower left of the screen you will see a setting for "Requested Module Security" and "Current Local Security". Set those to "System Access".

Without making that change QlikView will not allow macro code to access external files.

Anonymous said...

Thanks a lot! and Thanks For sharing!

I was searching on document properties and not in module configuration!

PallaviSwarup said...

Hi,

This is working very well, but do you know how to set focus on te Open dialog box.

Thanks

-TB said...

Hi Pallavi,
Sorry, no. I suspect it may work differently between Windows XP and Windows7.
If you find a way, would you post a comment about it here?
Thanks,
-Tim

Brooks said...

Great Info!!!!!! Great Blog !!!
I added other things that might be helpful for some...breaking the full path into pieces.

'''' Full Path w/ Name
f_pathname=oXL.GetOpenFilename("All Files (*.*),*.*",1,"Select file",False)
''' Just the Folder Path
f_path=Left(f_pathname, InStrRev(f_pathname, "\"))
''' Just the File Name w/ Extension
f_nme=Right(f_pathname, Len(f_pathname) - InStrRev(f_pathname, "\"))
''' Just the File Name w/o Extension
f_fil=Left(f_nme,InStrRev(f_nme,".")-1)
''' Just the File Extension
f_ext=Right(f_nme, Len(f_nme) - InStrRev(f_nme, "."))

''' Added the Variable Set's to match
ret=ActiveDocument.GetVariable("filePathName").SetContent(f_pathname,false)
ret=ActiveDocument.GetVariable("filePath").SetContent(f_path,false)
ret=ActiveDocument.GetVariable("fileName").SetContent(f_nme,false)
ret=ActiveDocument.GetVariable("fileFile").SetContent(f_fil,false)
ret=ActiveDocument.GetVariable("fileExt").SetContent(f_ext,false)