时间戳

首页 > 解决方案 > MS Access 运行时 3011 错误 - 变量范围

vba - MS Access 运行时 3011 错误 - 变量范围

问题描述

对此的任何帮助将不胜感激。

我正在尝试使用电子表格填充 Access 中的表格，并指定要读取的文件、工作表和范围。不幸的是，excel中的范围没有前缀。

运行时错误 3011：Microsoft Access 数据库引擎找不到对象“SheetName$A:Z10”。确保对象存在并且正确拼写其名称和路径名。如果“SheetName$A:Z10”不是本地对象，请检查您的网络连接或联系服务器管理员。

DoCmd.TransferSpreadsheet acImport, 10, "TableName", URL & "\" & fileimport, True, "SheetName!" & "A1:Z" & N - 1

其中 N 是行数。如您所见，指定选项卡和范围调试的字符串很好作为 SheetName!A1:Z10（例如 N=11），但在错误消息中，Access 已替换“！” 用“$”，整个事情都出错了。

任何想法为什么会发生这种情况，以及如何解决它？

完整程序如下。

Private Sub rapportoExcel_succ(singoloExcel As Integer)

Dim nomefile, SzSQL, URL, Tabella, foglio1, foglio2 As String
Dim N, M As Integer
Dim Workbook1 As Object
Dim ImportList As Boolean
Set wks1 = DBEngine.Workspaces(0)
Set db = wks1.Databases(0)
Set Workbook1 = CreateObject("Excel.Application")

If IsNull(URL_name) Then
    URL = CurrentProject.Path
    Else
    URL = URL_name
End If

If IsNull(Date1) Then
    Else
    dstep = Date1
End If

qa = MsgBox("Are you sure?", vbYesNo)
If qa <> 6 Then Exit Sub

If Me!ImportList = -1 Or Me.Recordset.EOF Then Me.Recordset.MoveFirst

Do
     
Me.Repaint

If Me!ImportList = -1 Then
     fileimport = Me!nomefile & ".xlsx"
     Else
     fileimport = NomeDoc1 & ".xlsx"
End If
     
Workbook1.Workbooks.Open URL & "\" & fileimport, False
Workbook1.Visible = True
Workbook1.DisplayAlerts = False

With Workbook1.ActiveWorkbook
    .Unprotect ("a")
    foglio1 = .Sheets(3).Name
    .Sheets(3).Select
    .Unprotect ("a")
    .Sheets.Add
    .Sheets(3).Move before:=Sheets(1)
    .Sheets(1).Name = "SheetName"
    
    .Sheets("FullTable").Select
    .Sheets("FullTable").Range("C2").Select
    
End With

Workbook1.ActiveWorkbook.Sheets("FullTable").Range("C2").FormulaR1C1 = "=COUNTA(R[1]C:R[10000]C)"

N = Workbook1.ActiveWorkbook.Sheets("FullTable").Range("C2").Value + 2

If IsNull(dstep) Then Else dstep = Workbook1.ActiveWorkbook.Sheets("FullTable").Range("B1").Value

Workbook1.ActiveWorkbook.Sheets("FullTable").Range("X3:X" & N).Value = dstep 

Workbook1.ActiveWorkbook.Sheets("FullTable").Range("A3:X" & N).Copy

Workbook1.ActiveWorkbook.Sheets("SheetName").Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Workbook1.CutCopyMode = False

Workbook1.ActiveWorkbook.Sheets("SheetName").Columns("I").Delete Shift:=xlToLeft

Workbook1.ActiveWorkbook.Sheets("SheetName").Columns("J").Delete Shift:=xlToLeft

Workbook1.ActiveWorkbook.Sheets("SheetName").Columns("K").Delete Shift:=xlToLeft

Workbook1.ActiveWorkbook.Sheets("SheetName").Range("A1").FormulaR1C1 = "F1"

Workbook1.ActiveWorkbook.Sheets("SheetName").Range("B1").FormulaR1C1 = "F2"

With Workbook1.ActiveWorkbook
    .Sheets("SheetName").Select
    .Sheets("SheetName").Range("A1:B1").AutoFill Destination:=.Sheets("SheetName").Range("A1:Z1"), Type:=xlFillDefault
    
End With

DoCmd.TransferSpreadsheet acImport, 10, "TableName", URL & "\" & fileimport, True, "SheetName!" & "A1:Z" & N - 1

ActiveWorkbook.Close SaveChanges:=False
If Me!ImportList = -1 Then Me.Recordset.MoveNext
Loop Until Me.Recordset.EOF Or Me!ImportList = 0

MsgBox "Import complete" & IIf(Me.ImportList = 0, "1 group", Me.Recordset.RecordCount & " groups")

Workbook1.Quit

End Sub

标签： vbams-access

解决方案

代码的问题是变量和对象没有很好地定义，从名为 WorkBook1 的 Excel App 开始，而不是 ExcelApp。这会带来混乱。重写代码并避免“Active__”对象全部修复。

推荐阅读