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

Darryl,

Negative on all counts, unfortunately. It's an Excel 2002 file, there are certainly only two tabs, and the names are "Data" and "Values".

Also I if there were any hidden tabs the count would be greater than 2.

???

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
Sent: Thursday, February 03, 2011 5:39 PM
To: Access Developers discussion and problem solving
Subject: Re: [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.
_______________________________________________________________________________________

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com