0

I'm trying to setup a command button in a user form to allow the user to open either an Excel and/or a Word Document one at a time or both at the same time. But so far I'm able only to select and open Excel but not Word files with the following code:

Sub OpeningExcelFile()
    Dim Finfo As String
    Dim FilterIndex As Integer
    Dim Title As String
    Dim Filename As Variant
    Dim wb As Workbook


    'Setup the list of file filters
    Finfo = "Excel Files (*.xlsx),*xlsx," & _
            "Macro-Enable Worksheet (*.xlsm),*xlsm," & _
            "Word Files (*.docx),*.docx," & _
            "All Files (*.*),*.*"
             MultiSelect = True


    'Display *.* by default
    FilterIndex = 4

    'Set the dialog box caption
    Title = "Select a File to Open"

    'Get the Filename
    Filename = Application.GetOpenFilename(Finfo, _
        FilterIndex, Title)

    'Handle return info from dialog box
    If Filename = False Then
        MsgBox "No file was selected."
    Else
        MsgBox "You selected " & Filename

    End If

    On Error Resume Next

    Set wb = Workbooks.Open(Filename)

Do you know what it is missing?

Community
  • 1
  • 1
Sergio
  • 39
  • 8

1 Answers1

0

You cannot open word files with Excel Application.

You need to have check like below to handle this.

Dim objWdApp As Object
Dim objWdDoc As Object
If InStr(1, Filename, ".docx", vbTextCompare) > 0 Then
    Set objWdApp = CreateObject("Word.Application")
    objWdApp.Visible = True
    Set objWdDoc = objWdApp.Documents.Open(Filename) '\\ Open Word Document
Else
    Set wb = Workbooks.Open(Filename) '\\ Open Excel Spreadsheet
End If
shrivallabha.redij
  • 5,832
  • 1
  • 12
  • 27
  • Thank You so much! I will spend my time today studying your code to understand how it works. I really appreciated. Thank you – Sergio Nov 16 '17 at 21:26
  • Your code is working great, There is something that is causing me problems and is the fact that sometimes the user won't select any file so It would be a false. I was trying to handle the return info from dialog box but "m still having the error message 1004 False.xlsx could not be found. Do you know how to handle it? – Sergio Nov 16 '17 at 21:49
  • 1
    You need to add `Exit Sub` just below `MsgBox "No file was selected."` to ensure that the code is not executed further in absence of input. – shrivallabha.redij Nov 17 '17 at 08:09