Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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")