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

_______________________________________________________________________________________

Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________



Hi Lambert.

A couple of things spring to mind.  Firstly I would absolutely check no user has changed the sheetname from "Data" and "Values" to something else.
Secondly - is the workbook a legacy .xls format or one of the post XL2007 .xlsx .xlsm etc formats?

Sound to me like the sheet name has been changed?  That is the immediate suspect.

Check for things like HIDDEN Sheets.  A user may have hidden a worksheet so it is now Sheet2 in the count, but not visible.

regards
Darryl.



________________________________________
From: accessd-bounces at databaseadvisors.com [accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert [Lambert.Heenan at chartisinsurance.com]
Sent: Friday, 4 February 2011 6:23 AM
To: Access-D Email (accessd at databaseadvisors.com)
Subject: [AccessD] Bizarro-land: Excel and Access not playing (Access 2002)

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 ????
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________________________________________________

The information transmitted in this message and its attachments (if any) is intended 
only for the person or entity to which it is addressed.
The message may contain confidential and/or privileged material. Any review, 
retransmission, dissemination or other use of, or taking of any action in reliance 
upon this information, by persons or entities other than the intended recipient is 
prohibited.

If you have received this in error, please contact the sender and delete this e-mail 
and associated material from any computer.

The intended recipient of this e-mail may only use, reproduce, disclose or distribute 
the information contained in this e-mail and any attached files, with the permission 
of the sender.

This message has been scanned for viruses.
_______________________________________________________________________________________