Add dropdown list in excel export with macro

[qlikhalmar]

Hi all,

I want to export a table to excel and then add a drop down list in one of the columns.

The code in red is the part that is not working, the rest is OK

My code is:

'Settings
Set XLSapp                      = CreateObject("Excel.Application")                                                  'Set variable for excel application
XLSapp.Visible            = True                                                                                         'Exel visable during macro?
XLSapp.DisplayAlerts = False                                                                                        'Showing alerts during macro?
XLSDirectory              = ActiveDocument.Variables("vXlsDirectory").GetContent.String             'Excel directory
XLSDocumentName           = ActiveDocument.Variables("vXlsName").GetContent.String                  'Name new exceldocument
XLSTemplate                     = ActiveDocument.Variables("vXlsTemplate").GetContent.String              'Name of the template file

'Open Excel template
Set XLDoc                 = XLSapp.Workbooks.open(XLSDirectory & "\" & XLSTemplate)                 'Open Excel template file

'Start loop
Set val = ActiveDocument.Fields("QvdName").GetPossibleValues(20000)                                      'Count number of QVD's

For i = val.Count - 1 To 0 Step -1                                                                                  'Set loop

ActiveDocument.Fields("QvdName").Select val.Item(i).Text                                            'Set QvdName as variable
vSheetname = ActiveDocument.GetField("QvdName").GetPossibleValues.Item(0).Text                 'Select field QvdName

XLDoc.Sheets.Add.Name = vSheetname                                                                             'Add sheet with QvdName
ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard True                                     'Add table with fieldinfo
XLSapp.Worksheets(vSheetname).Range("A1").Select()
XLSapp.Worksheets(vSheetname).PasteSpecial
XLSapp.Worksheets(vSheetname).cells.WrapText = False

'Add dropdown box
'vNoOfRows = ActiveDocument.Variables("vNoOfRows").GetContent.String

With Range("C2:C & vNoOfRows & ").Validation
  XLSapp.Worksheets(vSheetname).Delete
  XLSapp.Worksheets(vSheetname).Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Instruction!$Q$30:$Q$31"
  XLSapp.Worksheets(vSheetname).IgnoreBlank = True
  XLSapp.Worksheets(vSheetname).InCellDropdown = True
  XLSapp.Worksheets(vSheetname).ShowInput = True
  XLSapp.Worksheets(vSheetname).ShowError = True
End With

ActiveDocument.Fields("QvdName").Clear                                                                         'Clear selections

Thanx for helping,

Halmar

[flipside]

Hi Halmar,

Something like this works for me on a single cell, haven't tested it on a column range though...

With XLApp.Cells(5,"C")

     .Select

     .Validation.add 3,2,,"=$A$3:$A$4" 'Type, AlertStyle, Operator, Formula1, Formula2

     .Validation.IgnoreBlank = True

     .Validation.InCellDropdown = True

     .Validation.InputTitle = "PLEASE NOTE"

     .Validation.ErrorTitle = "Error"

     .Validation.InputMessage = "use dropdown"

     .Validation.ErrorMessage = "Oops - please try again"

     .Validation.ShowInput = True

     .Validation.ShowError = True

end with

see also ...  http://msdn.microsoft.com/en-GB/library/microsoft.office.interop.excel.validation.add(v=office.11).a...

Haven't quite worked out what all the parameter values relate to but should be easy to find from the link above.

flipside

EDIT:  Yes, seems to work okay with a range ...

With XLApp.Range("C3:C65000")   instead of   With XLApp.Cells(5,"C")