none
Looking for code to open windows explorer to select file. RRS feed

  • Question

  • I would like to have VBA module that allows a user to open Windows Explorer from their Word document to browse for an Excel file. The idea is that after they have selected the file, another module will copy certain cells from Excel into the Word document.

    I tried this code but it didn't work. GetOpenFilename isn't an option with Application. What is the equivalent in Word VBA?

    Sub sbVBA_To_Open_Workbook_FileDialog()
    Dim strFileToOpen As String

    strFileToOpen = Application.GetOpenFilename _
    (Title:="Please choose a file to open", _
    FileFilter:="Excel Files *.xls* (*.xls*),")


    If strFileToOpen = False Then
        MsgBox "No file selected."
        Exit Sub
    Else
        Workbooks.Open Filename:=strFileToOpen
    End If

    End Sub

    Tuesday, June 2, 2015 5:11 PM

Answers

  • You can use the following function to select a Word (or Excel) file from Word (or Excel) VBA

    Function BrowseForFile(Optional strTitle As String, Optional bExcel As Boolean) As String
    Dim fDialog As FileDialog
        On Error GoTo err_Handler
        Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
        With fDialog
            .Title = strTitle
            .AllowMultiSelect = False
            .Filters.Clear
            If bExcel Then
                .Filters.Add "Excel workbooks", "*.xls,*.xlsx,*.xlsm"
            Else
                .Filters.Add "Word documents", "*.doc,*.docx,*.docm"
            End If
            .InitialView = msoFileDialogViewList
            If .Show <> -1 Then GoTo err_Handler:
            BrowseForFile = fDialog.SelectedItems.Item(1)
        End With
    lbl_Exit:
        Exit Function
    err_Handler:
        BrowseForFile = vbNullString
        Resume lbl_Exit
    End Function

    Call this from your macro, however you cannot open an Excel file in Word so you will need to run Excel from your macro in order to open it in Excel e.g.

    Sub sbVBA_To_Open_Workbook_FileDialog()
    Dim strWorkbookname As String
    Dim xlApp As Object
    Dim xlBook As Object
        strWorkbookname = BrowseForFile("Select Workbook", True)
        If strWorkbookname = "" Then
            MsgBox "No file selected."
            GoTo lbl_Exit
        Else
            On Error Resume Next
            Set xlApp = GetObject(, "Excel.Application")
            If Err Then
                Set xlApp = CreateObject("Excel.Application")
            End If
            On Error GoTo 0
            Set xlBook = xlApp.Workbooks.Open(Filename:=strWorkbookname)
            xlApp.Visible = True
        End If
    lbl_Exit:
        Set xlApp = Nothing
        Set xlBook = Nothing
        Exit Sub
    End Sub
    


    Graham Mayor - Word MVP
    www.gmayor.com

    • Marked as answer by TWIKLE Wednesday, June 3, 2015 2:59 PM
    Wednesday, June 3, 2015 8:04 AM

All replies

  • Hi TWIKLE,

    To select the files by using VBA code, we could use FileDialog method.

    Simple code:

    Sub Example1()
    Dim intChoice As Integer
    Dim strPath As String
    
    'only allow the user to select one file
    Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
    'make the file dialog visible to the user
    intChoice = Application.FileDialog(msoFileDialogOpen).Show
    'determine what choice the user made
    If intChoice <> 0 Then
        'get the file path selected by the user
        strPath = Application.FileDialog( _
            msoFileDialogOpen).SelectedItems(1)
        
        MsgBox strPath
    End If
    End Sub

    On the other hand, this issue is more related to VBA, the VBA forum is a better place for this issue, we will move it there for you.

    Regards                      

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, June 3, 2015 2:28 AM
  • You can use the following function to select a Word (or Excel) file from Word (or Excel) VBA

    Function BrowseForFile(Optional strTitle As String, Optional bExcel As Boolean) As String
    Dim fDialog As FileDialog
        On Error GoTo err_Handler
        Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
        With fDialog
            .Title = strTitle
            .AllowMultiSelect = False
            .Filters.Clear
            If bExcel Then
                .Filters.Add "Excel workbooks", "*.xls,*.xlsx,*.xlsm"
            Else
                .Filters.Add "Word documents", "*.doc,*.docx,*.docm"
            End If
            .InitialView = msoFileDialogViewList
            If .Show <> -1 Then GoTo err_Handler:
            BrowseForFile = fDialog.SelectedItems.Item(1)
        End With
    lbl_Exit:
        Exit Function
    err_Handler:
        BrowseForFile = vbNullString
        Resume lbl_Exit
    End Function

    Call this from your macro, however you cannot open an Excel file in Word so you will need to run Excel from your macro in order to open it in Excel e.g.

    Sub sbVBA_To_Open_Workbook_FileDialog()
    Dim strWorkbookname As String
    Dim xlApp As Object
    Dim xlBook As Object
        strWorkbookname = BrowseForFile("Select Workbook", True)
        If strWorkbookname = "" Then
            MsgBox "No file selected."
            GoTo lbl_Exit
        Else
            On Error Resume Next
            Set xlApp = GetObject(, "Excel.Application")
            If Err Then
                Set xlApp = CreateObject("Excel.Application")
            End If
            On Error GoTo 0
            Set xlBook = xlApp.Workbooks.Open(Filename:=strWorkbookname)
            xlApp.Visible = True
        End If
    lbl_Exit:
        Set xlApp = Nothing
        Set xlBook = Nothing
        Exit Sub
    End Sub
    


    Graham Mayor - Word MVP
    www.gmayor.com

    • Marked as answer by TWIKLE Wednesday, June 3, 2015 2:59 PM
    Wednesday, June 3, 2015 8:04 AM
  • Thanks. It works.
    Wednesday, June 3, 2015 2:59 PM