vb script in dts throws error when operating on Excel file - help please!

[Edward]

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

[Tom Lavedas]

Post by Edward
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.
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")

If you still have problems, I might try the
microsoft.public.excel.programming group as well, as this seems to me
to be related to the Excel object model, more than the scripting or
database issues.
_____________________
Tom Lavedas

[Edward]

Post by Tom Lavedas

Post by Edward
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.
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

[Tom Lavedas]

Post by Edward

Post by Tom Lavedas

Post by Edward
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.
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
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

[Edward]

Post by Edward

Post by Tom Lavedas

Post by Edward
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.
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
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

[Tom Lavedas]

Post by Edward

Post by Tom Lavedas

Post by Edward
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.
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
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(False,
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.
_____________________
Tom Lavedas

[Will]

Post by Tom Lavedas

Post by Edward

Post by Tom Lavedas

Post by Edward
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.
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
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(False,
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

[Tom Lavedas]

{snip}

Post by Tom Lavedas

This will fox you, though.  Let's say that the range of values to be

{snip}

Post by Tom Lavedas

Again, many thanks
Edward

We're playing 'stump the chump', are we? ;-))
Try something like this ...

{snip}

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

You are welcome. I enjoy the challenge and learn as I 'earn', so we
all win.
_____________________
Tom Lavedas

[Dave Patrick]

Do you have Excel installed on the server?
--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

Post by Edward
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.
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