Solved

VB Script to create and format Excel file

Posted on 2007-03-24
9
8,382 Views
Last Modified: 2013-11-30
I have created a DTS Package in SQL the runs a query and creates an Excel file with that data.  I would like to be able to create the file with some basic formatting (column widths, row height, font & font  size by row or column.  The DTS package creates a VB Transformation script that I will attach below.  Does anyone if it is possible and if so, how I can add sript to this that will include the type of formatting mentioned above?  FYI...I know very little about programming and Visual Basic.  I just started to teach myself some basics but I'm about as gren as it gets.  Thanks in advance for any help you can provide!  Brett

Here is the script:

'**********************************************************************
'  Visual Basic Transformation Script
'  Copy each source column to the
'  destination column
'************************************************************************

Function Main()
      DTSDestination("OrderNumber") = DTSSource("OrderNumber")
      DTSDestination("OrderDate") = DTSSource("OrderDate")
      DTSDestination("Customer") = DTSSource("Customer")
      DTSDestination("DocumentType") = DTSSource("DocumentType")
      DTSDestination("Project") = DTSSource("Project")
      DTSDestination("Rep_1") = DTSSource("Rep_1")
      DTSDestination("Rep_2") = DTSSource("Rep_2")
      Main = DTSTransformStat_OK
End Function
0
Comment
Question by:bwbpro
  • 4
  • 4
9 Comments
 
LVL 1

Expert Comment

by:cybeh
ID: 18787005
I am not sure if I do understand your problem. Have a look on this

    Columns("A:A").ColumnWidth = 13.14
    With Selection.Font
        .Name = "Arial"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Selection.Font.Bold = True
    Selection.Font.ColorIndex = 3

0
 

Author Comment

by:bwbpro
ID: 18787166
Cybeh - I think you're on the right track with what I need.  I'm thinking the best way to accomplish this may be to run a scheduled vb script after the file is created from the DTS Package.  The problem is I don't know how to write the scipt (I'm just starting to read up on VB).  The script would have to call the existing file, run the formatting script and save/close it.  The best I can pull off at this point is to record the macro within Excel for the exact formatting I need.  The file name and location is D:\Automated Reports\BackOrderReport.xls.  I'll also attach the macro I recorded.  If you can help me turn this into a script I would be very grateful!  Brett

Here's the macro:

Sub Formatting()
    Rows("1:1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Tahoma"
        .FontStyle = "Bold"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Columns("A:A").ColumnWidth = 10#
    Columns("A:A").ColumnWidth = 9.78
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("E:E").ColumnWidth = 36.89
    Columns("H:H").ColumnWidth = 11.44
    Columns("I:I").ColumnWidth = 7.67
        With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "Back Order Report"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = True
        .PrintComments = xlPrintNoComments
        .PrintQuality = 300
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 80
        .PrintErrors = xlPrintErrorsDisplayed
    End With
    ActiveWorkbook.Save
End Sub
0
 
LVL 1

Expert Comment

by:cybeh
ID: 18787618
I am not sure if I understand your problem. I guess you want sometime where you supply a file name, and the script will do the macro that you mention?

So, maybe it's something like
C:\myAutoScript.exe C:\myExcelFile.xls

then it will do the format to C:\myExcelFile.xls?

Please let me know if I am on the correct path.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:bwbpro
ID: 18788316
Cybeh - Yes, that is what I'm looking for.  A script to execute and format my existing Excel file based on the macro I recorded above.  Just to give you an idea what I have set up right now; I have an IT guy that wrote a .bat that is scheduled to run a vbscript.  This script emails the file I've mentioned once per week. Unfortantely, the file is not formatted to be very readable or printable for the recipients.   The recipients are salespeople that are very technology challenged.  For the formatting, I thought we could possibly setup the same scenario to have .bat run a vbscript that formats this file as needed (see recorded macro in my previous message).  I would schedule this script to run before the email script (or add it to the existing email script).  Thus, the recipient receives a nice, clean excel file, they can print without having to do any formatting themselves.  Unfortunately, my IT guy is not familiar with scripting to format office docs.  I'm always open to another approach if you know of a simpler way to accomplish this.  Thanks again for taking a look at this!  Brett
0
 
LVL 1

Accepted Solution

by:
cybeh earned 500 total points
ID: 18789551
I just give u some sample. You need to modify it base on your own macro.

Option Explicit
'Define the required variable
Dim Excel As Excel.Application ' This is the excel program
Dim ExcelWBk As Excel.Workbook ' This is the work book
Dim ExcelWS As Excel.Worksheet ' This is the sheet

Public Sub FormatThisFile(byval strFileName as string)
   Set Excel = GetObject(, "Excel.Application") ' Create Excel Object
   Set ExcelWBk = Excel.Workbooks.Open(strFileName)
   Set ExcelWS = ExcelWBk.Worksheets(1)
   Excel.Visible = False
   call Formatting
    Excel.Visible = True
    ExcelWBk.Close
    Excel.Quit
    Set ExcelWBk = Nothing
    Set Excel = Nothing
End Sub

Sub Formatting()
    With ExcelWS
        With .Range("1:1")
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .WrapText = True
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
            .Font.Name = "Tahoma"
            .Font.FontStyle = "Bold"
            .Font.Size = 8
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
            .Borders(xlEdgeLeft).LineStyle = xlNone
            .Borders(xlEdgeTop).LineStyle = xlNone
            .Borders(xlEdgeBottom).LineStyle = xlNone
            .Borders(xlEdgeRight).LineStyle = xlNone
            .Borders(xlInsideVertical).LineStyle = xlNone
        End with
       
        With .Range("A:A").ColumnWidth = 10#
       
       
    End with

End Sub
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18796813
Perhaps I am stating the obvious, but you do realize you will have to install MS Office on the same server as MS SQL Server, right?
0
 

Author Comment

by:bwbpro
ID: 18801516
Yep acperkins - I did make sure Office was installed, but I do understand you can never assume the obvious.

Cybeh - I'm having a tough time running the script you provided but that is probably because I'm a complete vbscript idiot (trying to progress to an idiot with enough knowledge to be dangerous).  When I run it the console opens an closes immediately without running anything.  Also wondering, if my existing file is D:\Automated Reports\BackOrderReport.xls, how would I incorporate that into your string?  Would it be as below?

Public Sub FormatThisFile(byval strFileName as string)
   Set Excel = GetObject(, "Excel.Application") ' Create Excel Object
   Set ExcelWBk = Excel.Workbooks.Open(D:\Automated Reports\BackOrderReport.xls)
   Set ExcelWS = ExcelWBk.Worksheets(1)
   Excel.Visible = False
   call Formatting
    Excel.Visible = True
    ExcelWBk.Close
    Excel.Quit
    Set ExcelWBk = Nothing
    Set Excel = Nothing
End Sub

I'm thinking not since I tried it and it didn't work.  Can you help me out with what I may be missing?

Sorry if these are a dumb questions but I'm trying.  I just have a lot to learn.
Thanks again!
Brett


0
 

Author Comment

by:bwbpro
ID: 19432171
Cybeh - Thanks for all of your help.  I'm am now on SQL 52005 and Reporting Services but your help with SQL 2000 and DTS was very valuable...Brett
0
 
LVL 1

Expert Comment

by:cybeh
ID: 19432731
Glad to help.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SqlServer no dupes 25 34
string fuctions 4 25
SQL Server 2012 r2 - Varible Table 3 23
VBA - If Bookmark = "XXBOOKMARKXX" then 15 25
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
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.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

785 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