运行时错误462使用Excel访问VBA [英] Run time error 462 Access VBA using Excel

问题描述

当尝试使用Access VBA打开/操作Excel文件时，偶尔会出现运行时错误.错误是

运行时错误'462':远程服务器计算机不存在或正在运行 不可用

令人沮丧的是，该错误仅在某些文件中发生，而在其他情况下不发生在其他文件中.这是我的代码，错误发生在workbooks.open(sPath)行:

    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

在网上进行了一些研究之后，我发现该文档很好地概述了该错误:

解决方案

您将 oExcel 声明为新的Excel.Application:

 Dim oExcel As New Excel.Application
 

这意味着在您的代码后面，Set oExcel = Excel.Application并没有真正的用处...因为 oExcel 已经是对Excel.Application的引用.添加MsgBox以演示此时的 oExcel 是什么...

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

为Excel对象变量考虑另一种方法.测试这个简化的过程(目的只是看它是否消除了您当前的错误):

 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
 

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?

解决方案

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

这篇关于运行时错误462使用Excel访问VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！