Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
shane_spencer
Specialist
Specialist

Macro - Browse for file WITH default location

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

Labels (4)
1 Solution

Accepted Solutions
cwolf
Creator III
Creator III

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

View solution in original post

5 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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
Specialist
Specialist
Author

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
MVP & Luminary
MVP & Luminary

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
Creator III
Creator III

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
Creator III
Creator III

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