带有 .xlsm 文件的 VBA 运行时错误 91，但未.xlsx

Question

我的程序可以按预期处理非宏 excel 文件，但无论出于何种原因，当我尝试将其与宏工作簿一起使用时，它都会给我运行时错误 91。当变量是宏书时，它看起来不像是将任何内容加载到 Fname 中。我怀疑这可能是因为公司设置是让用户在使用时启用宏。此代码段似乎无法加载宏书的文件路径。

Fname = Application.GetOpenFilename( _
    FileFilter:="XLS Files (*.xls),*.xls,XLSM Files (*.xlsm),*.xlsm", _
    Title:="Select a workbook", _
    MultiSelect:=False)
SheetName = Application.InputBox("Please enter the name of the output tab", , , , , , , 2)
pulled = sh1.Range("B1:B30")
WSoutputs = sh1.Range("B1:B30")
sh4.Range("J1:J28").ClearContents
Set sh2 = Workbooks.Open(Fname)

有没有办法从宏书中读取数据而无需启用它？如果没有，我将如何提示用户像正常一样打开文件并点击启用宏？

完整代码：

Option Explicit
Sub Pull()
Dim sh1 As Worksheet
Dim sh2 As Workbook
Dim sh3 As Worksheet
Dim sh4 As Worksheet
Dim Path As String
Dim i As Integer
Dim arg As String
Dim pulled
Dim WSoutputs
Dim Comp(30, 0) As Variant
Dim Fname As Variant
Dim SheetName
Set sh1 = ThisWorkbook.Sheets("Refs")
Set sh3 = ThisWorkbook.Sheets("Output")
Set sh4 = ThisWorkbook.Sheets("Inputs")
On Error GoTo Err:
Fname = Application.GetOpenFilename( _
    FileFilter:="XLS Files (*.xls),*.xls,XLSM Files (*.xlsm),*.xlsm,XLSX Files (*.xlsx),*.xlsx", _
    Title:="Select a workbook", _
    MultiSelect:=False)
SheetName = Application.InputBox("Please enter the name of the output tab", , , , , , , 2)
pulled = sh1.Range("B1:B30")
WSoutputs = sh1.Range("B1:B30")
sh4.Range("J1:J28").ClearContents
Set sh2 = Workbooks.Open(Fname)

For i = (LBound(pulled) + 2) To UBound(pulled)
    pulled(i, 1) = Trim(pulled(i, 1))
    pulled(i, 1) = sh2.Sheets(SheetName).Range(pulled(i, 1))
    WSoutputs(i, 1) = sh3.Range(WSoutputs(i, 1))
    If pulled(i, 1) = WSoutputs(i, 1) Then
        Comp(i - 3, 0) = "Match"
    Else
        Comp(i - 3, 0) = pulled(i, 1)
    End If
Next i
sh2.Close
sh4.Range("J1:J28") = Comp
Exit Sub
Err:
MsgBox ("Make sure the Workbook and tab names are correct. Make sure the output format matches this workbook")
sh4.Range("J1:J28").ClearContents
If Fname <> "False.xlsx" Or Fname <> "False.xlsm" Then
    sh2.Close
End If
End Sub

循环似乎没有做我想要的，因为其中的某个地方 Sh2 变得空了。此外，pull 和 wsoutput 包含单元格引用(例如 B3(，工作表之间的输入和输出格式相同。

Answer 1

我认为

是这样，但你应该发布尽可能多的代码。 我不知道以下内容是如何定义的。 无论如何，请尝试这个。

Set pulled = sh1.Range("B1:B30")
Set WSoutputs = sh1.Range("B1:B30")