Run time error 462 Access VBA using Excel

Question

I occasionally get a run time error when trying to open/manipulate Excel files using Access VBA. The error is

"Run-Time error '462': The remote server machine does not exist or is unavailable

What is frustrating is that the error occurs only for certain files and not others and in different instances. Here is my code, the error occurs at the workbooks.open(sPath) line:

    DoCmd.SetWarnings False

Dim oExcel As New Excel.Application
Dim oWB As Workbook
Dim oWS As Worksheet

Set oExcel = Excel.Application
Set oWB = oExcel.Workbooks.Open(sPath)
Set oWS = oWB.Sheets(1)

oExcel.Visible = False

    If fGetFileName(sPath) = "FILE_NAME1.xlsx" Then
        'oExcel.Visible = False
        oWS.Range("AW1").Value = "TEXT1"
        oWS.Range("AX1").Value = "TEXT2"
        oWS.Range("AY1").Value = "TEXT3"
    End If

oWB.Save
Debug.Print "Amended " & sPath

oWB.Close False
Set oWB = Nothing

oExcel.Quit
Set oExcel = Nothing

DoCmd.SetWarnings True

After a bit of research online, I've found this document gives a good overview of the error: https://anictteacher.files.wordpress.com/2011/11/vba-error-462-explained-and-resolved.pdf

Using the logic from that document, the error is that:

object has not been fully qualified by reference to the Office object in every case

However, I amended the row where the error occurs to specifically reference the Excel object (Set oWB = oExcel.Workbooks.Open(sPath)). Have tried declaring the dimensions as Objects and put reference to oExcel in every mention of a workbook/sheet. Any ideas? Does sPath need to better qualified?

What is the value of sPath when you encounter that error at Workbooks.Open(sPath)? — HansUp
– HansUp, Commented Jun 4, 2015 at 17:09

HansUp · Accepted Answer · 2015-06-04 17:07:53Z

You declare oExcel as a new Excel.Application:

Dim oExcel As New Excel.Application

That means later in your code, Set oExcel = Excel.Application is not really useful ... because oExcel is already a reference to Excel.Application. Add in a MsgBox to demonstrate what oExcel is at that point ...

MsgBox "TypeName(oExcel): " & TypeName(oExcel)
'Set oExcel = Excel.Application

Consider a different approach for your Excel object variable. Test this stripped down procedure (the purpose is only to see whether it eliminates your current error):

Dim oExcel As Excel.Application
Dim oWB As Excel.Workbook

DoCmd.SetWarnings True

MsgBox "TypeName(oExcel): " & TypeName(oExcel)
Set oExcel = New Excel.Application
MsgBox "TypeName(oExcel): " & TypeName(oExcel)
Set oWB = oExcel.Workbooks.Open(sPath)
oWB.Close False
Set oWB = Nothing
oExcel.Quit
Set oExcel = Nothing

Gustav · Accepted Answer · 2015-06-04 19:21:18Z

0

Also, do use WorkSheet and close this:

Set oWS = oWB.WorkSheets(1)
' snip.
oWB.Save
Set oWs = Nothing    
oWB.Close False
Set oWB = Nothing

answered Jun 4, 2015 at 19:21

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Collectives™ on Stack Overflow

Run time error 462 Access VBA using Excel

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related