On Oct 6, 6:29*pm, Tom Lavedas wrote:
On Oct 6, 10:24*am, Edward wrote:





On Oct 6, 1:52*pm, Tom Lavedas wrote:

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
_____________________

Wow Tom, that's awesome service! *I'm hugely in your debt.

This will fox you, though. *Let's say that the range of values to be
copied (above shown as e_wksheet3.Range("A1:I3")) was unknown - or, to
be completely specific, it was known that the columns were A through
I, but the number of rows was unknown. *I could cludge it by writing

* * *Const xlPasteValues = -4163
* * *e_wksheet3.Range("A1:I5000").Copy
* * *e_wksheet1.Range("A19").PasteSpecial xlPasteValues

but there would be sure to be circumstances where this was not a good
idea. *Any idea how to find the index of the first empty row and use
it in the script above? *I'm aware of a constant called xlDown, but
not sure how to use it, or even if it's the right one....

Again, many thanks

Edward

We're playing 'stump the chump', are we? ;-))

Try something like this ...

*Const xlPasteValues = -4163, xlDown = -4121, xlToRight = -4161
*sLRCorner = Range("A1").End(xlDown).End(xlToRight).Address(Fal se,
False)
*e_wksheet3.Range("A1:" & sLRCorner).Copy
*e_wksheet1.Range("A19").PasteSpecial xlPasteValues

This assumes there are no blank (empty) cells along the path traced
(down and then right). *A reversal of the two constants can also be
used (right and then down), if that will work better.

If the desired range is the only block of data on the sheet then the
location of the last cell can be found with this ...

*Const xlLastCell = 11
*sLRCorner = Range("A1").SpecialCells(xlLastCell).Address(False ,
False)

BTW, I figured all of this out by running the Macro Recorder in Excel
and then reviewing the VBA code that was created - and a few
references to the help documentation in the VB editor.

Ok, I failed to "stump the chump". That should, of course, be "stump
the champ" by the way.

I, and my clients, are in your debt. Dunno how I'm going to pay it,
but there you are.

Thanks again.

Edward