SQL Server 2000
DTS
VBScript
Excel

I have to manipulate an Excel file within a DTS package.  Put simply,
I need to move the contents of certain cells in one Worksheet into
diffferent cells on another worksheet.

Here's my code:

Function Main

        Dim e_app
        Dim e_wbook
        Dim e_wksheet1
        Dim e_wksheet2
        Dim e_wksheet3
        Dim sFilename

        sFilename = "\\Server1\Shared\Import\ManagerQuestionnaire.xls"

        ' Create the Excel Object
        Set e_app = CreateObject("Excel.Application")

        ' Open up the Excel Spreadsheet
        Set e_wbook = e_app.Workbooks.Open(sFilename)

        ' Which sheet do we get our data from
        Set e_wksheet1 = e_wbook.Worksheets("Sheet1")
        Set e_wksheet2 = e_wbook.Worksheets("Sheet2")

        e_wksheet1.Range("A7").Select
        e_wksheet1.Copy

        e_wksheet2.Select <-- BARFS HERE WITH "Select method of worksheet
class failed"
        e_wksheet2.Range("A1").Select  <-- If I remove the above line it
barfs with "Select method of Range class failed"

I've tried setting e_wksheet2 to the e_wbook.Worksheets("Sheet1")
(same as e_wksheet1) but it still fails.  The code parses correctly.
It obviously gets beyond the Select method call on e_wksheet1 so why
is it failing on e_wksheet2?

Thanks

Edward