Get Work sheet Name and last row (1 Viewer)

[bee55]

Hi every body

i have the following code to get excel sheet name and last row number it work fine with no problem but when i go to next record in my form and press the button agin it produce error 91 "object variable or with block not set"
please review the code :

‘ this code to get sheet name and last row in column A
‘General declaration
Dim LastRow As Long
Dim SheetName As String

'Declare Excel file
 Dim strPath As String
 Dim ApXL As Object
 Dim xlWBk As Object
 Dim xlWsh As Object
 Dim MyRange As Range

 
'open Excel file
 strPath = mypath
 Set ApXL = CreateObject("Excel.Application")
 Set xlWBk = ApXL.Workbooks.Open(strPath)
 SheetName = ActiveSheet.Name
 Me!Sheet_Name = SheetName
 Set xlWsh = xlWBk.Worksheets(SheetName)

 Set MyRange = xlWsh.Range("A" & "1")
 LastRow = Cells(65536, ("A")).End(xlUp).Row
 
 Me!Last_Row = LastRow
' close Excel
' ===================================
    If Not MyRange Is Nothing Then
        Set MyRange = Nothing
    End If
    
    If Not xlWsh Is Nothing Then
        xlWsh.Close
        Set xlWsh = Nothing
    End If    

    If Not xlWBk Is Nothing Then
        xlWBk.Close True
        Set xlWBk = Nothing
    End If  
    
    If Not ApXL Is Nothing Then
        ApXL.Quit
        Set ApXL = Nothing
    End If

 

[JHB]

You have not any reference to the Excel object in these two lines, (ActiveSheet and Cells is unknown for MS-Access):

..
SheetName = ActiveSheet.Name ..
. 
LastRow = Cells(65536, ("A")).End(xlUp).Row

 

[bee55]

You have not any reference to the Excel object in these two lines, (ActiveSheet and Cells is unknown for MS-Access):

..
SheetName = ActiveSheet.Name ..
. 
LastRow = Cells(65536, ("A")).End(xlUp).Row

when i press the button at first time it work fine and give a right result .
but when i press the button a gain it produce error

i think the problem on closing excel application , because if i open task manger and close excel file and then restart the database it work fine also

 

[JHB]

..
i think the problem on closing excel application , because if i open task manger and close excel file and then restart the database it work fine also

No it is not the closing, it will run each second time - did you set the references?

 

[bee55]

No it is not the closing, it will run each second time - did you set the references?

i have set the referance ( Microsoft Excel 14.0 object library )
is that you mean ?

 

[JHB]

No the references to the Excel object, you're missing it in the two lines I showed you.

..
SheetName = [B][COLOR=Red]ApXL.[/COLOR][/B]ActiveSheet.Name 
..
.  
LastRow = [B][COLOR=Red]ApXL.[/COLOR][/B]Cells(65536, ("A")).End(xlUp).Row

 

[bee55]

No the references to the Excel object, you're missing it in the two lines I showed you.

..
SheetName = [B][COLOR=Red]ApXL.[/COLOR][/B]ActiveSheet.Name 
..
.  
LastRow = [B][COLOR=Red]ApXL.[/COLOR][/B]Cells(65536, ("A")).End(xlUp).Row

this solved issu

thank you very mush

 

[JHB]

this solved issu

I know!

thank you very mush

You're welcome, luck with it.

 

[Rx_]

Congradulation on solving the problem.
This missive is offered to help those who expand usage of Excel automation prevent painful situations.
"Experience is not the best teacher, the experience of others is better."

Just for a followup to the casual reader that is searching for the "error 2nd time" on Excel automation code.

In your example:
ActiveSheet.Name
ApXL.ActiveSheet.Name The APXL is the Object Reference when Access is running the vba code to automate Excel.

The first time this code runs, the execution creates another instance of Excel on your behalf (to help you of course) and complets the process.
You couldn't help but notice: the vba compiler will not discover the missing object references for you.

If the task manager is brought up, there will be an Orphan Excel instance still using memory and processor threads. It is unreachable as the pointer (reference) is destroyed from the first run. The 2nd error typically leaves yet another instance of Excel running. These instance of Excel will reside until they are ended with Resource Manager or with a workstation reboot.
As a Quality Assurance policy, turn on the Task Manager when using Excel Automation. Make sure the instance of Excel is created and destroyed.

For those of you running Citrix or Remote Terminal, the multiple users with this problem can start to pile up many orphaned Excel instances that use up system resources. I learned this the hard way with dozens of users calling the same report many times a day. I had just released the code right before taking a short vacation. The IT Server people were waiting for my return, they were not happy.

Some tricky situations to watch for:
1350 For Each C In objxl.ActiveWindow.Selection
1360 If C.Value <> C.Offset(-1, 0).Value Then
1370 C.Font.FontStyle = "Bold"
1380 C.Resize(, 2).Font.Bold = True ' bolds 2th columns out all Comments should be bold - try -1 for column A to be bold too
1390 Else ' Note the C assigned to an ActiveWindow

Another one is a reference to a reference - missing the Objxl in Red will cause the 2nd time error:
Objxl.ActiveWorkbook.Names.Add Name:="Data1", RefersToR1C1Local:=Objxl.Range("A5").CurrentRegion ' Set named range Data1 to currentregion
OR
objxl.ActiveSheet.PivotTables("AverageDays_Area").AddDataField objxl.ActiveSheet. _
PivotTables("AverageDays_Area").PivotFields("Approval Time"), _
"Count of Approval Time", xlCount


Conditoinal Formating Example (verbose):
objxl.Selection.FormatConditions(1).StopIfTrue = False
objxl.Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""T"""
objxl.Selection.FormatConditions(objxl.Selection.FormatConditions.Count).SetFirstPriority
With objxl.Selection.FormatConditions(1).Font
.Color = -16752384
End With
With objxl.Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
End With
objxl.Selection.FormatConditions(1).StopIfTrue = False

Using the AutoFill example:
objxl.Selection.AutoFill Destination:=objxl.Range("J1:R1"), Type:=xlFillDefault

Some of my Excel datamining reports can be many pages of code. It is so easy to miss just one reference.
The rules of testing Excel Automation are:
Test twice in a row to see if there is an error
Use Task Manager - verify there are not Excel instance at end of Excel code completion.
Create an error in Excel code on purpose, verify that Error Trapping code closes all Excel instance.

I will step off my soap box now.