I'm trying to copy data from one workbook into another workbook that is build as a survey.
In the survey form, we are using ActiveX controls for combo boxes and check boxes. I left two samples of ways I have tried (and failed).
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
EDIT: I have been able to get the object to be pasted into when working in the same workbook with the copy below. I don't understand why it's not successful when working outside the document.
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
' Reporting Organization
Source.Activate
Dim Cbo_RptOrg As Variant
Cbo_RptOrg = Cells(2, 9).Value
qstnr.OLEObjects("Cbo_RptOrg").Object.Value = Cbo_RptOrg
This ended up working. Using a variable as suggested.