How to insert the Thousands Separator (comma) in Excel?

[Highlander]

Hello all.

I've got a VBScript that takes a CSV file, makes some formatting
changes, and saves it to an XLS file. What I'd like to add to this
script is the command to insert the Thousands Separator in column B
for the entire column. Column B contains numbers in every cell except
for the top cell which has a word.

In searching I did find the Constant "Const xlThousandsSeparator = 4"
but I can't quite figure out how to write the line to implement it.

Any suggestions would be greatly appreciated. Thanks!

My script:

Const xlDescending = 2
Const xlNormal = 1
Const xlCenter = -4108

Set objFSO = CreateObject("Scripting.FileSystemObject")

Dim CurDir
CurDir = objFSO.GetFolder(".").Path
Set Folder = objFSO.GetFolder(CurDir)
Set Folder = nothing

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open(CurDir & "\Results.csv")
Set objWorksheet = objWorkbook.Worksheets(1)

'~~ Sort by Column B, Descending
Set objRange = objWorksheet.UsedRange
Set objRange2 = objExcel.Range("B1")
objRange.Sort objRange2,2

'~~ Insert blank row at Row 1
sRow = "1"
objExcel.Worksheets(1).Rows(sRow).Insert xlDown

'~~ Insert Header text
objExcel.Worksheets(1).Cells(1,1).Value = "Server"
objExcel.Worksheets(1).Cells(1,2).Value = "Error Amount"

'~~ Bold the Header row
Set objRange3 = objExcel.Range("A1:B1")
objRange3.Font.Bold = True

'~~ Auto Fit and Center both Columns
objExcel.Cells.EntireColumn.AutoFit
objExcel.Cells.EntireColumn.HorizontalAlignment = xlCenter

'~~ Insert blank row at Row 2
sRow = "2"
objExcel.Worksheets(1).Rows(sRow).Insert xlDown

objWorkbook.SaveAs CurDir & "\Results.xls",1

objWorkbook.Close
objExcel.Quit

Set objFSO = nothing
Set objExcel = nothing
Set objWorkbook = nothing
Set objWorksheet = nothing
Set objRange = nothing
Set objRange2 = nothing
Set objRange3 = nothing

 

[LakeGator]

Hello all.

I've got a VBScript that takes a CSV file, makes some formatting
changes, and saves it to an XLS file. What I'd like to add to this
script is the command to insert the Thousands Separator in column B
for the entire column. Column B contains numbers in every cell except
for the top cell which has a word.

In searching I did find the Constant "Const xlThousandsSeparator = 4"
but I can't quite figure out how to write the line to implement it.

Any suggestions would be greatly appreciated. Thanks!

My script: (trimmed out to save valuable electronic trees)

The approach I take in cases like this is to record an Excel Macro.
This creates VBA code to implement what I need. I then translate the
VBA into VBScript.

I believe this will work for your case. Hope it helps.

 

[Highlander]

The approach I take in cases like this is to record an Excel Macro.
This creates VBA code to implement what I need. I then translate the
VBA into VBScript.

I believe this will work for your case. Hope it helps.

Cool!

Recording a macro gave me this:

Sub Comma()
Columns("B:B").Select
Selection.NumberFormat = "#,##0"
End Sub

After a little tweaking and testing, adding these two lines to my
script produced the desired results:

objExcel.ActiveSheet.Range("B:B").Select
objExcel.Selection.NumberFormat = "#,##0"

Thanks LakeGator!

- Dave

 

[Guest]

objExcel.ActiveSheet.Range("B:B").Select
objExcel.Selection.NumberFormat = "#,##0"

No need to select the column:

objExcel.ActiveSheet.Columns("B:B").NumberFormat = "#,##0"

 

[Highlander]

No need to select the column:

objExcel.ActiveSheet.Columns("B:B").NumberFormat = "#,##0"

That pares it down to just one line. Thanks urkec.

While we're on a roll, I have another question. I want to implement
conditional formatting; where if a cell value in Column C is greater
than 50000, then that cell font will be changed to bold. Recording a
macro gave me this:

Sub Bold_Column()
Columns("C:C").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, _
Formula1:="50000"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
End With
End Sub

I've tried transposing the code from this macro into my script, with
no luck. Any suggestions would be greatly appreciated.

Thanks!

- Dave

 

[ekkehard.horner]

That pares it down to just one line. Thanks urkec.

While we're on a roll, I have another question. I want to implement
conditional formatting; where if a cell value in Column C is greater
than 50000, then that cell font will be changed to bold. Recording a
macro gave me this:

Sub Bold_Column()
Columns("C:C").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, _
Formula1:="50000"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
End With
End Sub

I've tried transposing the code from this macro into my script, with
no luck. Any suggestions would be greatly appreciated.

Thanks!

- Dave

From your

Const xlDescending = 2
Const xlNormal = 1
Const xlCenter = -4108

code I gather, you'll have no problems to add the constants
for xlCellValue and xlGreater. That leaves you with the task to
convert the VBA named parameters like "Type:=xlCellValue" to
VBScript positional ones: Use the Excel/VBA documentation
to determine the prototype of the FormatConditions.Add method;
if you are lucky the method's parameter list looks like

Add Type, Operator, Formula1, Formula2

then you can write

Selection.FormatConditions.Add xlCellValue, xlGreater, "50000"

otherwise you'll have to distribute your values to the correct
positions. E.g.

Add Operator, Formula2, Type, Formula1

==>

Selection.FormatConditions.Add xlGreater, , xlCellValue, "50000"

(use , , to skip (optional) parameters you can't/won't set)

 

[Highlander]

Highlander schrieb:








From your

Const xlDescending = 2
Const xlNormal = 1
Const xlCenter = -4108

code I gather, you'll have no problems to add the constants
for xlCellValue and xlGreater. That leaves you with the task to
convert the VBA named parameters like "Type:=xlCellValue" to
VBScript positional ones: Use the Excel/VBA documentation
to determine the prototype of the FormatConditions.Add method;
if you are lucky the method's parameter list looks like

Add Type, Operator, Formula1, Formula2

then you can write

Selection.FormatConditions.Add xlCellValue, xlGreater, "50000"

otherwise you'll have to distribute your values to the correct
positions. E.g.

Add Operator, Formula2, Type, Formula1

==>

Selection.FormatConditions.Add xlGreater, , xlCellValue, "50000"

(use , , to skip (optional) parameters you can't/won't set)- Hide quoted text -

- Show quoted text -

Okay I've added these Constants:

Const xlCellValue = 1
Const xlGreater = 5

And then the following code works:

'~~ Insert Bold and Red Font for any value greater than 50000 in
Column C
objExcel.Range("C:C").Select
objExcel.Selection.FormatConditions.Delete
objExcel.Selection.FormatConditions.Add xlCellValue, xlGreater,
"50000"
With objExcel.Selection.FormatConditions(1).Font
.Bold = True
.ColorIndex = 3 'Red
.Italic = False
End With

There is one problem though. Some of the cells in Column C contain
text, not a number, and the text is getting changed to Bold and Red
font. I only want the font to change for cells that contain a number.
How do I exclude the text cells from the conditional formatting?

Thanks in advance!

- Dave