DTS with vbscript & Excel Validation Object

• Matt Simmons

  SSCrazy

  Points: 2846

  More actions

  April 29, 2003 at 3:09 pm

  #151827

  I'm working on a DTS in which we automate the creation of an Excel Spreadsheet. The spreadsheet is used as a checksheet. I can place and format all my values, but can't create the validation.

  I've used Excel to build a macro and tried to modify that code.

  ---Excel Code---

  With Selection.validation

  .Delete

  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= xlBetween, Formula1:="=$A$1:$A$4"

  .IgnoreBlank = True

  .InCellDropdown = True

  .InputTitle = ""

  .ErrorTitle = ""

  .InputMessage = ""

  .ErrorMessage = ""

  .ShowInput = True

  .ShowError = True

  End With

  Selection.AutoFill Destination:=Range("D7:F9"), Type:=xlFillDefault

  Range("D7:F9").Select

  The Selection reference doesn't work so I add my object reference in front of each line.

  ---VBScript Code---

  wkbnew.worksheets(1).cells.item(rownum,7).Validation.Delete

  wkbnew.worksheets(1).cells.item(rownum,7).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= xlBetween, Formula1:="=$A$1:$A$4"

  wkbnew.worksheets(1).cells.item(rownum,7).Validation.IgnoreBlank = True

  wkbnew.worksheets(1).cells.item(rownum,7).Validation.InCellDropdown = True

  wkbnew.worksheets(1).cells.item(rownum,7).Validation.InputTitle = ""

  wkbnew.worksheets(1).cells.item(rownum,7).Validation.ErrorTitle = ""

  wkbnew.worksheets(1).cells.item(rownum,7).Validation.InputMessage = ""

  wkbnew.worksheets(1).cells.item(rownum,7).Validation.ErrorMessage = ""

  wkbnew.worksheets(1).cells.item(rownum,7).Validation.ShowInput = True

  wkbnew.worksheets(1).cells.item(rownum,7).Validation.ShowError = True

  Peace Out!

  Regards,
  Matt

• Matt Simmons

  SSCrazy

  Points: 2846

  More actions

  April 29, 2003 at 3:11 pm

  #455732

  and to finish the original post.

  I receive a syntax error on the 2nd line of the vbscript code.

  I've played with it for hours today and am looking for a bit of help on what the syntax error would be or how else I can format this part.

  Much Thanks,

  Matt

  Peace Out!

  Regards,
  Matt

• Matt Simmons

  SSCrazy

  Points: 2846

  More actions

  April 30, 2003 at 9:09 am

  #455813

  Much thanks to Chip Pearson in the microsoft.public.scripting.vbscript news group for posting the links to http://www.cpearson.com/zips/xlconsts.zip and

  http://www.cpearson.com/zips/xlobjects.zip

  The numeric translations for my variables was containted within.

  The correct line should read as follows:

  wkbnew.worksheets(1).range(rowcol).validation.add 3, 1, 1, "=$A$1:$A$4"

  Peace Out!

  Regards,
  Matt