excel - 粘贴到 Excel ActiveX 组合框

Question

我正在尝试将数据从一个工作簿复制到另一个作为调查构建的工作簿中。

在调查表中，我们将 ActiveX 控件用于组合框和复选框。我留下了两种我尝试过（但失败了）的方法样本。

Sub TransferData()

Set Source = Workbooks.Open("FromHere.xlsm")
Set qstnr = Workbooks.Open("ToHere.xlsx")

' Banner Form Classification
    Source.Activate
    Cells(8, 2).Copy
    qstnr.Activate
    Set Cbo_Classification = qstnr.OLEObjects("Cbo_Classification")
    With Cbo_Classification.Object
     .Text = "Not sure what to do here"
    End With

' Reporting Organization
    Source.Activate
    Cells(9, 2).Copy
    qstnr.Activate
    'ActiveSheet.OLEObjects("Cbo_RptOrg").PasteSpecial Paste:=xlPasteValues

End Sub

编辑：当使用下面的副本在同一个工作簿中工作时，我已经能够获得要粘贴的对象。我不明白为什么在文档之外工作时它不成功。

Sub TransferObjects()

Dim wbk As Workbook: Set wbk = Workbooks.Open("CopyFrom.xlsm")
Dim tmplt As Workbook: Set tmplt = Workbooks.Open("CopyTo.xlsx")
Dim qstnr As Worksheet

Set qstnr = tmplt.Sheets("Sheet1")

qstnr.OLEObjects("Cbo_RptOrg").Object.Value = Range("K12").Value

End Sub

标签： excelvbacopy-pasteactivexobjectobject-properties

Answer 1

    ' Reporting Organization
    Source.Activate
    Dim Cbo_RptOrg As Variant
    Cbo_RptOrg = Cells(2, 9).Value
    qstnr.OLEObjects("Cbo_RptOrg").Object.Value = Cbo_RptOrg

这最终奏效了。按照建议使用变量。