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

#1

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

"Edward" wrote:

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: vb script in dts throws error when operating on Excel file - help please! - 10-05-2009 , 12:51 PM