VBScript, XLS, Formattting and other Functions

Hello, does anyone have a list of properties that you can change when writing a XLS file from VBScripting?  Thing I am interested...

How to write to multiple sheets in the same file... Say a new page per server, but in the same xls.
How to change the cell colors
How to change font, font size, font color

If there is a reference with the entire list properties, it'd be great.
mmitchell57Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

exx1976Commented:
Below are some functions I've written to do varous things in Excel.  I have hundreds more, but they're scattered across as many scripts, and I don't have time to go dig them up.  These should get you started.

If you're unsure how to do something, jsut record a macro of you doing it in the GUI, then save the macro, and then view the macro code.  VBA is similar to VBS, you should be able to figure out how to make it work.

HTH,
exx
Const xlCenter = &hffffeff4
Const xlLeft = &hffffefdd
Const xlRight = &hffffefc8
Const xlBottom = &hffffeff5
Const xlTop = &hffffefc0
Const xlInsideHorizontal = 12
Const xlInsideVertical = 11
Const xlDiagonalDown = 5
Const xlDiagonalUp = 6
Const xlEdgeBottom = 9
Const xlEdgeRight = 10
Const xlEdgeLeft = 7
Const xlEdgeTop = 8
Const xlNone = &hffffefd2
Const xlThin = 2
Const xlContinuous = 1
Const xlMedium = &hffffefd6
Const xlUnderlineStyleSingle = 2
Const xlAscending = 1
Const xlNo = 2
Const xlDescending = 2
 
 
Sub Underline(sheetNumber, underlineStyle, cells)
	On Error Resume Next
	arr = Split(cells,":",-1,1)
	Set sheet = oExcelApp.ActiveWorkBook.Worksheets(sheetNumber)
	If arr(0) = arr(1) Then
		Set oRange = sheet.Range(arr(0))
	Else
		Set oRange = sheet.Range(arr(0), arr(1))
	End If
	oRange.Font.Underline = underlineStyle
End Sub
 
Sub Sort(sheetNumber, cells, key, direction, header)
 On Error Resume Next
 
 arr = Split(cells,":",-1,1)
 Set sheet = oExcelApp.ActiveWorkbook.Worksheets(sheetNumber)
 If arr(0) = arr(1) Then
  Exit Sub
 Else
  Set oRange = sheet.Range(arr(0), arr(1))
  Set oRangeKey = oExcelApp.Range(key)
  oRange.Sort oRangeKey,direction,,,,,,header
 End If
End Sub
 
Sub AccountingFormat(sheetNumber, cells)
	On Error Resume Next
	arr = Split(cells,":",-1,1)
	Set sheet = oExcelApp.ActiveWorkBook.Worksheets(sheetNumber)
	If arr(0) = arr(1) Then
		Set oRange = sheet.Range(arr(0))
	Else
		Set oRange = sheet.Range(arr(0), arr(1))
	End If
	oRange.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
End Sub
 
Sub MoneyFormat(sheetNumber, cells)
	On Error Resume Next
	arr = Split(cells,":",-1,1)
	Set sheet = oExcelApp.ActiveWorkBook.Worksheets(sheetNumber)
	If arr(0) = arr(1) Then
		Set oRange = sheet.Range(arr(0))
	Else
		Set oRange = sheet.Range(arr(0), arr(1))
	End If
	oRange.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
End Sub
 
Sub TextFormat(sheetNumber, cells)
	On Error Resume Next
	arr = Split(cells,":",-1,1)
	Set sheet = oExcelApp.ActiveWorkBook.Worksheets(sheetNumber)
	If arr(0) = arr(1) Then
		Set oRange = sheet.Range(arr(0))
	Else
		Set oRange = sheet.Range(arr(0), arr(1))
	End If
	oRange.NumberFormat = "@"
End Sub
 
Sub PercentFormat(sheetNumber, cells)
	On Error Resume Next
	arr = Split(cells,":",-1,1)
	Set sheet = oExcelApp.ActiveWorkBook.Worksheets(sheetNumber)
	If arr(0) = arr(1) Then
		Set oRange = sheet.Range(arr(0))
	Else
		Set oRange = sheet.Range(arr(0), arr(1))
	End If
	oRange.NumberFormat = "0.00%"
End Sub
 
Sub BorderBoxOutside(sheetNumber, weight, cells)
	On Error Resume Next
	Border sheetNumber, xlDiagonalUp, xlNone, cells
	Border sheetNumber, xlDiagonalDown, xlNone, cells
	Border sheetNumber, xlEdgeLeft, xlContinuous, cells
	Border sheetNumber, xlEdgeTop, xlContinuous, cells
	Border sheetNumber, xlEdgeRight, xlContinuous, cells
	Border sheetNumber, xlEdgeBottom, xlContinuous, cells
	BorderWeight sheetNumber, xlEdgeLeft, Weight, cells
	BorderWeight sheetNumber, xlEdgeTop, Weight, cells
	BorderWeight sheetNumber, xlEdgeRight, Weight, cells
	BorderWeight sheetNumber, xlEdgeBottom, Weight, cells
End Sub
 
Sub BorderBox(sheetNumber, weight, cells)
	On Error Resume Next
	Border sheetNumber, xlDiagonalUp, xlNone, cells
	Border sheetNumber, xlDiagonalDown, xlNone, cells
	Border sheetNumber, xlEdgeLeft, xlContinuous, cells
	Border sheetNumber, xlEdgeTop, xlContinuous, cells
	Border sheetNumber, xlEdgeRight, xlContinuous, cells
	Border sheetNumber, xlEdgeBottom, xlContinuous, cells
	Border sheetNumber, xlInsideVertical, xlContinuous, cells
	Border sheetNumber, xlInsideHorizontal, xlContinuous, cells
	BorderWeight sheetNumber, xlEdgeLeft, Weight, cells
	BorderWeight sheetNumber, xlEdgeTop, Weight, cells
	BorderWeight sheetNumber, xlEdgeRight, Weight, cells
	BorderWeight sheetNumber, xlEdgeBottom, Weight, cells
	BorderWeight sheetNumber, xlInsideVertical, Weight, cells
	BorderWeight sheetNumber, xlInsideHorizontal, Weight, cells
End Sub
 
Sub Border(sheetNumber, alignment, lineStyle, cells)
	On Error Resume Next
	arr = Split(cells,":",-1,1)
	Set sheet = oExcelApp.ActiveWorkBook.Worksheets(sheetNumber)
	If arr(0) = arr(1) Then
		Set oRange = sheet.Range(arr(0))
	Else
		Set oRange = sheet.Range(arr(0), arr(1))
	End If
	oRange.Borders(alignment).LineStyle = lineStyle
End Sub
 
Sub BorderWeight(sheetNumber, alignment, weight, cells)
	On Error Resume Next
	arr = Split(cells,":",-1,1)
	Set sheet = oExcelApp.ActiveWorkBook.Worksheets(sheetNumber)
	If arr(0) = arr(1) Then
		Set oRange = sheet.Range(arr(0))
	Else
		Set oRange = sheet.Range(arr(0), arr(1))
	End If
	oRange.Borders(alignment).Weight = Weight
End Sub
 
Sub FontColor(sheetNumber, colorOfFont, cells)
	On Error Resume Next
	arr = Split(cells,":",-1,1)
	Set sheet = oExcelApp.ActiveWorkBook.Worksheets(sheetNumber)
	If arr(0) = arr(1) Then
		Set oRange = sheet.Range(arr(0))
	Else
		Set oRange = sheet.Range(arr(0), arr(1))
	End If
	oRange.Font.ColorIndex = colorOfFont
End Sub
 
Sub Bold(sheetNumber, cells)
	On Error Resume Next
	arr = Split(cells,":",-1,1)
	Set sheet = oExcelApp.ActiveWorkBook.Worksheets(sheetNumber)
	If arr(0) = arr(1) Then
		Set oRange = sheet.Range(arr(0))
	Else
		Set oRange = sheet.Range(arr(0), arr(1))
	End If
	oRange.Font.Bold = True
End Sub
 
Sub FontName(sheetNumber, nameOfFont, cells)
	On Error Resume Next
	arr = Split(cells,":",-1,1)
	Set sheet = oExcelApp.ActiveWorkBook.Worksheets(sheetNumber)
	If arr(0) = arr(1) Then
		Set oRange = sheet.Range(arr(0))
	Else
		Set oRange = sheet.Range(arr(0), arr(1))
	End If
	oRange.Font.Name = nameOfFont
End Sub
 
Sub FontSize(sheetNumber, sizeOfFont, cells)
	On Error Resume Next
	arr = Split(cells,":",-1,1)
	Set sheet = oExcelApp.ActiveWorkBook.Worksheets(sheetNumber)
	If arr(0) = arr(1) Then
		Set oRange = sheet.Range(arr(0))
	Else
		Set oRange = sheet.Range(arr(0), arr(1))
	End If
	oRange.Font.Size = sizeOfFont
End Sub
 
Sub Horizontal(sheetNumber, alignment, cells)
	On Error Resume Next
	arr = Split(cells,":",-1,1)
	Set sheet = oExcelApp.ActiveWorkBook.Worksheets(sheetNumber)
	If arr(0) = arr(1) Then
		Set oRange = sheet.Range(arr(0))
	Else
		Set oRange = sheet.Range(arr(0), arr(1))
	End If
	oRange.horizontalAlignment = alignment
End Sub
 
Sub Vertical(sheetNumber, alignment, cells)
	On Error Resume Next
	arr = Split(cells,":",-1,1)
	Set sheet = oExcelApp.ActiveWorkBook.Worksheets(sheetNumber)
	If arr(0) = arr(1) Then
		Set oRange = sheet.Range(arr(0))
	Else
		Set oRange = sheet.Range(arr(0), arr(1))
	End If
	oRange.verticalAlignment = alignment
End Sub
 
Sub Merge(sheetNumber, cells)
	On Error Resume Next
	arr = Split(cells,":",-1,1)
	Set sheet = oExcelApp.ActiveWorkBook.Worksheets(sheetNumber)
	If arr(0) = arr(1) Then
		Set oRange = sheet.Range(arr(0))
	Else
		Set oRange = sheet.Range(arr(0), arr(1))
	End If
	oRange.Merge
End Sub
 
Sub Write(sheetNumber, data, cells)
	On Error Resume Next
	arr = Split(cells,":",-1,1)
	Set sheet = oExcelApp.ActiveWorkBook.Worksheets(sheetNumber)
	If arr(0) = arr(1) Then
		Set oRange = sheet.Range(arr(0))
	Else
		Set oRange = sheet.Range(arr(0), arr(1))
	End If
	oRange.FormulaR1C1 = data
End Sub
 
Sub ColorCells(sheetNumber, colorIndex, cells)
	On Error Resume Next
	arr = Split(cells,":",-1,1)
	Set sheet = oExcelApp.ActiveWorkBook.Worksheets(sheetNumber)
	If arr(0) = arr(1) Then
		Set oRange = sheet.Range(arr(0))
	Else
		Set oRange = sheet.Range(arr(0), arr(1))
	End If
	oRange.Interior.ColorIndex = colorIndex
End Sub
 
Sub WrapText(SheetNumber, cells)
	On Error Resume Next
	arr = Split(cells,":",-1,1)
	Set sheet = oExcelApp.ActiveWorkBook.Worksheets(sheetNumber)
	If arr(0) = arr(1) Then
		Set oRange = sheet.Range(arr(0))
	Else
		Set oRange = sheet.Range(arr(0), arr(1))
	End If
	oRange.WrapText = True
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mmitchell57Author Commented:
Thank you for the hint about Macro's. That will help out quite a bit. Also, thank you for all the snippets. :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.