用于将 Excel 工作簿打开到启用加载项的特定工作表的 Visual Basic 脚本 [英] Visual Basic script to open Excel workbook to specific worksheet with add-ins enabled

问题描述

在安装了 64 位 Microsoft Office Professional Plus 2016 的 Windows 7 Enterprise x64 主机上工作.

Working on a Windows 7 Enterprise x64 host with 64-bit Microsoft Office Professional Plus 2016 installed.

我想创建 Visual Basic 脚本 vbscript(我的 Windows 桌面上的文件)启动 Microsoft Excel，打开启用宏的 Excel 工作簿文件Workbook.xlsm"，然后选择/激活该工作簿中的工作表XYZ".因此，我使用以下 VB 脚本:

I'd like to create Visual Basic script vbscript (a file on my Windows desktop) that launches Microsoft Excel, opens the macro-enabled Excel workbook file "Workbook.xlsm", and selects/activates the worksheet "XYZ" within that workbook. So, I woite the following VB script:

' File: test.vbs
' Opens Excel workbook "Workbook.xlsm" to worksheet "XYZ"
Set objXl = CreateObject("Excel.Application")
Set ObjWb = objXl.Workbooks.Open("C:\Path\To\Workbook.xlsm")
objXl.Goto ObjWb.Sheets("XYZ").Range("A1")
objXl.Application.Visible = True

此脚本成功启动excel，打开指定的工作簿，并选择指定的工作表.

This script successfully launches excel, opens the specified workbook, and selects the specified worksheet.

但是，所有包含调用一个或多个插件模块中定义的函数的公式的工作表单元格都已损坏；这些单元格都包含错误值#NAME?.

However, all worksheet cells containing a formula that invokes one or more functions defined within an add-in module are all broken; these cells all contain the error value #NAME?.

如果我手动关闭并重新打开工作簿(不使用脚本)，则包含调用加载项模块中定义的一个或多个函数的公式的工作表单元格都可以正常工作.

If I manually close and reopen the workbook (not using the script), the worksheet cells containing a formula that invokes one or more functions defined within an add-in module all work correctly.

所以我想知道如何修改我的 Visual Basic 脚本，以便在它启动 Excel、打开工作簿并选择所需的工作表后，工作簿的工作表单元格可以成功调用加载项模块中定义的函数.

So I'm wondering how to modify my Visual Basic script so that after it launches Excel, and opens the workbook, and selects the desired worksheet, the workbook's worksheet cells can successfully invoke functions defined in add-in modules.

推荐答案

根据 Tim Williams 的评论，Microsoft 的这篇文章描述了这个问题并解释了如何解决它:

As per Tim Williams' comment, this article from Microsoft describes this issue and explains how to solve it:

在 Excel 中使用 CreateObject 命令时不会加载加载项

' File: test.vbs
' Opens Excel workbook "Workbook.xlsm" to worksheet "XYZ"
Set objXL = CreateObject("Excel.Application")
Set ObjWB = objXL.Workbooks.Open("C:\Path\To\Workbook.xlsm")

' Open add-in file 'AddIn.xlam'
objXL.Workbooks.Open ("C:\Path\To\AddIn.xlam")

objXL.Goto ObjWB.Sheets("XYZ").Range("A1")
objXL.Application.Visible = True

' Maximize the window after it opens
objXL.Application.WindowState = xlMaximized

Set objWB = Nothing
Set objXL = Nothing

这篇关于用于将 Excel 工作簿打开到启用加载项的特定工作表的 Visual Basic 脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！