asked on

Export for MS SQL to Excel 2000

Using Data Transformation Services, I'm trying to setup an export from our SQL Database into an Excel spreadsheet. I'm able to get that far, but problem is that I need to have certain columns in Excel be a specific cell format. And the datatype I select in the transformation area of DTS seems to effect that data coming for SQL but not the actual cell format in Excel.

nickah

Hello M0Dnar,
Do you have access to Visual Studio or Visual Basic?
Try the following program with your own formatting. You can compile it to an executable and add a command to your DTS which applies your formatting. There's probably a way to do this with ActiveX or VB Script.

Option Explicit
Private ExcelApp As New Excel.Application
Private workBook As Excel.workBook
Private ws As Excel.Worksheet

Private Sub main()

Set ws = InitializeReport
FormatWS
workBook.Save
workBook.Close True
ExcelApp.Quit

End Sub

Public Function InitializeReport() As Excel.Worksheet
Set workBook = ExcelApp.Workbooks.Open("c:\delete1Row.xls")
ExcelApp.Visible = False
Set ws = workBook.ActiveSheet
ws.Activate
Set InitializeReport = ws
End Function

Public Sub FormatWS()

'explore range.select & activate (single cell)
ws.Cells(3, 1) = "=a1"

ws.Cells(6, 1).Font.Bold = True 'the cell on the first row and 6th column
ws.Range("a1", "b1").Font.Bold = True

ws.Rows(3).Font.Bold = True 'make the second row bold
ws.Rows(4).RowHeight = 10 'column width

End Sub

The best of luck to you.

Nick

M0DNAR

ASKER

Thanks for your thoughts Nick. I'm afraid I don't have access to Visual Studios. But The DTS editor in MS SQL does allow for ActiveX code to be added to the job.

However, I think I may be either misunderstanding your code or miscommunicating what I'm trying to do. I'm not actually trying to change the text properties (like, size, font, alignment, etc.), but the type of data that's in the cells.

For example, if you right click a cell or set of cells in Excel you can click on Cell Format. From there you can choose that type of data that's in the cell. I'm trying to create cells that are Text rather than Excel's General format.

CleanupPing

M0DNAR:
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.

ASKER CERTIFIED SOLUTION

M0DNAR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial