vba - 使用用户表单从当前打开的工作簿中检索外部工作簿的信息

Question

我为用户窗体编写代码，允许我使用工作簿 Excel 表中的项目 ID 检索信息。

但是，现在我需要根据用户输入搜索 在外部工作簿中找到的 projectID 调用active master file .

然后它将检索信息并将其放入用户窗体并使用添加命令按钮将信息插入到当前工作簿中称为项目跟踪文件的新行中。

问题是我不确定将信息从外部工作簿检索到当前打开的工作簿中存在的用户窗体的代码。

我有两本工作簿。此用户表单位于名为 project tracker 的工作簿中。而我想根据他们的项目 ID 检索信息的外部工作簿称为 active master project .

这是命令搜索按钮的代码，用于在名为 project tracker 的工作簿的工作表中进行检索和搜索。 :

Private Sub CommandSearchButton2_Click()

    Dim lastrow
    Dim ProjCode As String
    Dim LabelProjName As String
    Dim LabelObjective As String
    Dim LabelProjSponsor As String
    Dim LabelProjSponsorNew As String
    Dim LabelProjManager As String
    Dim LabelRegulatory As String
    Dim LabelRiskLvl As String
    Dim LabelDatePar As Date
    Dim LabelCostPar As Long
    Dim LabelAffectCust As String
    Dim LabelCustNonRetail As String
    Dim LabelCustRetail As String
    Dim LabOutsourcingImp As String
    Dim LabelKeyUpdate As String
    Dim LabelSector As String



    searchRow = 0
        lastrow = Sheets("Program Status Summary").Range("B" & Rows.Count).End(xlUp).Row
        ProjCode = TextBoxProjCode.Text

     For currentrow = 4 To 100

        If Cells(currentrow, 2).Text = ProjCode Then

            searchRow = currentrow

            TextBoxProjCode.Text = Cells(currentrow, 2).Text
            TextBoxProjName.Text = Cells(currentrow, 3)
            TextBoxSector.Text = Cells(currentrow, 4)
            TextBoxObjective.Text = Cells(currentrow, 5)
            TextBoxProjSponsor.Text = Cells(currentrow, 7)
            TextBoxProjSponsorNew.Text = Cells(currentrow, 8)
            TextBoxProjM.Text = Cells(currentrow, 6)
            TextBoxRegulatory.Text = Cells(currentrow, 20)
            TextBoxRiskLvl.Text = Cells(currentrow, 13)
            TextBoxDatePar.Text = Cells(currentrow, 12)
            TextBoxCostPar.Text = Cells(currentrow, 10)
            TextBoxAffectCust.Text = Cells(currentrow, 15)
            TextBoxCustNonRetail.Text = Cells(currentrow, 16)
            TextBoxCustRetail.Text = Cells(currentrow, 17)
            TextBoxOutsourcingImp.Text = Cells(currentrow, 19)
            TextBoxKeyUpdate.Text = Cells(currentrow, 18)

        End If

    Next currentrow

TextBoxProjCode.SetFocus

End Sub

Answer 1

使用多个工作簿的最佳方法是使用对象变量 :

Dim WbPT As Workbook, _
    WbAMP As Workbook, _
    WsPSS As Worksheet, _
    Ws As Worksheet

'----The SET keyword is only to attribute value to an object variable
Set WbPT = Workbooks("project tracker")
Set WbAMP = Workbooks("active master project")

Set WsPSS = WbPT.Sheets("Program Status Summary")

'----Use with to have the reference availaible starting with a simple dot "."
With WsPSS
    MsgBox .Cells(1, "B")
End With

所以如果我理解得很好，你的代码应该是这样的:

Private Sub CommandSearchButton2_Click()

    Dim lastrow
    Dim ProjCode As String
    Dim LabelProjName As String
    Dim LabelObjective As String
    Dim LabelProjSponsor As String
    Dim LabelProjSponsorNew As String
    Dim LabelProjManager As String
    Dim LabelRegulatory As String
    Dim LabelRiskLvl As String
    Dim LabelDatePar As Date
    Dim LabelCostPar As Long
    Dim LabelAffectCust As String
    Dim LabelCustNonRetail As String
    Dim LabelCustRetail As String
    Dim LabOutsourcingImp As String
    Dim LabelKeyUpdate As String
    Dim LabelSector As String

    Dim WbPT As Workbook, _
        WbAMP As Workbook, _
        WsPSS As Worksheet, _
        Ws As Worksheet

    Set WbPT = Workbooks("project tracker")
    Set WbAMP = Workbooks("active master project")

    Set WsPSS = WbPT.Sheets("Program Status Summary")

    With WsPSS
        searchRow = 0
        lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
        ProjCode = TextBoxProjCode.Text

         For currentrow = 4 To lastrow
            If .Cells(currentrow, 2).Text = ProjCode Then
                searchRow = currentrow

                TextBoxProjCode.Text = .Cells(currentrow, 2).Text
                TextBoxProjName.Text = .Cells(currentrow, 3)
                TextBoxSector.Text = .Cells(currentrow, 4)
                TextBoxObjective.Text = .Cells(currentrow, 5)
                TextBoxProjSponsor.Text = .Cells(currentrow, 7)
                TextBoxProjSponsorNew.Text = .Cells(currentrow, 8)
                TextBoxProjM.Text = .Cells(currentrow, 6)
                TextBoxRegulatory.Text = .Cells(currentrow, 20)
                TextBoxRiskLvl.Text = .Cells(currentrow, 13)
                TextBoxDatePar.Text = .Cells(currentrow, 12)
                TextBoxCostPar.Text = .Cells(currentrow, 10)
                TextBoxAffectCust.Text = .Cells(currentrow, 15)
                TextBoxCustNonRetail.Text = .Cells(currentrow, 16)
                TextBoxCustRetail.Text = .Cells(currentrow, 17)
                TextBoxOutsourcingImp.Text = .Cells(currentrow, 19)
                TextBoxKeyUpdate.Text = .Cells(currentrow, 18)
            End If
        Next currentrow
    End With

    TextBoxProjCode.SetFocus

End Sub