[AccessD] Bizarro-land: Excel and Access not playing (Access 2002)

Heenan, Lambert Lambert.Heenan at chartisinsurance.com
Thu Feb 3 13:09:46 CST 2011


Cross posted to Access-L and AccessD

I have some code that has been running just find for years on end. It opens an Excel file and then does a quick check for the existence of a particular worksheet name in the file, as the first step to ensure that it is a correctly formatted  file.

A correct file has two tabs, the first named "Data" and the other named "Values". Here is the code that all of a sudden does not work:

Dim xlObj As Excel.Application
Dim i as Integer
Dim bInvalidFile as Boolean
...

        Set xlObj = Excel_OpenWorkBookHidden(mod_sExcelPath)
        ' opens the file with the standard automation methods*, see below
        bInvalidFile = True
        For i = 1 To xlObj.Worksheets.Count
        '  xlObj.Worksheets.Count evaluates to 2

            If (xlObj.Worksheets(i).Name = "Values") Then

        ' when i = 1 the expression xlObj.Worksheets(i).Name evaluated to "Data", as expected
       ' but as soon as the Next i statement is executed xlObj.Worksheets(i).Name shows up in the
       ' Watch window as "Application-defined or object-defined error"

                bInvalidFile = False
                Exit For
            End If
        Next i '

I have a function that was written some time after the above problematic code which looks for a given tab name in an Excel file...

Function Excel_WorkSheetExists(xlApp As Excel.Application, strName As String) As Boolean
Dim xlWs As Excel.Worksheet

    On Error Resume Next
    Set xlWs = xlApp.Worksheets(strName)
    Excel_WorkSheetExists = (Err.Number = 0)
    Set xlWs = Nothing
    On Error GoTo 0
End Function
When I call this function using a reference to the exact same file as was causing trouble above...

    Set xlApp = Excel_OpenWorkBookHidden(strPORFile)
        ' now check for the presence of some worksheets...
    boolHit = Excel_WorkSheetExists(xlApp, "Data")
    If boolHit Then
        boolHit = boolHit And Excel_WorkSheetExists(xlApp, "Values")
    End If
... the function happily finds both the "Data" and the "Values" tab and returns True.

I am simply going to blink and replace the old For Loop at the top of this posting with a call to the Excel_WorkSheetExists function, which will get me past the problem, but I cannot for the life of me figure out what the problem might be with the old code that has work fine for so long.


* my method for opening the Excel file ...

70            If IsExcelRunning() Then
80                Set xlApp = GetObject(, "Excel.Application")
90            Else
100               Do
110                   Set xlApp = CreateObject("Excel.Application")
120               Loop Until IsExcelRunning()
130           End If

Lambert ????



More information about the AccessD mailing list