I'm pretty sure i know why this is happening, but I cannot seem to sort out the solution.
I have the below code.
It is running from Acccess 2010.
It runs fine, the first time through.
Then it errors out on the red line on the second loop.
Code:
Private Sub DoExcelOperationsTesting_Original()
Dim ExcelInst As Excel.Application
MsgBox "DoExcelOperationsTesting"
Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)
Set rsExcelOpsResults = db.OpenRecordset("tbl_Results_ExcelOps", dbOpenDynaset)
rsExcelOpsResults.AddNew
For RunNumber = 0 To 9
Set ExcelInst = CreateObject("Excel.Application")
With ExcelInst
.Visible = True
.Workbooks.Add
.Calculation = xlCalculationManual
End With
ExcelInst.Worksheets("Sheet1").Cells(1, 1).Select
ExcelInst.Worksheets("Sheet1").Cells(1, 1).Formula = "=ROW()+COLUMN()"
Start = Timer
[COLOR="Red"][B]ExcelInst.Range(Cells(1, 1), Cells(1, (RunNumber + 1) * 100)).Select[/B][/COLOR]
Selection.FillRight
Finish = Timer
rsExcelOpsResults.Fields(((RunNumber + 1) * 1) + 1).Value = Finish - Start
Start = Timer
[COLOR="Red"][B]ExcelInst.Range(Cells(1, 1), Cells((RunNumber + 1) * 100, (RunNumber + 1) * 100)).Select[/B][/COLOR]
Selection.FillDown
Finish = Timer
rsExcelOpsResults.Fields(((RunNumber + 2) * 1) + 1).Value = Finish - Start
Start = Timer
Calculate
Finish = Timer
rsExcelOpsResults.Fields(((RunNumber + 3) * 1) + 1).Value = Finish - Start
ExcelInst.ActiveWorkbook.Close (False)
Set ExcelWkb = Nothing
ExcelInst.Quit
Set ExcelInst = Nothing
Next RunNumber
rsExcelOpsResults.Update
Set rsExcelOpsResults = Nothing
Set ws = Nothing
Set db = Nothing
End Sub
Like I said, it runs FINE the first time though the loop.
After the first time, is when it throws an error on either one of the two red lines.
I've tried quite a few ways to be more specific (to avoid the _Global), but nothing seems to work for me.
If you look two lines up and three lines up from first red line, you'll see two lines of code where you successfully use the Cells object, which is exposed by a worksheet. What I would do is declare a variable of type worksheet, assign it the value ExcelInst.Worksheets("Sheet1") and use that as required to gain access to Cells.
hth
Well, the other lines you mention, yes they use "Cells" but on the problematic lines, they are nested within the Range method.
I have to apologize for the bum code in the first post.
I had been doing some editing in the VBE and then tried to clean it up in my post and I missed some lines.
The lines where I'm calling .Fields() are all calculated incorrect.
Never-the-less, the main problem with the code is not realted to those posting errors.
Here is a new set of code which employes some other structure I had tried as well as your suggestion.
BTW, I had already tried to create a workbook object within ExceIinst and there was no joy.
Anyway, here is some different code and results detail after it:
Code:
Private Sub DoExcelOperationsTesting_New2()
Dim ExcelInst As Excel.Application
Dim ExcelWkSht As Excel.Worksheet
Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)
Set rsExcelOpsResults = db.OpenRecordset("tbl_Results_ExcelOps", dbOpenDynaset)
Set ExcelInst = CreateObject("Excel.Application")
rsExcelOpsResults.AddNew
rsExcelOpsResults!testLogID = rsLocalRunLog!testLogID.Value
For RunNumber = 0 To 19
With ExcelInst
.Visible = True
.Workbooks.Add
.Calculation = xlCalculationManual
End With
Set ExcelWkSht = ExcelInst.Worksheets("Sheet1")
ExcelWkSht.Cells(1, 1).Select
ExcelWkSht.Cells(1, 1).Formula = "=ROW()+COLUMN()"
Start = Timer
[B][COLOR="Red"] ExcelWkSht.Range(Cells(1, 1), Cells(1, (RunNumber + 1) * 100)).Select[/COLOR][/B]
Selection.FillRight
Finish = Timer
rsExcelOpsResults.Fields((RunNumber * 3) + 1).Value = Finish - Start
Start = Timer
[B][COLOR="red"] ExcelWkSht.Range(Cells(1, 1), Cells((RunNumber + 1) * 100, (RunNumber + 1) * 100)).Select[/COLOR][/B]
Selection.FillDown
Finish = Timer
rsExcelOpsResults.Fields((RunNumber * 3) + 2).Value = Finish - Start
Start = Timer
Calculate
Finish = Timer
rsExcelOpsResults.Fields((RunNumber * 3) + 3).Value = Finish - Start
ExcelInst.ActiveWorkbook.Close (False)
Set ExcelWkSht = Nothing
Next RunNumber
rsExcelOpsResults.Update
ExcelInst.Quit
Set ExcelInst = Nothing
Set rsExcelOpsResults = Nothing
Set ws = Nothing
Set db = Nothing
End Sub
so, in the above code, you can see I've moved the creating of ExcelInst outside the loop.
In theory this allows me to keep the same instsance of Excel and just repeat adding/editing/closing new workbooks.
The above code works a little better, but it is still not correct and I am a bit confused as to why.
Here is what happens.
I open the database and open the form and click the button that launches the code.
It NOW runs through to completion and ends (all loops 0 to 19).
HOWEVER, if I click it again, it errors out on the first red line on the first pass through.
I click OK on the error, and then manually close the Excel window opened by the code, and then run the button again, the code will now run successfully.
One thing i noticed is that after the first run cycle (wich runs successfully and completes) there is an orphaned Excel instance in TaskManager.
I've noticed that after the error fires, and I manually close Excel, the orphaned instance ends, and then the code will run ok.
Interestingly, if I try to manually kill the orphaned instance and then run the code, I still get an error, but the error is entirely different:
Run-time error ‘462’:
The remote Server machine does not exist or is unavailable
I am convinced this problem is a result of that orphaned Excel instance but I can't figure out how to reliably kill it when the code runs the first time through.
But the reference to Cells() shouldn't work because it hasn't been declared, and it doesn't exist in Access. I'd take a step back and make sure that in VBA you require variable declaration, so check the top of your module and you should see
Code:
Option Compare Database
Option Explicit [COLOR="Green"]'add this line if it doesn't exist[/COLOR]
Then, in a VBA code windows, go to Main Menu-->Tools-->Options-->Editor tab-->Code Settings section-->Require Variable Declaration and make sure this check box is checked. This causes VBA to require that you declare variables, and anything not declared will not compile. I think your code only works because VBA is automatically creating a variable called Cells, which is a behaviour that is unexpected. You think it is correctly accessing cells on the worksheet, and it isn't.
But as a first step, do that and see if you get any new errors, then lets resolve those errors.
no, I apprecaite your effort on my behalf, but this is off track.
You are correct, "Cells" doesn't exist in Access except that I have added a reference to Exce14.0l in the project's references.
That is how and why the normal Excel methods, including Cells and Range and Worksheets, ActiveWorksheet can work.
Also, I don't know if you saw it, but in my post at 02:56 PM I actually do mention that the second set of code does actually run, one full loop ( 0 to 19).
The problem is that an Excel instance is orphaned and I need to be able to make sure it is killed with the Sub completes or it cannot be run again.
Look, I really do appreciate your help, but if what you're saying is correct, it could not possible work, even for one cycle.
The correctnes of this is that it IS operating Access, exactly as the functions would if they were called from within Excel.
I can watch it go through.
It turns off the calculation (xlCalculationManual)
It applies the original formula, (using the Cells method)
It performs the FillRight (an Excel Constant)
it performs the FillDown (an Excel Constant)
It runs the calculation (an Excel Method)
It closes the workbook without saving using ActiveWorkbook
And it loops through it all 20 times (for RunNumer = 0 to 19)
None of these are native to Access so if the problem were Access thinking Cells was an undeclared variable, it would surely think all of those other methods were also undeclared variables.
it seems to be, the key part of the error message is not that it is reporting "Cells" but that it is somehow thinking it is being used globally "_Global".
This tells me, it cannot seem to connect the line of code to a specific worksheet in a specific workbook.
When I connect these realities to the fact that I can successfully run the code when there is no orphaned instance, and I cannot run the code when there is, it leads me to the seemingly inescapable conclusion that some how the ExcelInst object is confused about the residual Excel instance from the previous run.
If that instance would or could be made to go away it seems to be the whole thing would work everytime, not just for one full cycle.
I totally do understand what your saying about undeclared variables.
I have protected against that by referencing the Excel14 object model.
so, the VBA does know what these methods are.
Do you, or does anybody else have any insights as to how I can avoid the residual Excel instance after the Loop cycle finishes?
Reread my first post in this thread. Cells, as an unqualified reference, is not valid in Access. The compiler will either create a new variable implicitly, namely an empty variant, or the reference will be ambiguous, which is to say that it will not be able to be resolved with certainty. Setting a reference to Excel will not remedy this problem, since setting such a reference does not bring into scope--in Access VBA--a global object that exposes a member named Cells.
To solve that problem, qualify the reference. An Excel.Application object exposes Cells, as does Excel.Range and Excel.Worksheet.
. . . but that is your reference to the Excel.Application object. At that point you will never be able to run ExcelInst.Quit, and you will never be able to shut it down that way. Don't set that object to nothing until after you run Quit.
I really can't explain how this thread has gone so far off track, or why it is I am having to re-explain so many things that seem completely contradictory to what you are saying.
First:
I do declare an object reference, ExcelInst as an Excel.Application and then going even further, I also declare ExcelWkSht as a Worksheet member of ExcelInst.
If you look at my code, I call Cells from there. and yes, remarkably, it actually does work.
Also, if you re-read my code, I don't set ExcelInst to "Nothing" in the loop, I set ExcelWkSht to nothing.
I don't know if you ever tried it, but if you set an object to nothing, and then try to call something from it, VBA will throw an "Object Required" error.
Again, I get no such error.
I just set the Worksheet object to nothing, at the end of the loop, so a new one can be created back at the top of the loop, again, within the ExcelInst Excel.Application object.
I'm not sure if your getting this, but yes, the code actually does ruin, and works all the way through the loop, 20 times and ends the sub successfully.
Again, I appreciate what your saying, but if even one thing of what you're saying were applicable, it would fail before it ever got through the loop even one time.
As I've thought through this more, I think the loop may actually be the key culpret here.
I'm wondering if the orphaned Excel instance is stranded and cannot close becuase it was created inside a loop that has not yet completed. But, I'm not sure about that becuase if that were copmpletely true, it seems like I might have 20 orphaned instances instead of just one.
Try the attached database, open the form and hit the button "Run Excel".
I've comment out the data parts, because I haven't the database/table you referee to.
I'm using Excel 2000 so I've also change the (RunNumber + 1) * 100 to (RunNumber + 1) * 10., (horizontal amount of cells in 2000 version is 256).
The problem is not how many times it is run in the loop, it occurs each second time you open Excel, I think it is something in MS-Access which isn't got clean up/set back until one error occurs or MS-Access is closed down.
Hej JHB,
Tak for din prøve, og ja, det virker!
Jeg kan godt lide din brug af Split.
Der var ikke sket for mig.
Dog, vi stadig ende op med en forældreløse Excel forekomst med loop.
Jeg har tænkt mig at se på en anden måde at få samme arbejdet gjort uden den forældreløse forekomst og vil integrere din brug af Split til den nye proces.
The problems was, clearly, the orphaned instance of Excel.
The question was, what was causing it to be orphaned.
For example, if I used all the code from the second code set I posted yesterday, except comments out all the functional Excel code and put a message box there, Access would create the Excel Objects, fire the message box and then close the Excel Object and the Excel instance disappeared just fine on every loop pass.
Even if I allowed the first set of method Cells to remain, still, when Access Quit the ExcelInst, the Excel process died along with it, just as it needs to.
JHB’s method of using Split did allow the code to more or less bully its way through the nested Cells usage, but it still left an orphaned instance.
And, even worse than with my code, the instance left behind did not die when Access was closed.
This is for production Testing purposes internally here and I can’t be having anything like that happening so even though the Split method did more or less force cells to work, it did not really solve the problem.
So, getting back to my diagnostic process, I started adding, line by line, my original code and seeing where the instance became orphaned.
IT SEEMS IMPORTANT TO REMEMBER, and what I think lagbolt was missing, or at least failing to articulate a response for, is that the code, as posted in the second code set yesterday, actually does work, and works perfectly. All the Excel functions work exactly as they are expected to work if the code was being called in Excel.
The real problem was, these functions were not specific enough such that after the instance was orphaned on the first Loop pass, on the subsequent loops, Access could not tell on which Excel instance the functions needed to be applied, the current, or the orphaned.
What I did not realize at the time was that if I had set any of the other lines, WHICH WERE NOT CALLING CELLS, the same error would be thrown.
So, what I actually discovered goes back, at least partially to what lagbolt was writing yesterday.
And I need to try and be very clear about this.
The partial connection had ZERO to do with why the functions erred out on subsequent runs, but EVERYTHING to do with why the instance was getting orphaned in the first place.
It turns out, every one of the Excel functions I called, EVERY ONE OF THEM, worked perfectly one time, but because they were not fully qualified when they ran, created the orphaned instance.
This was true of the Cells, Selection.FillRight, SelectionFillDown and Calculate.
So, in the end it had absolutely ZERO to do with undeclared functions or variables, but everything to do with making sure Access new with which object the given function was being applied.
As I go back and re-read lagbot’s posts, in his first post, he does mention declaring a variable for a Worksheet object. If what he intended me to realize from that post, was what I have now discovered about the orphaned instance problem, then I think maybe we both could learn a bit about better communication. To me then, and even now, I cannot seem to connect the first sentence where he mentions I successfully used Cells to the second sentence where it fails. In my mind, the line of code was declared to the Excelinst. It did not dawn on me then that the actual usage of the Cells itself needed to also be qualified.
And even if I had, and even if that had allowed the nested Cells line to work, I would have still hit the error, and also created the orphaned instance as soon as I got to Selection.FillRight.
As for the posts after that, I’m sorry lagbolt, but even though you mentioned the reference to Cells() shouldn’t work, the truth is that it did, and just exactly as expected, (visually).
Here is the fully functional code which not only works (remember the original code also worked), but also enables Access to keep track of the instances so when I call .Quit, the process truly does die.
Code:
Private Sub DoExcelOperationsTesting()
Dim ExcelInst As Excel.Application
Dim ExcelWkb As New Excel.Workbook
Dim ExcelRange As Excel.Range
Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)
Set rsExcelOpsResults = db.OpenRecordset("tbl_Results_ExcelOps", dbOpenDynaset)
rsExcelOpsResults.AddNew
rsExcelOpsResults!testLogID = rsLocalRunLog!testLogID.Value
For RunNumber = 0 To 19
Set ExcelInst = CreateObject("Excel.Application")
Set ExcelWrkBk = ExcelInst.Workbooks.Add
ExcelInst.Calculation = xlCalculationManual
Set ExcelWrkSht = ExcelWrkBk.Worksheets("Sheet1")
ExcelWrkSht.Cells(1, 1).Select
ExcelWrkSht.Cells(1, 1).Formula = "=ROW()+COLUMN()"
Start = Timer
ExcelWrkSht.Range(ExcelWrkSht.Cells(1, 1), ExcelWrkSht.Cells(1, (RunNumber + 1) * 100)).Select
ExcelInst.Selection.FillRight
Finish = Timer
rsExcelOpsResults.Fields((RunNumber * 3) + 1).Value = Finish - Start
Start = Timer
ExcelWrkSht.Range(ExcelWrkSht.Cells(1, 1), ExcelWrkSht.Cells((RunNumber + 1) * 100, (RunNumber + 1) * 100)).Select
ExcelInst.Selection.FillDown
Finish = Timer
rsExcelOpsResults.Fields((RunNumber * 3) + 2).Value = Finish - Start
Start = Timer
ExcelInst.Calculate
Finish = Timer
rsExcelOpsResults.Fields((RunNumber * 3) + 3).Value = Finish - Start
ExcelWrkBk.Close (False)
Set ExcelWrkSht = Nothing
Set ExcelWrkBk = Nothing
ExcelInst.Quit
Set ExcelInst = Nothing
Next RunNumber
rsExcelOpsResults.Update
Set rsExcelOpsResults = Nothing
Set ws = Nothing
Set db = Nothing
End Sub
Thank you to lagbolt and JHB for hanging in there with me!
I appreciarte your time and patience.
