how to add a drop down list in excel using vbs

Question

Hi I am trying to add a drop down in a excel using vbs and i am getting the below error.

vbs(18, 15) Microsoft VBScript compilation error: Syntax error

I even recorded a marco and used the code from there still it didn't work.

Line 18 and col 15 is the line with the add keyword.

Range("A1").Select
    With Selection.Validation
        .Delete
        .Add (Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$Q$9:$Q$11")
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Q9:Q11 has a sample data set What am i doing wrong?

Answer 1

You need to replace the vba intrinsic constants of xlValidateList with their direct vbs numeric equivalent (which you can get via VBA help). This works:

Dim objExcel, objWB, objws
Set objExcel = CreateObject("excel.application")
Set objWB = objExcel.Workbooks.Add
Set objws = objWB.Sheets(1)
With objws.Range("A1").Validation
        .Add 3, 1, 1, "=$Q$9:$Q$11"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
 end With