Excel 2007 Rows.Count Error 2

[bajo71]

I've got a Long Integer variable FinalRow which works fine in Excel 2003, but not 2007.

FinalRow = xlWkBook.Sheets("Rolling Forecast Models").Cells(Rows.Count, 1).End(xlUp).Row

when I get to this point it throws Application/Object Error. Any ideas why? Long Int go out to 2+Billion, so it can't be a row count issue.

Thanks.......Mickey

 

[SkipVought]

Hi,

Please post the relevant code that you are referring to.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[bajo71]

I should mention it only craps out on this automation sub. I've got FinalRow variable in source workbook which is .xlsm and it works fine using Rows.Count

Sub OpenDashboard(TemplateInd As String, ByVal DshBrdFile As String, DshBrdFilePath As String, isFin As Boolean, _
Optional ByVal BeginSum As Double = 0)
Dim xlApp As Excel.Application, xlWkBook As Workbook
Dim FinalRow As Long, i As Integer, LoopTwice

For LoopTwice = 1 To 2
If IsFileOpen(DshBrdFile) Then Workbooks(DshBrdFile).Close SaveChanges:=False

Set xlApp = New Excel.Application
xlApp.Visible = False
Set xlWkBook = xlApp.Workbooks.Open(DshBrdFilePath)

xlWkBook.Sheets("Rolling Forecast Models").Select
FinalRow = xlWkBook.Sheets("Rolling Forecast Models").Cells(Rows.Count, 1).End(xlUp).Row
For i = FinalRow To 5 Step -1
If xlWkBook.Sheets("Rolling Forecast Models").Cells(i, 2) = TemplateInd Then
If isFin = True Then
xlWkBook.Sheets("Rolling Forecast Models").Range("A" & i & "" & i).Interior.ColorIndex = 43
If LoopTwice = 1 Then xlWkBook.Sheets("Rolling Forecast Models").Range("E" & i).Value = BeginSum
Else: xlWkBook.Sheets("Rolling Forecast Models").Range("A" & i & "" & i).Interior.ColorIndex = 46
End If
End If
Next i

xlWkBook.Save
xlWkBook.Close
xlApp.Quit
Set xlApp = Nothing
DshBrdFile = "Shared_" & DshBrdFile
DshBrdFilePath = Left(DshBrdFilePath, 84) & DshBrdFile
Next LoopTwice

End Sub

 

[PHV]

What about this ?

With xlWkBook.Sheets("Rolling Forecast Models")
  FinalRow = .Cells([!].[/!]Rows.Count, 1).End(xlUp).Row
End With

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

 

[SkipVought]

...but you have i as INTEGER for the ROW sequencer!!!

Dim i as LONG

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[bajo71]

PHV, I'm certain that won't work, because the first time it runs OK, but subsequent runs fail and I've tried with and without 'With xlWkBook...'

 

[PHV]

As you automate Excel you have to use full qualified excel objects.
Did you notice the red point in my suggestion ?
Did you even try it ?

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

 

[SkipVought]

PHV's point is that BOTH Cells & Rows need the SHEET reference.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[bajo71]

OK, I'll give that a try

 

[bajo71]

Fully Qualified Excel objects was the key.

Thank you very much.....Mickey