Running Excel from inside Access Error 462

This is making me nuts.  I'm using late binding, I have closed and set to nothing all my objects, and I'm still getting a 462 error on line 100 the second time this code executes.  I need to get this fixed, but more importantly, I need to understand why --even with the closing and nothing-- the error is still being thrown.

Paul

<><><>
Public Sub ManipulateExcel()
10    On Error GoTo HandleError

      Dim xlApp As Object, xlWorkBook As Object, xlWorkSheet As Object
      Dim strFilePathAndName As String
      Dim k As Variant, i As Long, n As Long
     
20    strFilePathAndName = "C:\CommissionSpreadsheets\ExcelManipulationSample.xlsx"
     
30    Set xlApp = CreateObject("Excel.Application")
40    Set xlWorkBook = xlApp.Workbooks.Open(strFilePathAndName)

50    xlApp.Visible = True  'must be after creation of the new instance

60    Set xlWorkSheet = xlWorkBook.Worksheets(3)  'must be the index number, not the name, of the worksheet!

70        With xlWorkSheet
80      xlWorkSheet.Range("A1").EntireColumn.Insert
90      xlWorkSheet.Range("$A$1").Value = "SpreadsheetRowID"
100       With xlWorkSheet.Range("a2:a" & .Range("b" & Rows.Count).End(xlUp).row)
110            k = .Value
120               For i = 1 To UBound(k, 1)
130               If Len(k(i, 1)) = 0 Then
140                  n = n + 1
150                  k(i, 1) = n
160               End If
170            Next
180         .Value = k
190       End With
200     End With

'close down Excel:
210   Set xlWorkSheet = Nothing
220   xlWorkBook.Save
230   xlWorkBook.Close
240   Set xlWorkBook = Nothing
250   xlApp.Quit
260   xlApp.Application.Quit
270   Set xlApp = Nothing
               
ExitSub:
280      DoCmd.SetWarnings True
290      Exit Sub

HandleError:
380      DoCmd.SetWarnings True
390      MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf & _
        "Line:  " & Erl & vbCrLf & _
        "Sub 'ManipulateExcel'  in Module 'ExcelCode'", vbOKOnly, strAppNa
400      Resume ExitSub

EndSub:
   End Sub
LVL 8
Paul Cook-GilesSenior Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
Have a look here
https://anictteacher.files.wordpress.com/2011/11/vba-error-462-explained-and-resolved.pdf
It's worth a shot.

Clearly, Excel isn't closing at the end of each run.
Fire up the task manager.
DISABLE error handling first-off
(later look at your error handler -- you HAVEN'T released your objects in it, so any error you throw and exit on will leave an orphan Excel.exe object kicking around to muck up subsequent code -- but that can be fixed after.
I don't see you turn warnings off--but I see you turn them back on.)


Now, put breakpoints on line 30 the 200-series lines
Run the code.
Watch the task manager
Excel.exe should show up in Task Manager after line 40 and be gone by line 280
I see you make Excel visible
Generally, with that, I also allow user control, too

xlApp.Visible = True
xlApp.UserControl = True


I think by doing so, you assign the object to the general userland, and when the 200-series lines execute, Access will let go of Excel, and if Excel survives it'll be visible and controllable and not Access's problem.

The first link referenced suggests that every object call must be fully qualified
ie xlApp.xlSheet and not just xlSheet.

I don't do that, but then I don't late bind, either.
Does the problem manifest, code unchanged, if you give Access the Excel reference?

Check this too
http://support.microsoft.com/kb/178510
it suggests that your problem is this line
With xlWorkSheet.Range("a2:a" & .Range("b" & Rows.Count).End(xlUp).row)
In the brackets you are using .Range and Rows.Count without object qualifications, and that causes Excel to fire an instance that doesn't get closed.
Nick67Commented:
The line 100 fix would likely be easiest thing to try first.
Break it down to a variable

Dim WantedRange as string
Dim EndRow as Long
EndRow = xlWorkSheet.Range("b" & xlWorkSheet.Rows.Count).End(xlUp).row
'I'm guessing that may produce your result, but you get the idea
WantedRange = "a2:a" & EndRow
With xlWorkSheet.Range(WantedRange)
Paul Cook-GilesSenior Application DeveloperAuthor Commented:
Nick, thank you;  adding xlWorkSheet. in front of Range and Rows.Count let the Excel running in Taskmanager close, and the code will now run repeatedly without blowing up.  :)  Now I need to understand why.

Is  'qualification' giving every object its full name?  And how does the code in your second comment (defining WantedRange as string, making EndRow a number) work?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Paul Cook-GilesSenior Application DeveloperAuthor Commented:
one more question:  Is there a way (equivalent to Option Explicit, I suspect) to require that all objects are fully qualified?
Nick67Commented:
one more question:  Is there a way (equivalent to Option Explicit, I suspect) to require that all objects are fully qualified?
Not that I know of

Is  'qualification' giving every object its full name?
'Fully qualifying' is giving something its complete name
Dim SomeValue as integer
me.SomeControl = SomeValue
This works but in the background, there's a lot of assumptions and default assignments going on that depend upon context, because, fully qualified, this is what is really happening (or close to it, because we never really need to do it, so I may not have it perfectly written)
Application.AllForms("TheNameOfThePresentForm").Form.Controls("SomeControl").Value = SomeValue

So, the problem was that the assumptions your syntax caused, weren't the ones you wanted
With xlWorkSheet.Range("a2:a" & .Range("b" & Rows.Count).End(xlUp).row)
The two bold items weren't qualified, so the VBA engine takes a shot at figuring out what you want.  Now you didn't explicitly tell it to work with xlSheet, so it first guess is to fire up and Excel instance of its own to deal with the issue, and perhaps -- because in some ways, you'd think the .Range call should fall down and error, and not take a second guess -- then the code realizes it should use your objects, and proceeds along -- but that extra handle to Excel from the first bad guess: it hangs around and causes grief.

The takeaway: when you are automating Excel from within Access, stay away from the familiar Excel syntaxes that DON'T begin with objects (ActiveSheet, Sheets(), Workbook etc)  Every bit of Excel syntax needs to begin with an object YOU created in Access

Dim xlApp As Object, xlWorkBook As Object, xlWorkSheet As Object
xlApp.Workbooks.open(somePath) not Workbooks.open(somePath)
xlWorkBook.Worksheets.Add not WorkSheets.Add
xlWorkSheet.Range(SomeRange) not Range(SomeRange)

And how does the code in your second comment (defining WantedRange as string, making EndRow a number) work
It may not be syntactically correct -- but I could see that you were building up as string like
"A1:A256"
to use in
With xlWorkSheet.Range("A1:A256")
My suggestion was to break that complex statement down.
The second bit of that code & .Range("b" & Rows.Count).End(xlUp).row
was clearly looking to generate a number.
Excel syntax isn't really my thing, but we both know that
EndRow = .Range("b" & Rows.Count).End(xlUp).row
would have gone bang, and you would have been forced to fix it -- and the 462 error at the same time.

You may have been fooled because you were constructing a nested with statement.
You were building
With xlWorkSheet.Range(WantedRange)
    .something
    .somethingelse
    .more
end with

But you don't get to use .notations to refer to the new with item until after the with statement is complete, and the reference to the first with item with .notation wasn't appreciated by the compliler.
This probably would have worked
            Dim myRange as Object
70        With xlWorkSheet
 80          .Range("A1").EntireColumn.Insert
 90          .Range("$A$1").Value = "SpreadsheetRowID"
 100         MyRange = .Range("a2:a" & .Range("b" & Rows.Count).End(xlUp).row)
             End with
             With MyRange
 110            k = .Value
 120               For i = 1 To UBound(k, 1)
 130               If Len(k(i, 1)) = 0 Then
 140                  n = n + 1
 150                  k(i, 1) = n
 160               End If
 170            Next
 180         .Value = k
 200     End With

Hopefully that makes sense to you.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
This is a duplicate of http://www.experts-exchange.com/Database/MS_Access/Q_28632290.html

I wrote that you should declare the ranges explicitly to be able to debug it. If this really "is driving you nuts" I don't know why you refrain from such simple action.

Excel is extremely sensitive to mishandling of objects but it is not very difficult to do it right.

/gustav
Paul Cook-GilesSenior Application DeveloperAuthor Commented:
Gustav, you're correct-- but I didn't have enough background to understand what you said.
Nick, thank you;  I understand this, and will be saving your comments for the next time I have to talk to Excel.  :)

Thanks to you both!

PCG
Wayne Taylor (webtubbs)Commented:
Line 100 should read this....

    xlWorkSheet.Range("a2:a" & xlWorkSheet.Range("b" & xlWorkSheet.Rows.Count).End(-4162).Row)

...as "Rows" (as mentioned previously by Nick) and "xlUp" would not have been declared.
Nick67Commented:
Good catch Wayne!
With Late Binding, constants like xlUp either have to be defined by the coder
Private Const xlUp as Long = -4162
or as Wayne has done, substitute the numerical value in, in place of 'xlUp'
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.