vba 使用变量名称引用工作簿/工作表

Referring to Workbook/Worksheet with variable names

提问by Trey Hymanson

Sub CpyProdSch()
Dim wbkOpen As Workbook    
Dim wb2 As Workbook    
Dim wsName As String    
Dim strFileName As String    
Dim strFilePath As String    
Dim MsgBoxResult As Long    

strFilePath = "\Ykf001\grpdata\PUBLIC\Operations\Converting Schedule\"  

' this could also be a constant
strFileName = Dir(strFilePath)


Set wbkOpen = Workbooks.Open(strFilePath & strFileName, False, True)

'It stops here - or at least it appears to - defaults back to VBA view and does nothing further

    ActiveWorkbook.ActiveSheet.Range("A1:BN500").Select
        Selection.Copy

    Workbooks("Raw Data pRODUCTION.xlsx").Worksheets("production    Data").Range("A1:C1").Select.PasteSpecial Paste:=xlPasteValues, _
         Operation:=xlNone, SkipBlanks:=True, Transpose:=False
    Application.CutCopyMode = False

End If
wbkOpen.Close False

Application.ScreenUpdating = True

As you can see I have a variable workbook name, as the only way I can refer to it is knowing its the only excel file in the folder I'm accessing - so this isn't as simple as activating a known workbook name.

正如您所看到的，我有一个可变的工作簿名称，因为我可以引用它的唯一方法是知道它是我正在访问的文件夹中唯一的 excel 文件 - 所以这不像激活已知的工作簿名称那么简单。

As it stands now this portion of the code OPENS the workbook fine, it just won't do anything in the workbook because I think I'm referring to it incorrectly.

就目前而言，这部分代码可以很好地打开工作簿，它不会在工作簿中执行任何操作，因为我认为我指的是错误的。

Additionally, the sheet name with the data changes: Currently it says August 29, before it says July 14, its variable but it is always the first sheet. For some reason when the workbook opens though it opens to the second sheet.

此外，带有数据的工作表名称发生了变化：当前显示为 8 月 29 日，在显示 7 月 14 日之前，它是可变的，但它始终是第一张工作表。出于某种原因，当工作簿打开时，虽然它打开到第二张纸。

I need to activate the first sheet in the workbook I just opened so that I can copy and paste a range into my "Raw Production Data" workbook.

我需要激活我刚刚打开的工作簿中的第一张工作表，以便我可以将一个范围复制并粘贴到我的“原始生产数据”工作簿中。

---

---

Here is the code now [code] Set wbkOpen = Workbooks.Open(strFilePath & strFileName, False, True) Sheets(1).Activate Set wbkOpen = ActiveWorkbook

这是现在的代码 [code] Set wbkOpen = Workbooks.Open(strFilePath & strFileName, False, True) Sheets(1).Activate Set wbkOpen = ActiveWorkbook

    wbkOpen.Sheets(1).Range("A2").Copy

     Workbooks("Raw Data pRODUCTION.xlsx").Worksheets("Production Data").Range("A1").Select.PasteSpecial Paste:=xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=True, Transpose:=False
            Application.CutCopyMode = False
   [/code]

It still just opens the workbook and stops. I trimmed it down to just select sheets(1), and nothing happens after it opens the book. And the code WAS working this morning. I am baffled.

它仍然只是打开工作簿并停止。我将其修剪为仅选择工作表（1），打开书后没有任何反应。今天早上代码正在运行。我很困惑。

---

---

I did some tinkering so this is odd.

我做了一些修补，所以这很奇怪。

No matter what the file path is, where the sub is, or what the excel workbook is every single time the workbook will open then the macro will stop.

无论文件路径是什么，子文件在哪里，或者工作簿每次打开时 Excel 工作簿是什么，宏都会停止。

It's completely irrelevant of the action, because once that file opens the macro just stops.

它与操作完全无关，因为一旦该文件打开，宏就会停止。

Any ideas?

有任何想法吗？

回答by n8.

It's actually fairly shocking that this works at all, just goes to show that there's always something new to learn. Instead of doing this:

这实际上是相当令人震惊的，它完全有效，只是表明总有一些新的东西需要学习。而不是这样做：

Set wbkOpen = Workbooks.Open(strFilePath & strFileName, False, True)

You could do this:

你可以这样做：

Workbooks.Open strFilePath & strFileName, False, True
Sheets(1).Activate
Set wbkOpen = ActiveWorkbook

Works for me.

对我来说有效。

回答by Siphor

ActiveWorkbook.ActiveSheet.Range("A1:BN500").Select
Selection.Copy

Should be

应该

wbkOpen.Sheets(1).Range("A1:BN500").Copy

Workbooks.Open returns the workbook it opens so you can use that.(Note that wbkOpen. isnt even needed because a newly opened workbook is always the active workbook) You can get the first sheet of a workbook with Sheets(1). The .select and then selection.copy can be simplified to just .copy

Workbooks.Open 返回它打开的工作簿，以便您可以使用它。（注意 wbkOpen。​​甚至不需要，因为新打开的工作簿始终是活动工作簿）您可以使用 Sheets(1) 获取工作簿的第一张工作表。.select 然后 selection.copy 可以简化为 .copy

In

在

Workbooks("Raw Data pRODUCTION.xlsx").Worksheets("production    Data").Range("A1:C1").Select.PasteSpecial Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=True, Transpose:=False
                Application.CutCopyMode = False

is a select that does nothing useful, so it should be

是一个没有任何用处的选择，所以它应该是

Workbooks("Raw Data pRODUCTION.xlsx").Worksheets("production    Data").Range("A1:C1").PasteSpecial Paste:=xlPasteValues, _
                    Operation:=xlNone, SkipBlanks:=True, Transpose:=False
                    Application.CutCopyMode = False