Post reply

Problem with Visual Basic ActiveX Script

  • September 26, 2005 at 5:33 am

    #76428

    We are having a problem using a VB activeX script within our DTS package. We've set up a new server running Windows 2003 and SQL2K sp3a, and are transferring our production processes to it. I've managed to get all but one process working and this one uses an ActiveX script to manipulate the spreadsheet it created. The job works on production without any problems and other jobs using Excel (not in this manner) work fine.

    The message we're receiving is "Invalid procedure call or argument: 'Left'" - interestingly enough, it will work if we run it manually, but not as a scheduled job. I've changed to Microsoft Excel application launch permissions, checked that we're running the same version of Excel on both machines and can't find any reference to this problem in any of the usual sources.

    The troublesome code is

    Function GetWeekDates (oSheet)

     Dim sMonth, sDay, sYear, SDate, EDate

     TempDate = oSheet.Range("G6").Value 

     L=Len(TempDate)

     SDate=Left(TempDate, L-15) 

     sDay=DatePart("D", SDate)

     sMonth= GetMonth(DatePart("M", SDate) )

     sYear=DatePart("YYYY", SDate)

     SDate= sMonth & sDay & ", " & sYear

     

     TempDate = oSheet.Range("H6").Value 

     L=Len(TempDate)

     EDate=Left(TempDate, L-15) 

     sDay=DatePart("D", EDate)

     sMonth= GetMonth(DatePart("M", EDate) )

     sYear=DatePart("YYYY", EDate)

     EDate= sMonth & sDay & ", " & sYear

     GetWeekDates = "For " & SDate & " To " & EDate

    End Function

    Has anyone else had a similar problem - or any ideas? Thanks!

  • September 27, 2005 at 8:01 am

    #593280

    Is your variable "L" being returned with a value less than 15?  say 10... L-15 would = -5... you can't do that on a Left function.

    Also... it would not hurt to DIM your L variable... then add an "IF L > 15 THEN" above your Left command.

    -Mike Gercevich

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply