VBA中vba将一列range赋值给数组作用对象失败是什么原因？

来源：蜘蛛抓取(WebSpider) 时间：2023-12-29 13:00 标签： vba将一列range赋值给数组
本文介绍了Excel VBA运行时错误'1004'对象'_Global'的方法'Range'失败的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述我有一个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屋！

我要回帖

更多关于 vba将一列range赋值给数组 的文章

 

随机推荐