如何引用我不知道其名称的打开的Excel工作表? [英] How to reference an open Excel sheet that I don't know the name of?

问题描述

背景:我正在Excel中记录一个宏，该宏在同时打开的三个不同工作簿之间传输数据(我正在记录它，然后进入代码并修复了所有错误，因为我对编码的经验为零).

Background: I'm recording a macro in Excel that transfers data between three different workbooks that are all open at the same time (I'm recording it and then going into the code and fixing any bugs because I have zero experience with coding).

问题:在第三次更改时，将始终使用其中两个工作簿(例如RFQ_1234，RFQ_1235).该宏非常有用，除了每次使用它时，我都必须对其进行调试并重新输入第三个工作簿的名称.如何更改代码，使其在不使用特定名称的情况下引用第三工作簿?

Problem: Two of the workbooks will always be used while the third changes (ex. RFQ_1234, RFQ_1235). The macro works great, except each time I use it, I have to debug it and re-enter the name of the third workbook. How do I change my code so that it references the 3rd workbook without using a specific name?

免责声明:我知道.select超级慢，我不在乎.它只需要工作.另外，我对编码知之甚少，所以请解释最细微的细节.

Disclaimer: I know .select is super slow, I don't care. It just needs to work. Also, I know very little about coding, so please explain even the smallest details.

代码示例:

Windows("RFQ_14446.xlsm").Activate
Range("J51").Select
Selection.Copy
Windows("Transfer Template.xlsm").Activate
Range("B1").Select
ActiveSheet.Paste
Windows("RFQ_14446.xlsm").Activate
Range("D27").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Transfer Template.xlsm").Activate
Range("B2").Select
ActiveSheet.Paste
Windows("RFQ_14446.xlsm").Activate
Range("D5").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Transfer Template.xlsm").Activate
Range("B3").Select
ActiveSheet.Paste

推荐答案

尽可能少地更改代码(根据要求！)...

Changing as little of your code as possible (as requested!)...

Sub Tester()

    Dim wbName As String

    wbName = GetRfqWbName("RFQ_")
    If Len(wbName) = 0 Then
        MsgBox "Didn't find the RFQ workbook!"
        Exit Sub
    End If

    Windows(wbName).Activate
    Range("J51").Select
    Selection.Copy
    Windows("Transfer Template.xlsm").Activate
    Range("B1").Select
    ActiveSheet.Paste
    Windows(wbName).Activate
    Range("D27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Transfer Template.xlsm").Activate
    Range("B2").Select
    ActiveSheet.Paste
    Windows(wbName).Activate
    Range("D5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Transfer Template.xlsm").Activate
    Range("B3").Select
    ActiveSheet.Paste
End Sub

'get the name of the first workbook which begins with sName...
Function GetRfqWbName(sName As String) As String
    Dim wb As Workbook
    For Each wb In Workbooks
        If wb.Name Like sName & "*" Then GetRfqWbName = wb.Name
        Exit For
    Next wb
End Function

这是上面的 Tester 子项的改进版本

here's an improved version of the Tester sub above

Sub Tester2()

    Dim wbName As String, shtSrc As Worksheet, shtDest As Worksheet

    wbName = GetRfqWbName("RFQ_")
    If Len(wbName) = 0 Then
        MsgBox "Didn't find the RFQ workbook!"
        Exit Sub
    Else
        'for example: you can substitute the sheet names instead
        Set shtSrc = Workbooks(wbName).Sheets(1)
        Set shtDest = Workbooks("Transfer Template.xlsm").Sheets(1)
    End If

    shtSrc.Range("J51").Copy shtDest.Range("B1")
    shtSrc.Range("D27").Copy shtDest.Range("B2")
    shtSrc.Range("D5").Copy shtDest.Range("B3")

End Sub

这篇关于如何引用我不知道其名称的打开的Excel工作表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！