Add dropdown list in excel export with macro

qlikhalmar — Wed, 01 May 2013 14:09:49 GMT

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

Re: Add dropdown list in excel export with macro

flipside — Wed, 01 May 2013 15:03:32 GMT

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

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")

topic Add dropdown list in excel export with macro in QlikView

Add dropdown list in excel export with macro

Hi all,

Re: Add dropdown list in excel export with macro