bwbpro
asked on
VB Script to create and format Excel file
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("OrderNumbe r") = DTSSource("OrderNumber")
DTSDestination("OrderDate" ) = DTSSource("OrderDate")
DTSDestination("Customer") = DTSSource("Customer")
DTSDestination("DocumentTy pe") = DTSSource("DocumentType")
DTSDestination("Project") = DTSSource("Project")
DTSDestination("Rep_1") = DTSSource("Rep_1")
DTSDestination("Rep_2") = DTSSource("Rep_2")
Main = DTSTransformStat_OK
End Function
Here is the script:
'*************************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'*************************
Function Main()
DTSDestination("OrderNumbe
DTSDestination("OrderDate"
DTSDestination("Customer")
DTSDestination("DocumentTy
DTSDestination("Project") = DTSSource("Project")
DTSDestination("Rep_1") = DTSSource("Rep_1")
DTSDestination("Rep_2") = DTSSource("Rep_2")
Main = DTSTransformStat_OK
End Function
ASKER
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.xl s. 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(xlDiagon alDown).Li neStyle = xlNone
Selection.Borders(xlDiagon alUp).Line Style = xlNone
Selection.Borders(xlEdgeLe ft).LineSt yle = xlNone
Selection.Borders(xlEdgeTo p).LineSty le = xlNone
Selection.Borders(xlEdgeBo ttom).Line Style = xlNone
Selection.Borders(xlEdgeRi ght).LineS tyle = xlNone
Selection.Borders(xlInside Vertical). LineStyle = xlNone
Columns("A:A").ColumnWidth = 10#
Columns("A:A").ColumnWidth = 9.78
Columns("B:B").EntireColum n.AutoFit
Columns("C:C").EntireColum n.AutoFit
Columns("D:D").EntireColum n.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.Prin tArea = ""
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
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(xlDiagon
Selection.Borders(xlDiagon
Selection.Borders(xlEdgeLe
Selection.Borders(xlEdgeTo
Selection.Borders(xlEdgeBo
Selection.Borders(xlEdgeRi
Selection.Borders(xlInside
Columns("A:A").ColumnWidth
Columns("A:A").ColumnWidth
Columns("B:B").EntireColum
Columns("C:C").EntireColum
Columns("D:D").EntireColum
Columns("E:E").ColumnWidth
Columns("H:H").ColumnWidth
Columns("I:I").ColumnWidth
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.Prin
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "Back Order Report"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page &P"
.RightFooter = ""
.LeftMargin = Application.InchesToPoints
.RightMargin = Application.InchesToPoints
.TopMargin = Application.InchesToPoints
.BottomMargin = Application.InchesToPoints
.HeaderMargin = Application.InchesToPoints
.FooterMargin = Application.InchesToPoints
.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
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.
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.
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
ASKER
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.xl s, 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:\Au tomated Reports\BackOrderReport.xl s)
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
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.xl
Public Sub FormatThisFile(byval strFileName as string)
Set Excel = GetObject(, "Excel.Application") ' Create Excel Object
Set ExcelWBk = Excel.Workbooks.Open(D:\Au
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
ASKER
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
Glad to help.
Columns("A:A").ColumnWidth
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