GHOST EXCEL

[TedRSki]

I hope someone can help. I have tried eveything that is posted here and on other sites. I get the runtime error 462 a lot especially on the LRow line of code. But I have the Ghost Excel which I can't get rid of.
Below is a sampling of my code where I use code pointing to an Excel spreadsheet.

Dim ExcelSheet As Object, XL As Object
Dim DB
Dim RSL
Dim X
Dim LRow As Integer

Set DB = CurrentDb()
Set RSL = DB.OpenRecordset("SELECT * from LABENTRY WHERE LLEVEL=1 ORDER BY LENTRY", dbOpenDynaset, dbReadOnly)
Set XL = CreateObject("Excel.Application")    '
Set ExcelSheet = CreateObject("Excel.Sheet")
Set ExcelSheet = XL.Workbooks.Open("F:\payroll_time_sheet_BLANK.xls")

Do Until RSL.EOF
'
    SName = RSL("LENTRY")
    Counter = 4
    LastCounter = 3
    NCount = 5
'
    XL.Worksheets(SName).Select
[b]    LRow = XL.ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row [/b]
    
    Do Until Counter = LRow
'
        If (XL.ActiveSheet.Cells(Counter, 3).Value2 = "0" And _
            XL.ActiveSheet.Cells(Counter, 1).Value2 = "0") Then
            XL.ActiveSheet.Rows(Counter).Hidden = True
        End If

RSL.MoveNext
Loop

ExcelSheet.Sheets(1).Select
ExcelSheet.Sheets(1).Cells(4, 3).Select

RSL.Close
ExcelSheet.Application.DisplayAlerts = False
ExcelSheet.SaveAs "F:\payroll_time_sheet_BLANK.xls"
ExcelSheet.Application.DisplayAlerts = True
'
On Error Resume Next
' 
ExcelSheet.Application.Quit
'
DB.Close    ' Release the object variable.
Set XL = Nothing
Set ExcelSheet = Nothing
Set RSL = Nothing
Set DB = Nothing

I have also tried the following for my IF selection and I still get the GHOST with it.

        If (ExcelSheet.Sheets(SName).Cells(Counter, 3).Value2 = "0" And _
            ExcelSheet.Sheets(SName).Cells(Counter, 1).Value2 = "0")  Then
            ExcelSheet.Sheets(SName).Rows(Counter).Hidden = True
        End If

I have tried everything and am ready to pull my remaining hair out. Can anyone help?

 

[RMcCallan]

I have had this problem before and I can't find the database where it happened but I seem to remember this it is the 'ActiveSheet' which keeps excel hanging... as I can't find it I'm not sure what I replaced it with but I hope this helps anyway!

Thanks,
Rebecca

 

[RMcCallan]

I have just had a look, this may help. I declared objActiveWrksheet as an object and (obviously) wherever I had activeworksheet i just replaced it with objActiveWrksheet. Good luck!

Thanks,
Rebecca

 

[RMcCallan]

One more important detail I forgot!
Set objActiveWrksheet = objActiveWkb.Worksheets("Sheet1")

Thanks,
Rebecca

 

[PHV]

Get rid of this line:
Set ExcelSheet = CreateObject("Excel.Sheet")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TedRSki]

PHV, I tried your suggestion and the first time I ran the routine I received the remote server error. Then I ended the Excel process and ran it again and it ran without the server error but still Excel does not close. It has to be something with the LRow and the DO UNTIL loop because I used the same basic format for another routine where I unhide the rows and that routine works. The Unhide does not use a DO UNTIL loop and does not have LRow.

 

[TedRSki]

RMcCallan, How did you declare "objActiveWkb.Worksheets"?

 

[RMcCallan]

As normal,
Dim objActiveWkb.Worksheets as Object

Thanks,
Rebecca

 

[PHV]

Your posted code is not complete as you have 2 Do and a single Loop ....

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TedRSki]

RMcCallan, I tried your suggestion and I still have the ghost. This is how I did it:

Function HideRows()
Dim ExcelSheet As Object, XL As Object
Dim objActiveWrksheet As Object
Dim objActiveWkb As Object
Dim LRow As Integer

Set DB = CurrentDb()
Set RSL = DB.OpenRecordset("SELECT * from LABENTRY WHERE LLEVEL=1 ORDER BY LENTRY", dbOpenDynaset, dbReadOnly)
Set XL = CreateObject("Excel.Application")    '
Set objActiveWkb = XL.Workbooks.Open("F:\payroll_time_sheet_BLANK.xls")

Do Until RSL.EOF
'
    SName = RSL("LENTRY")
    Counter = 4
    LastCounter = 3
    NCount = 5
'
    Set objActiveWrksheet = objActiveWkb.Worksheets(SName)
    XL.Worksheets(SName).Select
    
    LRow = objActiveWrksheet.Cells(Rows.Count, "B").End(xlUp).Row

    Do Until Counter = LRow
'
        If (objActiveWrksheet.Cells(Counter, 3).Value2 = "0" And _
            objActiveWrksheet.Cells(Counter, 1).Value2 = "0") Then
            objActiveWrksheet.Rows(Counter).Hidden = True
        End If
        Counter = Counter + 1
        NCount = Counter + 1
    Loop
'
    End If
RSL.MoveNext
Loop

RSL.Close
objActiveWrksheet.Application.DisplayAlerts = False
objActiveWrksheet.SaveAs "F:\payroll_time_sheet_BLANK.xls"
objActiveWrksheet.Application.DisplayAlerts = True

On Error Resume Next
' Close Excel with the Quit method on the Application object.
objActiveWrksheet.Application.Quit
DB.Close    ' Release the object variable.
Set XL = Nothing
Set RSL = Nothing
Set DB = Nothing
Set objActiveWrksheet = Nothing
End Function

 

[TedRSki]

Sorry, PHV. The code above is pretty much the code of the function except there are more conditions of the IF statement. All I did was replace XL.ActiveSheet with objActiveWrksheet. But it is all pretty much there. I deleted all of my code comments to try and make the excerpt smaller.

 

[RMcCallan]

objActiveWkb.Close SaveChanges:=True
XL.Application.Quit

You do appear to be closing it but perhaps it will work better with the above code because of the changes?

also you set everything but active workbook to be nothing

Set objActiveWkb = Nothing

Thanks,
Rebecca

 

[TedRSki]

RMcCallan, Tried the changes as follows and still have the ghost. But whenever I run the function the first time after making the changes I get the remote server error. Then I reset the code and End Process the Excel.Exe and then rerun the function. It runs and leaves me with the ghost.

objActiveWkb.Close SaveChanges:=True
XL.Application.Quit
DB.Close    ' Release the object variable.
Set XL = Nothing
Set RSL = Nothing
Set DB = Nothing
Set objActiveWrksheet = Nothing
Set objActiveWkb = Nothing
''DoCmd.Hourglass False
MsgBox "PDP created on F:\payroll_time_sheet_BLANK.xls"

 

[RMcCallan]

I'm not too sure what else it could be then... just one thing I can see which you could change but I'm not sure how it could make the application hang but I'll mention it anyway,

I believe you don't need to set your 'RSL' and 'DB' to = Nothing.

Sorry I can't offer you more help perhaps PHV has some better ideas.

Thanks,
Rebecca

 

[TedRSki]

Rebecca, thank you for you help but still a ghost. I even tried switching it over to Sub Routine and still have the ghost. I may end up with just a work around. If I can't get it working here than I was thinking about putting the code into the Workbook Open module so when the user opens the workbook thru Excel, the empty rows would be hidden. But I figured my process in Access would be unhiding the rows before the load then loading all of the data. I was just hoping that if I could have hidden the empty rows the second portion of my load would run faster - first I load the names then I load the hours.

 

[TedRSki]

Rebecca and PHV, I made a copy of the code and commented out a lot of the code that accessed the Excel application. I ran the routine and no ghost. Then I began slowly un-commenting out parts of the code, each time running the routine. Each time Excel closed with no problem. The line of code that causes the ghost is the LROW line of code - "LRow = XL.Worksheets(SName).Cells(Rows.Count, "B").End(xlUp).Row". This is also the line of code that gives me the remote server problem. When I hard-coded the total number of rows in each sheet and leave the line commented out, the routine runs with no ghost. So now it is just a matter of figuring out how to code to find the last row of each sheet.

 

[PHV]

LRow = XL.Worksheets(SName).Cells([!]XL.Worksheets(SName).[/!]Rows.Count, "B").End(xlUp).Row

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TedRSki]

PHV, thank you. That works. I also found a WITH statement that works as well. I use it to find the Last Row for looping and Last Column for looping with an autofit. Both work well. The WITH statement is below. Have you ever done row checking for visibility? I loaded the names from one file according to job category. Now I am loading the hours worked and figured on checking the visibility of the row - if visible = true - to cut down on the looping time.

    With XL.Worksheets(SName)
       LRow = .Cells(.Rows.Count, "B").End(xlUp).Row
       LCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
.
.
.
    Dim Col As Integer
    
    For Col = 1 To LCol
        XL.Worksheets(SName).Columns(Col).EntireColumn.AutoFit
    Next Col