VBScript Code for Excel formatting

VBScripting is capable of providing us will enormous amounts of information. The problem that we with VBScript is how to present that information? One way to do this is to use MS Excel to display our output. The following lines of code will open an Excel workbook. It will then enumerate and display some of the potential property values for borders in Excel. By looking through this script, you will get an idea of how to format the cells, enter data, and selecting the cell that you want to place data in.

' =============================================================================
' Boarder1
' File: Border1.vbs
' Author: Jason A. Yoder
' Version 1.0 Status: Completed
' -----------------------------------------------------------------------------
' Test script for Creating boarders in Excel.
' =============================================================================


' == Main Code ==============================================================
Set objExcel = CreateObject("Excel.Application")

' Open the spreadsheet
OpenExcelWorksheet

' Worksheet Title
WorksheetTitle

' Border Thickness
BorderThickness

' Border Position
BorderPosition

' Line Style
LineStyle

' Line Color
LineColor


' == End Main Code ==========================================================


' ++ Procedures +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

' ++ OpenExcelWorksheet +++++++++++++++++++++++++++++++++++++++++++++++++++++
' Opens the spreadsheet specified in the variable 'TargetWorksheet'.
Sub OpenExcelWorksheet

objExcel.Visible = True
objExcel.Workbooks.Add



End Sub ' OpenExcelWorksheet
' ++ End OpenExcelWorksheet +++++++++++++++++++++++++++++++++++++++++++++++++

' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Sub WorksheetTitle
' Creates a page title
objExcel.Cells(1,1).Value = "Excel Cell Formatting Referance Chart"
objExcel.Cells(1,1).Font.Size = 20
End Sub ' WorksheetTitle
' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Sub BorderThickness
' Creates borders of various thickness
objExcel.Cells(2,2).Value = "Weight"
For X = 3 to 6
'Set objRange = ObjExcel.Range(1,1)
ObjExcel.Cells(X,2).Borders.Weight = X - 2
ObjExcel.Cells(X,2).Value = X - 2
Next ' X
End Sub ' BorderThickness

' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Sub BorderPosition
' Creates borders at different positions
objExcel.Cells(2,4).Value = "Posiitons"
Y = 1
For X = 3 to 26 Step 2
'Set objRange = ObjExcel.Range(1,1)
ObjExcel.Cells(X,4).Borders(Y).Weight = 2
ObjExcel.Cells(X,4).Value = Y
Y = Y + 1
Next ' X
End Sub ' BorderPosition
' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Sub LineStyle
' Changes the line style
objExcel.Cells(2,6).Value = "Style"
Y = 1
For X = 3 to 28 Step 2
'Set objRange = ObjExcel.Range(1,1)
ObjExcel.Cells(X,6).Borders(9).Weight = 2
ObjExcel.Cells(X,6).Borders(9).LineStyle = Y
ObjExcel.Cells(X,6).Value = Y

Y = Y + 1
Next ' X
End Sub ' LineStyle
' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Sub LineColor
' Changes the line color
objExcel.Cells(2,8).Value = "Color"
Z = 0
For X = 8 to 11
For Y = 3 to 29 Step 2
'Set objRange = ObjExcel.Range(1,1)
ObjExcel.Cells(Y,X).Borders(9).Weight = 4
ObjExcel.Cells(Y,X).Borders(9).ColorIndex = Z
ObjExcel.Cells(Y,X).Value = Z
Z = Z + 1
Next ' Y
Next ' x
End Sub ' LineStyle
' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++



' ++ End Procedures +++++++++++++++++++++++++++++++++++++++++++++++++++++++++

' == End of Script ==========================================================