?
Solved

Export for MS SQL to Excel 2000

Posted on 2003-02-27
5
Medium Priority
?
424 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

764 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