topic Macro - Browse for file WITH default location in QlikView

Macro - Browse for file WITH default location

shane_spencer — Sat, 16 Nov 2024 18:16:14 GMT

I'm using the below macro so the use can pick a file to load into QlikView. I want to set a default location rather than the standard "Documents" to save the user navigating to where the file(s) are located. Is this possible? I will be using a variable based on the location of the QV document and some selections.

ub 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=nothin

http://qlikviewmaven.blogspot.com/2009/03/browse-for-file-macro-button.html

Re: Macro - Browse for file WITH default location

marcus_sommer — Tue, 04 Aug 2020 11:20:09 GMT

After a quick search it seems that's not (so easy) possible but it looked that there are additionally and/or alternative measures available. For this take a look here:

https://www.mrexcel.com/board/threads/setting-a-default-path-in-application-getopenfilename.565906/

https://www.mrexcel.com/board/threads/getopenfilename-default-directory.574110/

- Marcus

Re: Macro - Browse for file WITH default location

shane_spencer — Tue, 04 Aug 2020 12:16:59 GMT

I've already seen and tried both of them. The ChDrive and ChDir don't appear to be valid commands in QV Macros. And the Function has an syntax problem.

Re: Macro - Browse for file WITH default location

marcus_sommer — Tue, 04 Aug 2020 12:56:24 GMT

Yes, they won't work there directly but the logic might be an option by splitting this task and outsourcing some of them (for example executing a cmd for it which writes the chosen file into any parameter/variable-file which is then already taken from the QV macro). I don't think that's a really nice way ... but probably possible ...

But the suggestion from the second link using the "msoFileDialogOpen" might be easier to implement.

- Marcus

Re: Macro - Browse for file WITH default location

cwolf — Tue, 04 Aug 2020 13:29:25 GMT

Sub Browse_for_File ' Set default path defaultPath=ActiveDocument.GetVariable("default_path").GetContent().string set oXL=CreateObject("Excel.Application") if lcase(oXL.DefaultFilePath)<>lcase(defaultPath) then oXL.DefaultFilePath=defaultPath 'Close and reopen to make the the new default path available oXL.Quit set oXL=CreateObject("Excel.Application") end if ' Get the file pathname f_name=oXL.GetOpenFilename("All Files (*.*),*.*",,"Select file",false) if lcase(f_name)="false" then f_name="" end if oXL.Quit ret=ActiveDocument.GetVariable("file_pathname").SetContent(f_name,false) end sub

Re: Macro - Browse for file WITH default location

cwolf — Tue, 04 Aug 2020 16:18:40 GMT

Following the link of Marcus, this is also a good solution:

sub Browse_for_File defaultPath=ActiveDocument.GetVariable("default_path").GetContent().string f_name="" set oXL=CreateObject("Excel.Application") With oXL.FileDialog(1) .ButtonName = "&Open" .initialFilename = defaultPath .Filters.Clear .Filters.Add "All Files (*.*)", "*.*", 1 .Filters.Add "Excel Files (*.xlsx)", "*.xlsx", 2 .Filters.Add "CSV Files (*.csv)", "*.csv", 3 .Title = "Select file" .AllowMultiSelect = False If .Show = -1 Then f_name = .SelectedItems(1) End With oXL.Quit ret=ActiveDocument.GetVariable("file_pathname").SetContent(f_name,false) end sub