CODEWITHSUNDEEP
vbscript
Geddy
Geddy

Reputation:

VBScript Excel Formatting .xlsx files

Basically I want to know how to set center alignment for a cell using VBScript...

I've been googling it and can't seem to find anything that helps.

Upvotes: 3

Views: 21924

Answers (2)

David J. Sokol
David J. Sokol

Reputation: 3566

Set excel = CreateObject("Excel.Application")

excel.Workbooks.Add() ' create blank workbook

Set workbook = excel.Workbooks(1)

' set A1 to be centered.
workbook.Sheets(1).Cells(1,1).HorizontalAlignment = -4108 ' xlCenter constant.

workbook.SaveAs("C:\NewFile.xls")

excel.Quit()

set excel = nothing

'If the script errors, it'll give you an orphaned excel process, so be warned.

Save that as a .vbs and run it using the command prompt or double clicking.

Upvotes: 5

mistrmark
mistrmark

Reputation: 4349

There are many ways to select a cell or a range of cells, but the following will work for a single cell.

'Select a Cell Range
Range("D4").Select

'Set the horizontal and vertical alignment
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
End With

The HorizontalAlignment options are xlLeft, xlRight, and xlCenter

Upvotes: 1

Related Questions