ExcelSheet Row Hide Error - Obj Doesn't Support Propert or Method

[TedRSki]

I have loaded data from a query into an excel workbook with multiple sheets. The load worked fine. Then I reuse a table with the sheet names to try and access the sheets to hide unused rows. The code errors out on the "Hidden" line of code. I do not close Excel before I run this loop so I did not use another Open Workbook command. Here is my code that I am using to determine if I have a group name in Row 1 Column 1 which is a merged cell with columns 2 and 3 and an employee name in Row 2 Column 3, Sname is set to the first column from a table query. I complete the first pass with entering names onto the sheet, then do a MoveFirst to start at the beginning of the table for loop #2.

If (ExcelSheet.Sheets(SName).Cells(Counter, 3).Value2 = "0" And _
    ExcelSheet.Sheets(SName).Cells(LCounter, 1).Value2 = "0") Or _
   (ExcelSheet.Sheets(SName).Cells(Counter, 3).Value2 = " " And _
    ExcelSheet.Sheets(SName).Cells(LCounter, 1).Value2 = " ") Or _
   (ExcelSheet.Sheets(SName).Cells(Counter, 3).Value2 = "" And _
    ExcelSheet.Sheets(SName).Cells(LastCounter, 1).Value2 = "") Then
      ExcelSheet.Sheets(SName).Rows(Counter).Select
   [b]   ExcelSheet.Sheets(SName).EntireRow.Hidden = True [/b]
End If

 

[boblarson]

You need to change this:

ExcelSheet.Sheets(SName).EntireRow.Hidden = True

to this:

Selection.EntireRow.Hidden = True

Bob Larson
FORMER Microsoft Access MVP (2008-2009, 2009-2010)
Free Tutorials/Samples/Tools:

http://www.btabdevelopment.com

 

[boblarson]

Although if you are doing this from Access then you need to use

ExcelSheet.Selection.EntireRow.Hidden = True

(tying it to the application object)

See here for why:

http://www.btabdevelopment.com/ts/excelinstance

Bob Larson
FORMER Microsoft Access MVP (2008-2009, 2009-2010)
Free Tutorials/Samples/Tools:

http://www.btabdevelopment.com

 

[TedRSki]

Bob, I tried changing the "Hidden" row to your suggestion - ExcelSheet.Selection.EntireRow.Hidden = True - and I still get the error #438. I also tried removing the Rows(Counter).Select statement and still got the error. The code is extensive otherwise I would post the whole function. Should I perhaps break it out and maybe close the Excel application then reopen it and try to code this as a separate subroutine? This code is within a function not a subroutine. I had found some of the code within a function on the site. That is why I left it in a function.

 

[PHV]

What about this ?
ExcelSheet.Sheets(SName).Rows(Counter).Hidden = True

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

 

[boblarson]

Is ExcelSheet the application object?

By that I mean - it is

Dim ExcelSheet As Object

or

Dim ExcelSheet As Excel.Appliction

(either one - first one is for Late Binding and second is for Early Binding).

Bob Larson
FORMER Microsoft Access MVP (2008-2009, 2009-2010)
Free Tutorials/Samples/Tools:

http://www.btabdevelopment.com

 

[TedRSki]

Thank you both for your help. It now works. Now I just have to figure out how to unhide the rows BEFORE loading the data. Plus I am going to try to make the sheet visible while running the Hidden code so I can see what is happening - I have to figure out my IF statement. But I wanted to post the code for anyone who needs to do the same thing that I am doing. So here is the code, I removed extra comments and blank rows to shorten it a bit :

Function HideRowsX()
' Declare an object variable to hold the object
' reference. Dim as Object causes late binding.
Dim ExcelSheet As Object, XL As Object
Dim DB
Dim RSL

Dim SName As String
Dim Counter As Integer
Dim LastCounter 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("C:\payroll_time_sheet_BLANK.xls")

'    ExcelSheet.Sheets(SName).Cells.EntireRow.Hidden = False

Do Until RSL.EOF
'
'   Hide the rows that are blank starting one row after the last filled row
'
    SName = RSL("LENTRY")
    Counter = 4
    LastCounter = 3
'
'    LastRow = ExcelSheet.Sheets(SName).Cells(Rows.Count, "E").End(xlUp).Row
'    totalrow = LastRow - 10
'
    LastRow = 950

    Do Until Counter = LastRow
'
        If (ExcelSheet.Sheets(SName).Cells(Counter, 3).Value2 = "0" And _
            ExcelSheet.Sheets(SName).Cells(LastCounter, 1).Value2 = "0") Or _
           (ExcelSheet.Sheets(SName).Cells(Counter, 3).Value2 = " " And _
            ExcelSheet.Sheets(SName).Cells(LastCounter, 1).Value2 = " ") Or _
           (ExcelSheet.Sheets(SName).Cells(Counter, 3).Value2 = "" And _
            ExcelSheet.Sheets(SName).Cells(LastCounter, 1).Value2 = "") Then
            ExcelSheet.Sheets(SName).Rows(Counter).Hidden = True
        End If
        Counter = Counter + 1
        LastCounter = Counter - 1
    Loop
RSL.MoveNext
Loop

RSL.Close

ExcelSheet.Application.DisplayAlerts = False
ExcelSheet.SaveAs "C:\payroll_time_sheet_BLANK.xls"
ExcelSheet.Application.DisplayAlerts = True
'
On Error Resume Next

' Close Excel with the Quit method on the Application object.
ExcelSheet.Application.Quit
RSL.Close
DB.Close    ' Release the object variable.
Set ExcelSheet = Nothing
Set RSL = Nothing
Set DB = Nothing
End Function