Topic
  • 4 replies
  • Latest Post - ‏2013-01-08T21:46:38Z by SystemAdmin
SystemAdmin
SystemAdmin
1061 Posts

Pinned topic VBA and OpenFile dialog box

‏2013-01-08T19:19:09Z |
I'd like to write some VBA that opens a file dialog box from within SA. The user will be able choose an associated excel file to open from that point. Any idea on which objects I would use for that?
Updated on 2013-01-08T21:46:38Z at 2013-01-08T21:46:38Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    1061 Posts

    Re: VBA and OpenFile dialog box

    ‏2013-01-08T19:54:12Z  
    I use the MS Office Object Library and the following user-function to allow selection of files.

    Public Function RetrieveFiles(ByRef selectedFiles As Scripting.Dictionary, _
    ByVal initFolder As String) As Boolean

    'selectedFiles is a scripting.dictionary used to hold the selected files
    'initFolder is the default folder for the file dialog

    Dim fd As FileDialog
    Dim fileSelected As Variant
    Dim fileList As New Scripting.Dictionary
    Dim fileChosen As Integer

    Set fd = myWord.FileDialog(msoFileDialogFilePicker)
    With fd
    .AllowMultiSelect = True 'set this to false if you want to limit selection to only one file
    .InitialView = msoFileDialogViewList
    .InitialFileName = initFolder
    End With

    fileChosen = fd.Show
    If fileChosen 0 Then
    RetrieveFiles = True
    For Each fileSelected In fd.SelectedItems
    selectedFiles.Add fileSelected, fileSelected
    Next
    Else
    RetrieveFiles = False
    End If

    End Function
  • SystemAdmin
    SystemAdmin
    1061 Posts

    Re: VBA and OpenFile dialog box

    ‏2013-01-08T20:36:01Z  
    I use the MS Office Object Library and the following user-function to allow selection of files.

    Public Function RetrieveFiles(ByRef selectedFiles As Scripting.Dictionary, _
    ByVal initFolder As String) As Boolean

    'selectedFiles is a scripting.dictionary used to hold the selected files
    'initFolder is the default folder for the file dialog

    Dim fd As FileDialog
    Dim fileSelected As Variant
    Dim fileList As New Scripting.Dictionary
    Dim fileChosen As Integer

    Set fd = myWord.FileDialog(msoFileDialogFilePicker)
    With fd
    .AllowMultiSelect = True 'set this to false if you want to limit selection to only one file
    .InitialView = msoFileDialogViewList
    .InitialFileName = initFolder
    End With

    fileChosen = fd.Show
    If fileChosen 0 Then
    RetrieveFiles = True
    For Each fileSelected In fd.SelectedItems
    selectedFiles.Add fileSelected, fileSelected
    Next
    Else
    RetrieveFiles = False
    End If

    End Function
    Thanks Phil - I was thinking along those lines, but with the 'fd as FileDialog' statement, it is saying "User-Defined Type not defined". Somehow it is not able to reference the MS Office library? The intellesense doesnt list it as a option either.
  • SystemAdmin
    SystemAdmin
    1061 Posts

    Re: VBA and OpenFile dialog box

    ‏2013-01-08T21:23:34Z  
    Thanks Phil - I was thinking along those lines, but with the 'fd as FileDialog' statement, it is saying "User-Defined Type not defined". Somehow it is not able to reference the MS Office library? The intellesense doesnt list it as a option either.
    Make sure you include the reference to the office object library (shown in the attached file). Your version might be different.

    Since you know you just want to select/open an excel file, you could just reference the Excel object library - I think the filedialog object is also included there.
  • SystemAdmin
    SystemAdmin
    1061 Posts

    Re: VBA and OpenFile dialog box

    ‏2013-01-08T21:46:38Z  
    Make sure you include the reference to the office object library (shown in the attached file). Your version might be different.

    Since you know you just want to select/open an excel file, you could just reference the Excel object library - I think the filedialog object is also included there.
    Wow - thank you Phil. I am still new to this and didn't know I could make those references through that dialog. There were a ton of libraries listed. It looks like I have Microsoft 14 libraries, not sure how that will impact what we're doing. I'll give it a try tomorrow and see what happens. Thanks!