Automating Excel ActiveX/ Form controls
I have scenario where i need to select a value from combo box and click on a command button(ActiveX controls) in Excel. When i click the button, a modal form will be opened and on performing any action (Eg: Entering the details and submit, Cancel, Close) on the form, the form will be closed.
Please help me in automating the above scenario.
Note: I tried with the below code. When i execute the below code, it is selecting the value and clicking on the command button. But the control is not coming out of the last statement until the form is closed/ submitted.
' Create Excel Object
Set objExcel = CreateObject("Excel.Application")
Set objExcelWorkBook = objExcel.Workbooks.Open(FilePath\FileName.xlsb)
Set objExcelSheet = objExcelWorkBook.Worksheets("Input")
objExcel.Visible = True
' Set the value in combo box
.Value = "xxxxxx"
' Click on Select command button
Set objOLE = objExcelSheet.OLEObjects
objOLE("cmdInputSelect").Object.Value = True