1 Reply Latest reply: May 1, 2013 11:10 AM by Dave Riley RSS

    Add dropdown list in excel export with macro

    Halmar Heijnen
      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
          Dave Riley

          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).aspx

           

           

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