EXTREMELY URGENT: Problem with exlApp.Workbooks.Open() function in VBA

[DimlyLitMind]

Hi all,

I am trying to run a VBA code in Access to open an excel file and export some data into it. This has worked before with Excel 2003. I had to recently migrate to Excel 2007 and I am getting various errors one by one, but at this point I am stuck at this particular step of opening an Excel file. The file is present, the path is correct (I checked while debugging the VBA code) and I get an error. The problem is, the Err.Description field is blank. In fact, everything in Err object is either blank or 0. Can someone help me? This is EXTREMELY URGENT. Thanks!!!

Here's my code:

On Error Resume Next
    ' try to open Excel
    Set exlApp = CreateObject("Excel.Application")
    RunExcelBeforeDataTransfer = False
    If LoadExcel = False Then
        Exit Function
    End If
    Set wrkBook = exlApp.Workbooks(strFSFileName)
    If 0 = Err.Number Then 'a workbook with this name is OPEN. Close it first.
        exlApp.Workbooks(strFSFileName).Close SaveChanges:=False
    End If
    
    Err.Clear
    On Error GoTo ErrorHandler
    Call Log("Opening Excel file: " & strThisAppPath + strFSFileName)
    Set wrkBook = exlApp.Workbooks.Open(strThisAppPath + strFSFileName, 0, False)
    exlApp.Run (strFSFileName & "!GeraFormatBeforeDataTransfer")
    exlApp.Workbooks(strFSFileName).Close SaveChanges:=True
    UnLoadExcel
    
    RunExcelBeforeDataTransfer = True
    Exit Function
ErrorHandler:
    UnLoadExcel
    Call Log("Error preparing Excel for data transfer: " & CStr(Err.Number) & " - " & Err.Description & Err.Source)
    AppError "Error preparing Excel for data transfer: " & CStr(Err.Number) & " - " & Err.Description & Err.Source
    Err.Raise vbObjectError + 1, "", ""
End Function

 

[RoryA]

Have you tried commenting out the On Error GoTo ErrorHandler line and adding an On Error Goto 0 in its place for testing?

 

[DimlyLitMind]

I have tried your suggestion. I am getting an error message that "The macro "filename.xls!GeraFormatBeforeDataTransfer" is not running. Either it is not available in this workbook or the macros are disabled." However, it ran before, today AND yesterday. I think the macro is not getting enabled. How can I enable it? I have set "Enable all macro settings" in Excel options.

 

[RoryA]

Is the file still an .xls not an .xlsm or .xlsb file? Does the file name have spaces in it?

 

[xenou]

You can test if the workbook opens successfully:

Set wrkBook = exlApp.Workbooks.Open(strThisAppPath + strFSFileName, 0, False)
Msgbox wrkBook.Name

It is possible you need a path separator:

Set wrkBook = exlApp.Workbooks.Open(strThisAppPath [B][COLOR="#FF0000"]& "\" &[/COLOR][/B] strFSFileName, 0, False)

Also now just noticing that + is not a vba concatenator so that is wrong:

Set wrkBook = exlApp.Workbooks.Open(strThisAppPath [B][COLOR="#FF0000"]+[/COLOR][/B] strFSFileName, 0, False)

Should be:

Set wrkBook = exlApp.Workbooks.Open(strThisAppPath [B][COLOR="#FF0000"]&[/COLOR][/B] strFSFileName, 0, False)

 

[DimlyLitMind]

It seems there was an issue with the file used. There were two files in two folders with the same name, but one with the macro not present (or not activated, I think). I used the newer files and it worked!! I don't know why there were two files like that to begin with, but the code worked without any changes made. I think the + and "\" are not involved.

 

[DimlyLitMind]

Thank you for your help Rory. It seems there were two files in two folders which have the same name but one was an older version which does not contain the macro I think. I used the newer file and it worked.