Open File dialog box to get Excel

Question

I've written some Word VBA which takes an Excel file and updates Labels (ActiveX Control) in the Word file. The only thing is this Excel file will change path and filename each month. Instead of editing 2 variables each month, how do I add an Open File dialog box so the user selects the Excel file to be used?

Here is what I have now:

Sub Update()
    Dim objExcel As New Excel.Application
    Dim exWb As Excel.Workbook

    PathWork = "C:\My Documents\2015-05 Report\"
    CalcFile = "May2015-data.xlsx"

    Set exWb=objExcel.Workbooks.Open(FileName:=PathWork & CalcFile)
    ThisDocument.date.Caption=exWb.Sheets("Data").Cells(1,1)
End Sub

Answer 1

Here is a simplified macro which will allow the user to select only Macro-Enabled Excels. I couldn't comment on the previous answer as I have not earned enough reputation to comment on an answer. Please mind it.

Public Sub GetCaptionFromExcel()
    Dim objExcel As New Excel.Application, exWb As Workbook
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Select Macro-Enabled Excel Files"
        .Filters.Add "Macro-Enabled Excel Files", "*.xlsm", 1
        If .Show <> -1 Then Exit Sub

        Set exWb = objExcel.Workbooks.Open(.SelectedItems(1))
        '*** Use the values from excel here***
        MsgBox exWb.Sheets("Data").Cells(1, 1)
        '*** Close the opened Excel file
        exWb.Close
    End With
End Sub

Answer 2

You could try something like this

Replace PathWork and CalcFile with Dialogbox

With Dialogs(wdDialogFileOpen)
    If .Display Then
        If .Name <> "" Then
            Set exWb = Workbooks.Open(.Name)
            sPath = exWb.Path
        End If
    Else
        MsgBox "No file selected"
    End If
End With

Complete CODE should look like this

Option Explicit

Sub Update()
    Dim objExcel As New Excel.Application
    Dim exWb As Excel.Workbook
    Dim sPath As String

    '// Dialog box here to select excel file
    With Dialogs(wdDialogFileOpen)
        If .Display Then
            If .Name <> "" Then
                Set exWb = Workbooks.Open(.Name)
                sPath = exWb.Path
            End If
            Set exWb = objExcel.Workbooks.Open(FileName:=sPath)
            ActiveDocument.Date.Caption = exWb.Sheets("Data").Cells(1, 1)
        Else
            MsgBox "No file selected"
        End If
    End With
    Set objExcel = Nothing
    Set exWb = Nothing
End Sub