DTS Exporting to Excel - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS Exporting to Excel

Author

Topic

gbatta
Starting Member

26 Posts

Posted - 2006-08-25 : 15:14:59

I am using a DTS package to pull data into excel. Everything is working fine, except when I open the spreadsheet, my data doesn't start in cell A2 where I programmed it to; it starts in A1033. Below is the code I am using, any help is greatly appreciated.

-----
'********************************************************************
' Visual Basic ActiveX Script
'********************************************************************

Function Main()
Dim oExl, oWB, oWS, dst

dst = the path of the spreadsheet(not posting path on thread)

Set oExl = CreateObject("Excel.Application")
Set oWB = oExl.Workbooks.Open(dst)
Set oWS = oWB.Worksheets("Monthly Report")

With oWS.Range("A1", "M1")
.Interior.ColorIndex = 19
.Font.ColorIndex = 1
.Font.Bold = True
.HorizontalAlignment = 3
With .EntireColumn
.Font.Size = "10"
.AutoFit
With .Borders
.ColorIndex = 16
End With
End With
End With

With oWS.Range("A2", "A50")
.Interior.ColorIndex = 2
.Font.ColorIndex = 1
.Font.Bold = False
.HorizontalAlignment = 2
With .EntireColumn
.Font.Size = "8"
.AutoFit
With .Borders
.ColorIndex = 16
End With
End With
End With

With oWS.Range("B2", "B50")
.Interior.ColorIndex = 2
.Font.ColorIndex = 1
.Font.Bold = False
.HorizontalAlignment = 1
With .EntireColumn
.Font.Size = "8"
.AutoFit
With .Borders
.ColorIndex = 16
End With
End With
End With

With oWS.Range("C2", "C50")
.Interior.ColorIndex = 2
.Font.ColorIndex = 1
.Font.Bold = False
.HorizontalAlignment = 1
With .EntireColumn
.Font.Size = "8"
.AutoFit
With .Borders
.ColorIndex = 16
End With
End With
End With

With oWS.Range("D2", "D50")
.Interior.ColorIndex = 2
.Font.ColorIndex = 1
.Font.Bold = False
.HorizontalAlignment = 1
With .EntireColumn
.Font.Size = "8"
.AutoFit
With .Borders
.ColorIndex = 16
End With
End With
End With

With oWS.Range("E2", "E50")
.Interior.ColorIndex = 2
.Font.ColorIndex = 1
.Font.Bold = False
.HorizontalAlignment = 1
With .EntireColumn
.Font.Size = "8"
.AutoFit
With .Borders
.ColorIndex = 16
End With
End With
End With

With oWS.Range("F2", "F50")
.Interior.ColorIndex = 2
.Font.ColorIndex = 1
.Font.Bold = False
.HorizontalAlignment = 1
With .EntireColumn
.Font.Size = "8"
.AutoFit
With .Borders
.ColorIndex = 16
End With
End With
End With

With oWS.Range("G2", "G50")
.Interior.ColorIndex = 2
.Font.ColorIndex = 1
.Font.Bold = False
.HorizontalAlignment = 1
With .EntireColumn
.Font.Size = "8"
.AutoFit
With .Borders
.ColorIndex = 16
End With
End With
End With

With oWS.Range("H2", "H50")
.Interior.ColorIndex = 2
.Font.ColorIndex = 1
.Font.Bold = False
.HorizontalAlignment = 1
With .EntireColumn
.Font.Size = "8"
.AutoFit
With .Borders
.ColorIndex = 16
End With
End With
End With

With oWS.Range("I2", "I50")
.Interior.ColorIndex = 2
.Font.ColorIndex = 1
.Font.Bold = False
.HorizontalAlignment = 1
With .EntireColumn
.Font.Size = "8"
.AutoFit
With .Borders
.ColorIndex = 16
End With
End With
End With

With oWS.Range("J2", "J50")
.Interior.ColorIndex = 2
.Font.ColorIndex = 1
.Font.Bold = False
.HorizontalAlignment = 1
With .EntireColumn
.Font.Size = "8"
.AutoFit
With .Borders
.ColorIndex = 16
End With
End With
End With

With oWS.Range("K2", "K50")
.Interior.ColorIndex = 2
.Font.ColorIndex = 1
.Font.Bold = False
.HorizontalAlignment = 1
With .EntireColumn
.Font.Size = "8"
.AutoFit
With .Borders
.ColorIndex = 16
End With
End With
End With

With oWS.Range("L2", "L50")
.Interior.ColorIndex = 2
.Font.ColorIndex = 1
.Font.Bold = False
.HorizontalAlignment = 1
With .EntireColumn
.Font.Size = "8"
.AutoFit
With .Borders
.ColorIndex = 16
End With
End With
End With

With oWS.Range("M2", "M50")
.Interior.ColorIndex = 2
.Font.ColorIndex = 1
.Font.Bold = False
.HorizontalAlignment = 1
With .EntireColumn
.Font.Size = "8"
.AutoFit
With .Borders
.ColorIndex = 16
End With
End With
End With

oWB.Close(True)
set oWS = Nothing
set oWB = Nothing

oExl.Quit
set oExl = Nothing

Main = DTSTaskExecResult_Success
End Function
-----

Thank you,
Ginger Batta
Information Technology Specialist
Center for Global Trade Analysis

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-08-28 : 16:30:30

Ginger,

quote:

I am using a DTS package to pull data into excel. Everything is working fine, except when I open the spreadsheet, my data doesn't start in cell A2 where I programmed it to; it starts in A1033. Below is the code I am using, any help is greatly appreciated.

The problem is that you didn't post any code that shows how you're putting data into the spreadsheet - all you showed is formatting code for different ranges in the "Monthly Report" workbook.

As the problem your question is about is where your data is being placed, you need to post the code that puts the data into the spreadsheet.

Ken

gbatta
Starting Member

26 Posts

Posted - 2006-08-28 : 16:40:52

I am using the following query in a transform data task to pull my data. Everything works fine becuase it does place all of my data in the spreadsheet; it just starts in cell A1033 instead of A2.

declare @FirstDayOfThisMonth DateTime, @FirstDayOfLastMonth DateTime

set @FirstDayOfThisMonth = convert(datetime, convert(char(7), getdate(), 120) + '-01')
set @FirstDayOfLastMonth = dateadd(mm, -1, convert(datetime, convert(char(7), getdate(), 120) + '-01'))

SELECT CAST(tbOrders.OrderID as int) as OrderID, tbOrders.Lastname, tbOrders.Firstname, tbOrders.Organization, tbOrders.Country, tbOrders.Region,
(convert(char(12), tbOrders.DateCreated, 101)) as OrderDate, (convert(char(12), tbOrders.ShipDate, 101)) as ShipDate,
SUBSTRING(tbProducts.Product, 1, (CHARINDEX(' (', tbProducts.Product))) as Product,
REPLACE(SUBSTRING(tbProducts.Product, (CHARINDEX(')', tbProducts.Product)), 50), ')', '') as Classification, tbOrders.PaymentMethod, tbOrders.PaymentDate,
SUBSTRING(tbProducts.Product, (CHARINDEX('$', tbProducts.Product)), ((CHARINDEX(')', tbProducts.Product)) - (CHARINDEX('$', tbProducts.Product)))) as ProductAmount
FROM tbOrders
INNER JOIN tbProducts ON tbOrders.OrderID = tbProducts.OrderID
WHERE tbOrders.License LIKE '6.0-%' AND tbOrders.Status = '1' AND
(tbOrders.ShipDate >= @FirstDayOfLastMonth AND tbOrders.ShipDate <= @FirstDayOfThisMonth)

My destination is the 'Monthly Report$' tab and each source has its own transformation that aligns with the proper destination.

I just cannot figure out where the error is occuring to make the data not start in cell A2. My original thought was that there was null data above my actual data, but I do not think that is the case.

Any help is appreciated.

Thank you!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-29 : 01:45:02

Where is the code that actually puts the data in your Excel sheet?

Peter Larsson
Helsingborg, Sweden

gbatta
Starting Member

26 Posts

Posted - 2006-08-29 : 08:39:09

I have a connection to SQL Server that uses a transform data task to pump the data to a Microsoft Excel 97-2000 connection. On the success of that step, it formats the cells with the above code.

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-08-29 : 16:13:35

qbatta,

Once again (now the third time, including Peter's request), post the ACTUAL CODE that puts the data into the Excel spreadsheet. Telling us how you're doing it doesn't help; if it did, you'd have an answer by now.

Ken

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources