Hi,,


I am using activex task in my dts, creating excel file with multiple
woksheets.

I want to create more than 3 worksheets in a workbook.

it is limiting to 3 worksheets.

here is my code



Dim oExcel
Dim oBook
Dim oSheet
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add

'Add data to cells of the first worksheet in the new workbook
Set oSheet = oBook.Worksheets(1)
oSheet.name = "AC19_COUNT"
Set oSheet = oBook.Worksheets(2)
oSheet.name = "POAMOUNT_NOMATCH"

Set oSheet = oBook.Worksheets(3)
oSheet.name = "RT1001_PO_NOTIN_FAMMS"
Set oSheet = oBook.Worksheets(4)
oSheet.name = "DuplicateCardNumber"


'Save the Workbook and Quit Excel
oBook.SaveAs"c:\temp\" oExcel.Quit

Any suggestions please

--
Message posted via http://www.sqlmonster.com

Hello sjambulapati via SQLMonster.com,

What do you intend to do with those Worksheets?

In DTS you can issue a CREATE TABLE statement against the Spreadsheet connection
to Create a Worksheet/Named Range. If this is not what you intend to do
then maybe an XL newsgroupd will be able to help you more than this one.


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Hi

thank you

I using recordset objects . Each results are exported to a excel file in
different worksheets.

here is my Code



Dim oExcel
Dim oBook
Dim oSheet
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'Add data to cells of the first worksheet in the new workbook
Set oSheet = oBook.Worksheets(1)
oSheet.name = "AC19_COUNT"
Dim fldCount
Dim recCount1
Dim iCol
Dim iRow
fldCount =EXCEL1.Fields.Count
For iCol = 1 To fldCount
osheet.Cells(1, iCol).Value = EXCEL1.Fields(iCol - 1).Name
Next
osheet.Cells(2, 1).CopyFromRecordset EXCEL1
Set oSheet = oBook.Worksheets(2)
oSheet.name = "POAMOUNT_NOMATCH"
fldCount =EXCEL2.Fields.Count
For iCol = 1 To fldCount
osheet.Cells(1, iCol).Value = EXCEL2.Fields(iCol - 1).Name
Next


osheet.Cells(2, 1).CopyFromRecordset EXCEL2
Set oSheet = oBook.Worksheets(3)
oSheet.name = "RT1001_PO_NOTIN_FAMMS"
fldCount =EXCEL5.Fields.Count

For iCol = 1 To fldCount
osheet.Cells(1, iCol).Value = EXCEL5.Fields(iCol - 1).Name
Next


osheet.Cells(2, 1).CopyFromRecordset EXCEL5


'Set oSheet = oBook.Worksheets(4)
'oSheet.name = "DUPLICATE CARD NUMBER"

' fldCount =EXCEL3.Fields.Count
' For iCol = 1 To fldCount
' osheet.Cells(1, iCol).Value = EXCEL3.Fields(iCol - 1).Name
' Next


' osheet.Cells(2, 1).CopyFromRecordset EXCEL3


I am getting Subscript Outof range Error


thanks

Allan Mitchell wrote:
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...r-dts/200604/1

Hello sjambulapati via SQLMonster.com,

If you are using Recordsets then can you not use the method I described
whereby you create the Excel Tbale/Named range in an ExecuteSQL task forst
anf then pump data to it???



Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Any example code please
It is working good up to three sheets ,after that i am getting subscript out
of range error

Thanks


Allan Mitchell wrote:
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...r-dts/200604/1

Hello sjambulapati via SQLMonster.com,


In an ExecuteSQL task use the XL Connection Manager and issue different CREATE
TABLE statements. You should be able to make more than 3 worksheets.



Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com