Post reply

Copying Excel worksheet in SSIS script task

  • June 23, 2011 at 6:11 am

    #253250

    Hello

    I am trying to write a vbscript SSIS script task to copy worksheets from one workbook to another

    I have taken this syntax from various places on the web, including here on SSC, but it won't compile happily:

    _sheets(_sheet.Name).copy, _sheets(1)

    The error it throws is 'Method arguments must be enclosed in parantheses

    This code works fine, but defaults to copying the sheet to a new workbook

    _sheets(_sheet.Name).copy()

    How can I change the code so that it accepts an argument for the location that I want to copy the worksheet to?

    I realise this is more of a vbscript question that a sql one but as I don't (yet) belong to a vb forum I thought I'd throw it out to all you SSIS gurus first

    Thanks in advance for all your help

  • June 23, 2011 at 8:02 am

    #1343051

    Try this: Sheets("Sheet1").Copy Before:=Workbooks("Book2").Sheets(1)

    It's really a VBScript question. You can google for the answer (too many there) or try record a macro in Excel and then see the code (that's what I did).

  • June 23, 2011 at 9:14 am

    #1343132

    Thanks John

    I found loads of VBA info on google, but not much about vbscript

    In the end, the answer turned about to be frustratingly simple:

    Change this

    _sheets(_sheet.Name).copy, _sheets(1)

    To this

    _sheets(_sheet.Name).copy(, _sheets(1))

    :crazy:

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

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