Darryl Collins
Darryl.Collins at iag.com.au
Sun Feb 6 17:29:13 CST 2011
_______________________________________________________________________________________
Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________
Hey Lambert,
If you want, send me a copy of the target XL file and the Access code modules used to call it, I will see if I can replicate the problem here, or see if there is anything funky with the XL file. Always interested in these sort of issues.
Cheers
Darryl.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Saturday, 5 February 2011 1:16 AM
To: Access Developers discussion and problem solving
Subject: Re: [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
--
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.
_______________________________________________________________________________________