VBA运行时错误1004:尝试在Excel 2013中创建表时，对象_Global的方法范围失败 [英] VBA Run-time error 1004: Method Range of object _Global failed when trying to create tables in Excel 2013

问题描述

我知道以前曾发布过类似的错误错误问题，但是在格式化表格时我什么也没发现，所以请不要关闭它.在MS Access 2013中的VBA代码中，它将数据从MS Access导出到Excel. 6个不同的查询被导出到1个excel文件中，每个文件都在不同的工作表上.这很好.然后，我将每张纸格式化以将所有数据存储在一个表中.我有一个表格，可以让用户选择保存文件的路径.如果是第一次创建文件，则它可以正常工作.如果是第二次在同一目录中创建文件，则无法正常工作，并且出现错误:

I understand similar questions with these errors have been posted before, but I found nothing when it came to formatting tables so don't close this please. In my VBA code in MS Access 2013 it exports data from MS Access to Excel. 6 different queries get exported into 1 excel file, each on a different worksheet. This works fine. I then format each sheet to have all the data in a table. I have a form which lets the user choose the path to save the file. If it is the first time creating the file, it works properly. If it is the second time creating the file in that same directory, it doesn't work and it gives me the error:

运行时错误1004:对象_Global的方法范围失败

Run-time error 1004: Method Range of object _Global failed

我认为这是因为我要覆盖文件而不是删除它并重新创建它.因此，我添加了一些代码来检查文件是否存在，如果存在，则将其删除.我添加了断点，并且在遍历代码的这一部分时，我正在查看我的documents文件夹.该文件已成功删除，然后重新创建了我想要的文件.它仍然给我那个错误.我手动去删除了文件，然后再次重新运行我的代码.它工作正常.

I figured this was because I was overwriting my file instead of deleting it and recreating it. So I added in some code to check if the file exists, and if it does, delete it. I added breakpoints and while running through this part of the code, I was watching my documents folder. The file successfully got deleted and then recreated which is what I wanted. It still gave me that error. I manually went to delete the file and then reran my code again. It worked properly.

为什么我需要手动删除此文件才能重新运行我的代码?还是导致问题的其他原因?这是我代码的重要部分，因为整个内容太长了，无法发布:

How come I need to manually delete this file in order to rerun my code? Or is it something else that is causing the problem? Here is the important parts of my code as the whole thing is too long to post:

'Checks if a file exists, then checks if it is open
Private Sub checkFile(path As String)

Dim openCheck As Boolean
'If file exists, make sure it isn't open. If it doesn't, create it
If Dir(path) <> "" Then
    openCheck = IsFileLocked(path)
    If openCheck = True Then
        MsgBox "Please close the file in " & path & " first and try again."
        End
    Else
        deleteFile (path)
    End If
Else

End If

End Sub

Sub deleteFile(ByVal FileToDelete As String)

    SetAttr FileToDelete, vbNormal
    Kill FileToDelete

End Sub

Private Sub dumpButton_Click()

On Error GoTo PROC_ERR

Dim path As String
Dim testBool As Boolean

path = pathLabel4.Caption
path = path & Format(Date, "yyyy-mm-dd") & ".xlsx"

checkFile (path)
dumpQueries (path)
formatFile (path)

'Error Handling
PROC_ERR:

If Err.Number = 2001 Then
    MsgBox "A file may have been sent to " & path
    Exit Sub
ElseIf Err.Number = 2501 Then
    MsgBox "A file may have been sent to " & path
    Exit Sub
ElseIf Err.Number = 3021 Then
    MsgBox "A file may have been sent to " & path
    Exit Sub
ElseIf Err.Number = 2302 Then
    MsgBox "A file may have been sent to " & path
    Exit Sub
ElseIf Err.Number = 0 Then
    MsgBox "Your file has been stored in " & pathLabel4.Caption
    Exit Sub
Else
    MsgBox Err.Number & ": " & Err.Description & vbCrLf & vbCrLf & "New Error. Please contact the IT department."
End If


Private Sub dumpQueries(path As String)

Dim obj As AccessObject, dB As Object

Set dB = Application.CurrentData
For Each obj In dB.AllQueries
    testBool = InStr(obj.name, "Sys")
    If testBool <> True Then
        If obj.name = "example1" Or obj.name = "example2" Then
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, obj.name, path, True, editWorksheetName(obj.name)
        End If
    End If
Next obj

End Sub


'Autofits the cells in every worksheet
Private Sub formatFile(path As String)

Dim Date1 As Date, strReportAddress As String
Dim objActiveWkb As Object, appExcel As Object

Set appExcel = CreateObject("Excel.Application")
appExcel.Visible = False
appExcel.Application.Workbooks.Open (path)

Set objActiveWkb = appExcel.Application.ActiveWorkbook
With objActiveWkb
    Dim i As Integer
    For i = 1 To .Worksheets.count
        .Worksheets(i).Select
        .Worksheets(i).Cells.EntireColumn.AutoFit
        .Worksheets(i).ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).name = "myTable1"         
    Next
End With

appExcel.ActiveWindow.TabRatio = 0.7
objActiveWkb.Close savechanges:=True
appExcel.Application.Quit
Set objActiveWkb = Nothing: Set appExcel = Nothing

End Sub

该错误发生在代码底部附近.这是一行:

The error occurs near the bottom of the code. It's the line:

.Worksheets(i).ListObjects.Add(xlSrcRange，Range("A1").CurrentRegion，，xlYes).name ="myTable1"

.Worksheets(i).ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).name = "myTable1"

我可能遗漏了几个功能，但是它们工作正常，不需要回答这个问题.

There may be a couple functions I left out but they work fine and shouldn't be needed for answering the question.

推荐答案

这是唯一相关的代码:

Set objActiveWkb = appExcel.Application.ActiveWorkbook
With objActiveWkb
    Dim i As Integer
    For i = 1 To .Worksheets.count
        .Worksheets(i).Select
        .Worksheets(i).Cells.EntireColumn.AutoFit
        .Worksheets(i).ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).name = "myTable1"         
    Next
End With

修剪绒毛并开始命名时，事情变得更容易理解-不需要.Select任何东西，appExcel已经 和对象，并且不必仅在With块中使用就引用活动工作簿的副本，特别是如果该副本无论如何都将是Object变量-如果副本是Workbook对象那么至少您将获得IntelliSense的成员...

Things get easier to follow when you trim the fluff away and start naming things - there's no need to .Select anything, appExcel is already an Application object, and there's no need to make a copy reference to the active workbook just to use in a With block, especially if that copy is going to be an Object variable anyway - if the copy were a Workbook object then you would at least get IntelliSense for its members...

您的来源范围不明确. Range("A1")在Excel-VBA中的 是对活动工作表的隐式引用.但这是Access-VBA，因此没有这样的事情，xlSrcRange是Excel对象模型中定义的枚举值，因此，如果您没有对Excel对象模型的引用(您是后期绑定对象，对吗?)，并且未指定Option Explicit，则VBA会将xlSrcRange视为另一个未声明的/未初始化的变量，因此您要在其中传递0，而xlSrcRange枚举值代表1-并且0恰好是xlSrcExternal的基础值.与xlYes相同.

Your source range is ambiguous. Range("A1") in Excel-VBA is an implicit reference to the active worksheet.. but this is Access-VBA, so there's no such thing, xlSrcRange is an enum value defined in the Excel object model, so if you don't have a reference to the Excel object model (you're late-binding this, right?), and Option Explicit isn't specified, then xlSrcRange is treated by VBA like just another undeclared/uninitialized variable, and therefore you're passing a 0 there, and the xlSrcRange enum value stands for a 1 - and 0 happens to be the underlying value for xlSrcExternal. Same with xlYes.

由于我们可能无法从您发布的代码中猜测实际的源范围，所以我给您留了以下内容:

Since we cannot possibly guess what the actual source range is supposed to be from the code you posted, I'm leaving you with this:

Dim target As Object
Dim srcRange As Object
Set srcRange = TODO

With appExcel.ActiveWorkbook
    Dim i As Integer
    For i = 1 To .Worksheets.Count
        .Worksheets(i).Cells.EntireColumn.AutoFit
        Set target = .Worksheets(i).ListObjects.Add(1, srcRange, , 1)
        If target Is Not Nothing Then target.Name = "myTable1"
    Next
End With

侧面问题...为什么在Excel已经已经将其命名为Table1时为何命名该表myTable1?还要注意，如果.Add失败，您的代码会因运行时错误91而崩溃，因为您需要从Nothing处调用.Add.在设置Name之前验证target不是Nothing可以避免这种情况.

Side question... why name the table myTable1 when Excel will already have named it Table1 anyway? Also note, if .Add fails, your code blows up with a runtime error 91 because you'd be calling .Add off Nothing. Verifying that the target is not Nothing before setting its Name will avoid that.

要在评论中回答您的问题，

To answer your question in the comments:

@ Mat'sMug这是您在说的吗?因为它给了我这个错误:"438:对象不支持此属性或方法"这是代码:.Worksheets(i).ListObjects.Add(SourceType:=xlSrcRange, Source:=.Cells(1).CurrentRegion, _ XlListObjectHasHeaders:=xlYes, TableStylename:="TableStyleMedium1").name = "Table"

之所以抛出438，是因为您的With块变量是Workbook对象，而Workbook对象没有.Range成员.

The reason this throws a 438 is because your With block variable is a Workbook object, and a Workbook object doesn't have a .Range member.

我所说的是，在Excel VBA中，对Range，Row，Column和Cells的不合格调用隐式引用了ActiveSheet，而对Worksheets，Sheets和Names隐式引用了ActiveWorkbook-这是许多VBA代码中经常出现的问题，并且是一个非常常见的错误.解决方案基本上是说出您的意思，然后说出您的意思；在这种情况下，失败是按您的意思"-根据错误消息，不合格的Range("A1")调用是调用[_Globals].Range("A1") ...，这很奇怪，因为这意味着您引用的是Excel对象模型库，这意味着您的后期绑定变量和Object变量也可以早绑定:为什么当您已经 引用您所使用的库时，为什么要处理Object变量并且缺少IntelliSense?重新绑定到?

What I was talking about, is that in Excel VBA unqualified calls to Range, Row, Column, and Cells are implicitly referencing the ActiveSheet, and unqualified calls to Worksheets, Sheets and Names are implicitly referencing the ActiveWorkbook - that's a recurrent problem in a lot of VBA code and a very common mistake to make. The solution is basically to say what you mean, and mean what you say; in this case the failure is on "mean what you say" - the unqualified Range("A1") call is, according to the error message, calling [_Globals].Range("A1")... which is weird because it implies that you're referencing the Excel object model library, which means your late-binding and Object variables could just as well be early-bound: why deal with Object variables and lack of IntelliSense when you're already referencing the library you're late-binding to?

这篇关于VBA运行时错误1004:尝试在Excel 2013中创建表时，对象_Global的方法范围失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！