Excel VBA运行时错误'1004'对象'_Global'的方法'Range'失败 [英] Excel VBA Run-Time Error '1004' Method 'Range' of object'_Global' Failed

问题描述

我有一个MS Access数据库，该数据库用于跟踪组的每日统计信息.我创建了一个按钮，该按钮会将查询结果导出到.xlsx，然后创建并设置数据透视表的格式，以供组管理人员审核.

  DoCmd.OutputTo acOutputQuery，"qry_daily_report"，"ExcelWorkbook(*.xlsx)"，"Z:\ Users \ jondoe \ .......... \ Desktop \ Export\ qry_daily_report.xlsx"，"False"，，"，acExportQualityPrint昏暗的appExcel作为Excel.Application将MyWorkbook变暗为Excel.Workbook设置appExcel = CreateObject("Excel.Application")设置myWorkbook = appExcel.Workbooks.Open("Z:\ Users \ jondoe \ .......... \ Desktop \ Export \ qry_daily_report.xlsx")appExcel.Visible = TrueActiveSheet.ListObjects.Add(xlSrcRange，Range("$ A $ 1:$ H $ 50000")，，xlYes).Name =" qry_daily_report 

我第一次单击按钮时，导出和数据透视表的创建可以按预期完成.但是，当我关闭.xlsx并再次单击该按钮时，导出将起作用，但是用于创建数据透视表的代码将无法执行.相反，我得到一个错误...对象'_Global'失败的Excel VBA运行时错误'1004'方法'范围'".当我尝试对其进行调试时，以下错误将突出显示.

  ActiveSheet.ListObjects.Add(xlSrcRange，Range("$ A $ 1:$ H $ 50000")，，xlYes).Name =" qry_daily_report 

我发现，如果我关闭数据库并重新打开它，则导出和数据透视表同样可以正常工作.同样，如果我保存数据库并单击按钮，则导出和数据透视表将正常运行.这两种选择都不适合我的客户.

请问有人对如何解决此错误有想法吗?

解决方案

Range("$ A $ 1:$ H $ 50000")是从全局而不是从Excel应用程序调用的./p>

对于 ActiveSheet 也是如此.

如果不使用Excel本身，请始终明确说明正在使用的 Excel实例.

  appExcel.ActiveSheet.ListObjects.Add(xlSrcRange，appExcel.ActiveSheet.Range("$ A $ 1:$ H $ 50000")，xlYes).Name =" qry_daily_report 

I have an MS Access database that is used to track daily statistics for a group. I have created a button that will export the results of a query to a .xlsx and then create and format a pivot table for review by the groups management.

DoCmd.OutputTo acOutputQuery, "qry_daily_report", "ExcelWorkbook(*.xlsx)", "Z:\Users\jondoe\..........\Desktop\Export\qry_daily_report.xlsx", False, "", , acExportQualityPrint



Dim appExcel As Excel.Application
Dim myWorkbook As Excel.Workbook

Set appExcel = CreateObject("Excel.Application")
Set myWorkbook = appExcel.Workbooks.Open("Z:\Users\jondoe\..........\Desktop\Export\qry_daily_report.xlsx")
appExcel.Visible = True


ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$H$50000"), , xlYes).Name = "qry_daily_report"

The FIRST time i click the button the export and pivot table creation works perfectly as intended. However, when I close the .xlsx and then click the button again, the export will work but the code to create the pivot table will not execute. Instead I get an error..."Excel VBA Run-Time Error '1004' Method 'Range' of object'_Global' Failed". When I try to debug it, I following is highlighted as the error.

 ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$H$50000"), , xlYes).Name = "qry_daily_report"

I find that if I close the database and reopen it, again, the export and pivot table works perfectly. Likewise, If I save the database and the click the button, the export and pivot table work perfectly. Neither option is reasonable for my client.

Anybody have an idea on how I can remedy this error, please?

解决方案

Range("$A$1:$H$50000") is called from global, not from the Excel application.

Same goes for ActiveSheet.

Always be explicit about which Excel instance you're using if you're not using Excel itself.

appExcel.ActiveSheet.ListObjects.Add(xlSrcRange, appExcel.ActiveSheet.Range("$A$1:$H$50000"), , xlYes).Name = "qry_daily_report"

这篇关于Excel VBA运行时错误'1004'对象'_Global'的方法'Range'失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！