For Each Loop works once then gives error 91
For Each Loop works once then gives error 91
(OP)
Upfront, letting you know I might not have explained well or need to provide more code for context...
This question is using MS-Access to run vba to modify an excel sheet.
Found this code to refresh data in pivot at https://www.automateexcel.com/vba/refresh-pivot-ta...
It seems to work fine without error the first time I run it. Normally I only have to run the code once and only discovered the issue when doing some testing to adjust other things in the spreadsheet unrelated to the pivot, when I click the button to run the code a second time, it stops on this line with the error.
Run-time error 91 Object or variable with block not set
This code is only run one time per click and I finish it off by setting things to nothing.
If I completely close/exit MS-Access and reopen it. It is fine again to run once, but not more than once before the error happens.
This question is using MS-Access to run vba to modify an excel sheet.
Found this code to refresh data in pivot at https://www.automateexcel.com/vba/refresh-pivot-ta...
It seems to work fine without error the first time I run it. Normally I only have to run the code once and only discovered the issue when doing some testing to adjust other things in the spreadsheet unrelated to the pivot, when I click the button to run the code a second time, it stops on this line with the error.
Run-time error 91 Object or variable with block not set
CODE -->
Case "Submittal Data"
.Cells(2, 1).CopyFromRecordset rs
For Each chPivot In ActiveWorkbook.PivotCaches
chPivot.Refresh
Next chPivot
.Range("H2:H" & lastRow + 1).ClearContents
End Select
This code is only run one time per click and I finish it off by setting things to nothing.
If I completely close/exit MS-Access and reopen it. It is fine again to run once, but not more than once before the error happens.
RE: For Each Loop works once then gives error 91
MsgBox ActiveWorkbook.PivotCaches.Count
For Each chPivot In ActiveWorkbook.PivotCaches
You may also check if you refer to proper workbook before refreshing the cache:
MsgBox ActiveWorkbook.Name
combo
RE: For Each Loop works once then gives error 91
CODE
This is weird with Excel.
If you have this in your code: Cells(2, 1). (See the missing period before Cells?), Excel will let you run it first time just fine, but then complains about the code you have just run. Nice.
So, I would check how you declared chPivot, and fully qualify it.
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: For Each Loop works once then gives error 91
Debug.Print ActiveWorkbook.Name displays: SubmittalsOverdueDataLeadChart_Template.xlsx
Debug.Print ActiveWorkbook.PivotCaches.Count displays: 1
second time error 91 when it gets to the debug statement
CODE -->
Case "Submittal Data" .Cells(2, 1).CopyFromRecordset rs Debug.Print ActiveWorkbook.Name Debug.Print ActiveWorkbook.PivotCaches.Count For Each chPivot In ActiveWorkbook.PivotCaches chPivot.Refresh Next chPivot .Range("H2:H" & lastRow + 1).ClearContents End Select
Here is the full code if that helps.
CODE -->
RE: For Each Loop works once then gives error 91
In that case, I am surprised that
ActiveWorkbook.PivotCaches (or trying to access anyu other properties or methods of ActiveWorkbook)
works at all!
Unless, of course, you have a global object declared as ActiveWorkbook somewhere else, are assigning it to an excel application's ActiveWorkbook, and is being referenced and dereferenced elsewhere.
You'd be best off in the short term to be explicxit, and use
objapp.ActiveWorkbook
In the long run, see if you can hunt down the declaration and usage of ActiveWorkbook with a view to eliminating it if you can; this is a great example of why global variables are to be used sparingly
RE: For Each Loop works once then gives error 91
For Each chPivot In wb.PivotCaches.
I guess that you have reference to Excel (so why excel objects and constants work). You may benefit from early binding, for instance:
Dim objapp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim Rng As Excel.Range
Set objapp = New Excel.Application
combo
RE: For Each Loop works once then gives error 91
Dim chPivot As PivotCache
would error.
So, we have here combination of early and late binding, but why?
And nowhere in the code I can see:
CODE
That's why, in my opinion, Excel is saying: I will let you pass once, but after that - an error.
Happened to me many times.
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: For Each Loop works once then gives error 91
CODE -->
Option Compare Database Option Explicit Public ActiveWorkbook As Object ' Early or late binding makes no difference to this Public Sub Demo() Set ActiveWorkbook = CreateObject("Excel.Application").workbooks.Add ' possibly in some startup code somewhere, so only run once minimalexample ActiveWorkbook.Close Set ActiveWorkbook = Nothing ' possibly in some cleanup code, but this can be run multiple times without error, so could happily sit ion your main processing loop minimalexample End Sub Public Sub minimalexample() Dim objapp As Object Set objapp = CreateObject("Excel.Application") Debug.Print ActiveWorkbook.Name End Sub
RE: For Each Loop works once then gives error 91
Try:
CODE
Case "Submittal Data" .Cells(2, 1).CopyFromRecordset rs Debug.Print objapp.ActiveWorkbook.Name ...
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: For Each Loop works once then gives error 91
CODE -->
combo
RE: For Each Loop works once then gives error 91
Your code gives "Variable not defined" error on line:
MsgBox ActiveWorkbook.Name
It needs:
MsgBox objExcel.ActiveWorkbook.Name
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: For Each Loop works once then gives error 91
combo
RE: For Each Loop works once then gives error 91
With an early binding (Excel referenced), I would expect to see:
Dim objExcel As New Excel.Application
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: For Each Loop works once then gives error 91
Andy, looks like your last post sorted it out. After adding objapp. before ActiveWorkbook, I was able to run the code multiple times without the error.
I did a search [CTRL+F] for Current Project and that was the only spot where ActivWorkbook exists, so my thought is that it only affected that one item...unless as Andy mentioned there are other statements that should be prefixed with objApp or use
Sometimes hard for me to discern which statements need more prefixes (excuse if terminology not correct) between native excel vba and running excel code indirectly via access vba.
Revised code in green
CODE -->
Case "Submittal Data" .Cells(2, 1).CopyFromRecordset rs Debug.Print objapp.ActiveWorkbook.Name Debug.Print objapp.ActiveWorkbook.PivotCaches.Count For Each chPivot In objapp.ActiveWorkbook.PivotCaches chPivot.Refresh Next chPivot .Range("H2:H" & lastRow + 1).ClearContents End Select
Regarding early/late binding, yes I have both because in the beginning I used early binding and then down the road tried to switch to late binding and then sometimes copied bits and pieces from older code or the web to get things working and so things got a bit muddled.
RE: For Each Loop works once then gives error 91
RE: For Each Loop works once then gives error 91
it is kind of tricky to switch, unless you have a way to fully compile your code and assure you do have Option Explicit
With an early binding, all Excel's constants like xlNone, xlDiagonalDown, xlEdgeBottom, xlContinuous, etc. will work just fine. But as soon as you change to a late binding (and eliminate reference to Excel), you may crash all over unless you change all of those constants to -4142, 5, 9, 1 respectively.
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: For Each Loop works once then gives error 91
combo
RE: For Each Loop works once then gives error 91
CODE -->
RE: For Each Loop works once then gives error 91
Something I advised way back near the beginning ...
RE: For Each Loop works once then gives error 91