?
Solved

Export for MS SQL to Excel 2000

Posted on 2003-02-27
5
Medium Priority
?
443 Views
Last Modified: 2007-12-19
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.
0
Comment
Question by:M0DNAR
  • 2
4 Comments
 

Expert Comment

by:nickah
ID: 8038790
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
0
 

Author Comment

by:M0DNAR
ID: 8039225
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.
0
 

Expert Comment

by:CleanupPing
ID: 9276257
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.
0
 

Accepted Solution

by:
M0DNAR earned 0 total points
ID: 9278318
My solution:

Create a new DTS package with the following objects:


ActiveX Task 1 ~~ (on success) ~~> MS ODBC Driver ~~ (data transform) ~~> Excel 97-2000 ~~ (on success) ~~> ActiveX Task 2


Details:


ActiveX Task 1:

'**********************************************************************
'
'  Visual Basic ActiveX Script
'  Check For And Delete Existing Files
'  Create Excel Headers
'
'**********************************************************************

Function Main()

      On Error GoTo 0

      Dim fso
      Dim strFile

      Set fso = CreateObject("Scripting.FileSystemObject")

      If (fso.FileExists("C:\temp\dts_temp.XLS")) then

            set strFile = fso.GetFile("C:\temp\dts_temp.XLS")
            strFile.Delete

      End If

      InitializeReport

      If Err.Number <> 0 Then

            Main = DTSTaskExecResult_Failure

      Else

            Main = DTSTaskExecResult_Success

      End If
      
End Function

Public Sub InitializeReport()

      Dim xlApplication
      Dim xlWorkBook
      Dim xlWorkSheet

      Set xlApplication = CreateObject("Excel.Application")

      xlApplication.DisplayAlerts = False

      Set xlWorkBook = xlApplication.WorkBooks.Add
      Set xlWorkSheet = xlWorkBook.Worksheets.Add

      xlWorkSheet.Name = "Results"

      xlWorkBook.Worksheets("Sheet1").Delete
      xlWorkBook.Worksheets("Sheet2").Delete
      xlWorkBook.Worksheets("Sheet3").Delete

      xlWorkSheet.Activate

      xlWorkSheet.Cells(1, 1) = "OP_CODE"
      xlWorkSheet.Cells(1, 2) = "LEASE"
      xlWorkSheet.Cells(1, 3) = "P_DATE"
      xlWorkSheet.Cells(1, 4) = "OIL"
      xlWorkSheet.Cells(1, 5) = "OIL_SLD"
      xlWorkSheet.Cells(1, 6) = "GAS"
      xlWorkSheet.Cells(1, 7) = "GAS_SLD"
      xlWorkSheet.Cells(1, 8) = "WATER"
      xlWorkSheet.Cells(1, 9) = "DYS"

      xlWorkBook.SaveAs "C:\temp\dts_temp.XLS", True

      xlWorkBook.Close
      xlApplication.Quit

End Sub

MS ODBC Driver object configured for the SQL database.

Data Transform connector configured to move the proper source column to the proper destination columns.

Excel 97-2000 pointing to file C:\temp\dts_temp.XLS

ActiveX Task 2:

'**********************************************************************
'
'  Visual Basic ActiveX Script
'  Format Excel
'
'**********************************************************************

Const xlBottom = -4107
Const xlTop = -4160
Const xlLeft = -4131
Const xlCenter = -4108
Const xlRight = -4152
Const xlContinuous = 1
Const xlThin = 2

Function Main()

      On Error GoTo 0

      Dim xlApplication
      Dim xlWorkBook
      Dim xlWorkSheet

      Set xlApplication = CreateObject("Excel.Application")
      xlApplication.DisplayAlerts = False
      Set xlWorkBook = xlApplication.WorkBooks.Open( "C:\temp\dts_temp.XLS")

      Set xlWorkSheet = xlWorkBook.ActiveSheet
      xlWorkSheet.Activate

      FormatWS xlWorkSheet

      xlWorkBook.Save
      xlWorkBook.Close
      xlApplication.Quit

      If Err.Number <> 0 Then

            Main = DTSTaskExecResult_Failure

      Else

            Main = DTSTaskExecResult_Success

      End If
      
End Function

Public Sub FormatWS(xlWorkSheet)

      With xlWorkSheet
            .Columns("A").NumberFormat = "@"
            .Columns("A").HorizontalAlignment = xlLeft

            .Columns("B").NumberFormat = "General"
            .Columns("B").HorizontalAlignment = xlLeft

            .Columns("C").NumberFormat = "mm/dd/yyyy"
            .Columns("C").HorizontalAlignment = xlRight

            .Columns("D").NumberFormat = "0"
            .Columns("D").HorizontalAlignment = xlRight

            .Columns("E").NumberFormat = "0"
            .Columns("E").HorizontalAlignment = xlRight

            .Columns("F").NumberFormat = "0"
            .Columns("F").HorizontalAlignment = xlRight

            .Columns("G").NumberFormat = "0"
            .Columns("G").HorizontalAlignment = xlRight

            .Columns("H").NumberFormat = "0"
            .Columns("H").HorizontalAlignment = xlRight

            .Columns("I").NumberFormat = "0"
            .Columns("I").HorizontalAlignment = xlRight
      End With
      
      With xlWorkSheet.Cells(1, 1)
            .NumberFormat = "General"
            .Interior.ColorIndex  = 15
            .HorizontalAlignment = xlCenter
            .ColumnWidth = 11
            .Borders(xlBottom).LineStyle = xlContinuous
                .Borders(xlBottom).Weight = xlThin
                .Borders(xlBottom).ColorIndex = 1
            .Borders(xlTop).LineStyle = xlContinuous
                .Borders(xlTop).Weight = xlThin
                .Borders(xlTop).ColorIndex = 1
            .Borders(xlRight).LineStyle = xlContinuous
                .Borders(xlRight).Weight = xlThin
                .Borders(xlRight).ColorIndex = 1
            .Borders(xlLeft).LineStyle = xlContinuous
                .Borders(xlLeft).Weight = xlThin
                .Borders(xlLeft).ColorIndex = 1
      End With
      
      With xlWorkSheet.Cells(1, 2)
            .NumberFormat = "General"
            .Interior.ColorIndex  = 15
            .HorizontalAlignment = xlCenter
            .ColumnWidth = 34.14
            .Borders(xlBottom).LineStyle = xlContinuous
                .Borders(xlBottom).Weight = xlThin
                .Borders(xlBottom).ColorIndex = 1
            .Borders(xlTop).LineStyle = xlContinuous
                .Borders(xlTop).Weight = xlThin
                .Borders(xlTop).ColorIndex = 1
            .Borders(xlRight).LineStyle = xlContinuous
                .Borders(xlRight).Weight = xlThin
                .Borders(xlRight).ColorIndex = 1
            .Borders(xlLeft).LineStyle = xlContinuous
                .Borders(xlLeft).Weight = xlThin
                .Borders(xlLeft).ColorIndex = 1
      End With

      With xlWorkSheet.Cells(1, 3)
            .NumberFormat = "General"
            .Interior.ColorIndex  = 15
            .HorizontalAlignment = xlCenter
            .ColumnWidth = 9.43
            .Borders(xlBottom).LineStyle = xlContinuous
                .Borders(xlBottom).Weight = xlThin
                .Borders(xlBottom).ColorIndex = 1
            .Borders(xlTop).LineStyle = xlContinuous
                .Borders(xlTop).Weight = xlThin
                .Borders(xlTop).ColorIndex = 1
            .Borders(xlRight).LineStyle = xlContinuous
                .Borders(xlRight).Weight = xlThin
                .Borders(xlRight).ColorIndex = 1
            .Borders(xlLeft).LineStyle = xlContinuous
                .Borders(xlLeft).Weight = xlThin
                .Borders(xlLeft).ColorIndex = 1
      End With

      With xlWorkSheet.Cells(1, 4)
            .NumberFormat = "General"
            .Interior.ColorIndex  = 15
            .HorizontalAlignment = xlCenter
            .ColumnWidth = 9
            .Borders(xlBottom).LineStyle = xlContinuous
                .Borders(xlBottom).Weight = xlThin
                .Borders(xlBottom).ColorIndex = 1
            .Borders(xlTop).LineStyle = xlContinuous
                .Borders(xlTop).Weight = xlThin
                .Borders(xlTop).ColorIndex = 1
            .Borders(xlRight).LineStyle = xlContinuous
                .Borders(xlRight).Weight = xlThin
                .Borders(xlRight).ColorIndex = 1
            .Borders(xlLeft).LineStyle = xlContinuous
                .Borders(xlLeft).Weight = xlThin
                .Borders(xlLeft).ColorIndex = 1
      End With

      With xlWorkSheet.Cells(1, 5)
            .NumberFormat = "General"
            .Interior.ColorIndex  = 15
            .HorizontalAlignment = xlCenter
            .ColumnWidth = 9
            .Borders(xlBottom).LineStyle = xlContinuous
                .Borders(xlBottom).Weight = xlThin
                .Borders(xlBottom).ColorIndex = 1
            .Borders(xlTop).LineStyle = xlContinuous
                .Borders(xlTop).Weight = xlThin
                .Borders(xlTop).ColorIndex = 1
            .Borders(xlRight).LineStyle = xlContinuous
                .Borders(xlRight).Weight = xlThin
                .Borders(xlRight).ColorIndex = 1
            .Borders(xlLeft).LineStyle = xlContinuous
                .Borders(xlLeft).Weight = xlThin
                .Borders(xlLeft).ColorIndex = 1
      End With

      With xlWorkSheet.Cells(1, 6)
            .NumberFormat = "General"
            .Interior.ColorIndex  = 15
            .HorizontalAlignment = xlCenter
            .ColumnWidth = 9
            .Borders(xlBottom).LineStyle = xlContinuous
                .Borders(xlBottom).Weight = xlThin
                .Borders(xlBottom).ColorIndex = 1
            .Borders(xlTop).LineStyle = xlContinuous
                .Borders(xlTop).Weight = xlThin
                .Borders(xlTop).ColorIndex = 1
            .Borders(xlRight).LineStyle = xlContinuous
                .Borders(xlRight).Weight = xlThin
                .Borders(xlRight).ColorIndex = 1
            .Borders(xlLeft).LineStyle = xlContinuous
                .Borders(xlLeft).Weight = xlThin
                .Borders(xlLeft).ColorIndex = 1
      End With

      With xlWorkSheet.Cells(1, 7)
            .NumberFormat = "General"
            .Interior.ColorIndex  = 15
            .HorizontalAlignment = xlCenter
            .ColumnWidth = 9
            .Borders(xlBottom).LineStyle = xlContinuous
                .Borders(xlBottom).Weight = xlThin
                .Borders(xlBottom).ColorIndex = 1
            .Borders(xlTop).LineStyle = xlContinuous
                .Borders(xlTop).Weight = xlThin
                .Borders(xlTop).ColorIndex = 1
            .Borders(xlRight).LineStyle = xlContinuous
                .Borders(xlRight).Weight = xlThin
                .Borders(xlRight).ColorIndex = 1
            .Borders(xlLeft).LineStyle = xlContinuous
                .Borders(xlLeft).Weight = xlThin
                .Borders(xlLeft).ColorIndex = 1
      End With

      With xlWorkSheet.Cells(1, 8)
            .NumberFormat = "General"
            .Interior.ColorIndex  = 15
            .HorizontalAlignment = xlCenter
            .ColumnWidth = 9
            .Borders(xlBottom).LineStyle = xlContinuous
                .Borders(xlBottom).Weight = xlThin
                .Borders(xlBottom).ColorIndex = 1
            .Borders(xlTop).LineStyle = xlContinuous
                .Borders(xlTop).Weight = xlThin
                .Borders(xlTop).ColorIndex = 1
            .Borders(xlRight).LineStyle = xlContinuous
                .Borders(xlRight).Weight = xlThin
                .Borders(xlRight).ColorIndex = 1
            .Borders(xlLeft).LineStyle = xlContinuous
                .Borders(xlLeft).Weight = xlThin
                .Borders(xlLeft).ColorIndex = 1
      End With

      With xlWorkSheet.Cells(1, 9)
            .NumberFormat = "General"
            .Interior.ColorIndex  = 15
            .HorizontalAlignment = xlCenter
            .ColumnWidth = 9
            .Borders(xlBottom).LineStyle = xlContinuous
                .Borders(xlBottom).Weight = xlThin
                .Borders(xlBottom).ColorIndex = 1
            .Borders(xlTop).LineStyle = xlContinuous
                .Borders(xlTop).Weight = xlThin
                .Borders(xlTop).ColorIndex = 1
            .Borders(xlRight).LineStyle = xlContinuous
                .Borders(xlRight).Weight = xlThin
                .Borders(xlRight).ColorIndex = 1
            .Borders(xlLeft).LineStyle = xlContinuous
                .Borders(xlLeft).Weight = xlThin
                .Borders(xlLeft).ColorIndex = 1
      End With

End Sub
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question