On Oct 5, 6:50 pm, Tom Lavedas wrote:
On Oct 5, 1:17 pm, Edward wrote:





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

Try directly addressing the ranges that you want to use instead of the
very problematic cell Select approach that the macro recorder tends to
provide, something like this ...

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

e_wksheet2.Range("A1") = e_wksheet1.Range("A7")

Hi Tom

This approach worked really well - thanks.

I also need to copy a range of cells, and this is the approach that
I'm trying:

e_wksheet3.Range("A1:I3") = e_wksheet1.Range("A19:I22")

Although the script runs without error, the target worksheet is
blank. However, if I substitute this line with

e_wksheet3.Range("A1") = e_wksheet1.Range("A19")

the cell at A1 is populated.

Any thoughts?

Thanks

Edward