无法设置Excel VBA工作表变量 [英] Unable to set excel VBA worksheet variable

问题描述

我试图将源工作表变量设置为另一个工作簿(DataSource1.xlsx)中的目标工作表变量，目的是最终使用它来读取目标工作表并执行一些计算，但是它使我的下标超出了范围错误.

I am trying to set a source worksheet variable to a target worksheet variable in another workbook(DataSource1.xlsx) with the purpose of eventually using it to read the target worksheet and perform some calculation but it gives me the subscript out of range error.

这是我的代码

Sub GenerateReport()

Dim source As Worksheet

Dim path As String    
Set source= Workbooks(ThisWorkbook.path & "\DataSource1.xlsx").Sheets("Sheet1")

'path = ThisWorkbook.path

'Set wb = Workbooks.Open(ThisWorkbook.path & "\DataSource1.xlsx")

Dim one As Integer

one = 10

End Sub

文件全部放在同一个文件夹中，并且我检查了是否没有命名或路径错误，为什么会发生这种情况?

The file are all placed in the same folder and I have checked that there are no naming or path errors, why is this happening?

编辑

这是我的项目在经过GSerg评论后的样子

This is what my project looks like after GSerg comments

推荐答案

如果已经打开工作簿，则需要按名称而不是路径来引用它:

If the workbook is already opened, you need to refer to it by name without path:

Set source = Workbooks("DataSource1.xlsx").Sheets("Sheet1")

如果尚未打开工作簿，则需要先打开它:

If the workbook is not opened yet, you need to open it first:

Set source = Workbooks.Open(ThisWorkbook.path & "\DataSource1.xlsx").Sheets("Sheet1")

这篇关于无法设置Excel VBA工作表变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！