impossible open excel file through getOpenFilename-CodePudding

I'm trying to open in another application an excel file through a dialog window an excel. I found this code in another post. However I don't get to open the file. I think the issues are in these lines of the code:If sFilePath = False Then and Set wb = excelApp.Workbooks.Open(sFilePath, False) I'm also trying to open a file .xlsx

Sub OpenFile()

' Create Excel object
'    On Error Resume Next
    Dim excelApp As Excel.Application
    Set excelApp = GetObject(, "Excel.Application")
    
    ' Prompt user to select Excel file to read from
    ' Alternatively, you could hard code the filepath like this:
    Dim sFilePath As String
    sFilePath = excelApp.GetOpenFilename(FileFilter:="Excel Files (*.xls*),*.xls*", Title:="Select excel file")
'    ' Open Excel file invisibly
    If sFilePath = False Then
        Exit Sub
    Else
        Set wb = excelApp.Workbooks.Open(sFilePath, False)
    End If

   End Sub

CodePudding user response：

You need to change your code a little bit, you may have the problem on the line where you're trying to evaluate a string to a boolean value as stated by @FunThomas, but I could think of at least another scenario that may be happening/will happen: the file may be opened by another process. I modified your code a little to address that scenario, this should be working or display the proper message if it doesn't.

Sub OpenFile()
Dim VarValueFromMsg As Variant
Dim WBToImport As Workbook
Dim TxtFileToImport As String
' Create Excel object
'    On Error Resume Next
    Dim excelApp As Excel.Application
    Set excelApp = GetObject(, "Excel.Application")
    ' Prompt user to select Excel file to read from
    ' Alternatively, you could hard code the filepath like this:
    VarValueFromMsg = excelApp.GetOpenFilename(Title:="Select excel file", FileFilter:="Excel Files (*.xls*),*.xls*", MultiSelect:=False)
    On Error GoTo Err01OpenFile
    If VarValueFromMsg = False Then Exit Sub
Err01OpenFile:
    TxtFileToImport = CStr(VarValueFromMsg)
    If Return_IsExcelFileLocked(TxtFileToImport) = True Then Exit Sub
    Set WBToImport = excelApp.Workbooks.Open(Filename:=TxtFileToImport)
End Sub
Function Return_IsExcelFileLocked(TxtFile As String) As Boolean
    On Error Resume Next
    ' If the file is already opened by another process,
    ' and the specified type of access is not allowed,
    ' the Open operation fails and an error occurs.
    If Dir(TxtFile) = "" Then MsgBox "Error01Return_IsExcelFileLocked: File does not exist.": Return_IsExcelFileLocked = True: Exit Function
    Open TxtFile For Binary Access Read Write Lock Read Write As #1
    Close #1
    ' If an error occurs, the document is currently open.
    If Err.Number <> 0 Then
    ' Display the error number and description.
    MsgBox "Error02Return_IsExcelFileLocked: #" & Str(Err.Number) & " - " & Err.Description & ": If file is open please close it."
    Return_IsExcelFileLocked = True
    Err.Clear
    End If
End Function

CodePudding user response：

Use Excel From Another Application

The Mistakes

Set xlApp = GetObject(, "Excel.Application") or Set xlApp = Excel.Application will reference an existing instance of Excel. If Excel isn't open (there is no instance), an error will occur.
.GetOpenFileName will return the file path (a string) or the boolean False. If you use a string variable, the boolean False will be converted to the string "False" so you can use the string to test if the dialog was canceled.

First Solution (Early Binding)

For example, if you want to open an Excel file from Word, you can create a reference to the Excel library and develop your macro almost as if you were in Excel e.g. all the objects from Excel will be available in the Word's IntelliSense.
To reference a new instance of the Excel Application object using early binding, you will use:
```
Set xlApp = New Excel.Application
```

Second Solution (Late Binding)

If you don't want to create a reference to the Excel library and/or you want to send the file to someone and you don't want to bother him/her with the details on how to create a reference, you will use late binding when you won't have access to the Excel objects' IntelliSense.
To reference a new instance of the Excel Application object using late binding, you will use:
```
Set xlApp = CreateObject("Excel.Application")
```

A Few Details

Whether Excel is already open or not, these codes will create a new instance that you will want to close after you're done.
To do this correctly, you have to account for possible errors occurring which is done by using an error-handling routine.
So whatever happens, in the end, the new Excel instance needs to get closed.

The Codes

Option Explicit

Sub OpenFileUsingExcelReference()
' Needs a reference to 'VBE>Tools>References>Microsoft Excel 16.0 Object Library'
    Const ProcName As String = "OpenFile"
    
    Dim xlApp As Excel.Application
    Dim wb As Workbook

    On Error GoTo ClearError
    
    Const FileFilterString As String = "Excel Files (*.xls*),*.xls*"
    Const TitleString As String = "Select Excel file"
        
    ' Reference a new invisible Excel application object.
    Set xlApp = New Excel.Application ' CreateObject("Excel.Application")
    xlApp.Visible = True ' when done developing, out-comment this line
    
    ' Prompt user to select Excel file to read from
    Dim sFilePath As String
    sFilePath = xlApp.GetOpenFilename( _
        FileFilter:=FileFilterString, Title:=TitleString)
    If sFilePath = "False" Then
        Err.Raise 1004, , "You canceled the dialog."
        'MsgBox "You canceled."
    End If
    
    Set wb = xlApp.Workbooks.Open(sFilePath, False)
    
    ' Continue, e.g.:
    Debug.Print "The workbook '" & wb.Name & "' has " _
        & wb.Sheets.Count & " sheet(s)."
    
SafeExit:
    
    ' Close the workbook and quit.
    If Not wb Is Nothing Then wb.Close SaveChanges:=False
    If Not xlApp Is Nothing Then xlApp.Quit
    
    Exit Sub
    
ClearError:
    Debug.Print "'" & ProcName & "' Run-time error '" _
        & Err.Number & "':" & vbLf & "    " & Err.Description
    Resume SafeExit
End Sub

Sub OpenFileWithoutExcelReference()
' No reference needed (*** indicates the differences)
    Const ProcName As String = "OpenFile"
    
    Dim xlApp As Object '***
    Dim wb As Object '***
    
    On Error GoTo ClearError

    Const FileFilterString As String = "Excel Files (*.xls*),*.xls*"
    Const TitleString As String = "Select Excel file"
        
    ' Reference a new invisible Excel application object.
    Set xlApp = CreateObject("Excel.Application") '***
    xlApp.Visible = True ' when done developing, out-comment this line
    
    ' Prompt user to select Excel file to read from
    Dim sFilePath As String
    sFilePath = xlApp.GetOpenFilename( _
        FileFilter:=FileFilterString, Title:=TitleString)
    If sFilePath = "False" Then
        Err.Raise 1004, , "You canceled the dialog."
        'MsgBox "You canceled."
    End If
    
    Set wb = xlApp.Workbooks.Open(sFilePath, False)
    
    ' Continue
    Debug.Print "The workbook '" & wb.Name & "' has " _
        & wb.Sheets.Count & " sheet(s)."
    
SafeExit:
    
    ' Close the workbook and quit.
    If Not wb Is Nothing Then wb.Close SaveChanges:=False
    If Not xlApp Is Nothing Then xlApp.Quit
    
    Exit Sub
    
ClearError:
    Debug.Print "'" & ProcName & "' Run-time error '" _
        & Err.Number & "':" & vbLf & "    " & Err.Description
    Resume SafeExit
End Sub