On Oct 6, 6:07*am, Edward wrote:
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

There are two ways, at least. One is to loop through the range
addressing each cell with the Cells(row,col) property which is a bit
complicated. The other is to use the clipboard in a somewhat
different manner than your first attempt. That is ...

e_wksheet3.Range("A1:I3").Copy
ActiveSheet.Paste e_wksheet1.Range("A19")

This approach avoids the problem with getting the 'selection' setup
correctly and will work for single cells as well. Note, however, that
since this performs a copy, any formulas will be copied over (if there
are any in the range), not just the values.

To paste just the values (if formulas might be present), the following
can be used instead ...

Const xlPasteValues = -4163
e_wksheet3.Range("A1:I3").Copy
e_wksheet1.Range("A19").PasteSpecial xlPasteValues
_____________________
Tom Lavedas