Macro - Browse for file WITH default location

[shane_spencer]

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

[marcus_sommer]

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

[shane_spencer]

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.

[marcus_sommer]

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

[cwolf · Accepted solution]

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

[cwolf]

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