Changing Cell-formating in Excel via VBA

Gert · November 11th, 2003, 10:08 AM

Hi

I am creating an Excel-Worksheet threw VBA, but my problem is that 4 of the columns need to be formated as Text, but they end up as General. Does anyone know how to format all the cells in a column via an Excel Worksheet object?

Thanks in advance.

Gert

pgtips · November 11th, 2003, 11:08 AM

Hi Gert,

The way I find out things like this is to go into Excel, record a new Macro, then do whatever it is I want to find out about (in this case format a column to text). Then I open up the Excel VB Editor and look at the VBA in the macro I just recorded.

In this case you get:
Columns("B:B").Select
Selection.NumberFormat = "@"

which can be condensed to
Columns("B:B").NumberFormat = "@"

hth
Phil

pgtips · November 11th, 2003, 11:25 AM

Gert, I should have added that "Columns" is a property of the Worksheet object, so you would probably need:
worksheetVariableName.Columns("B:B").NumberFormat = "@"

Additionally, you can refer to the column by number (starting at 1) instead of letter.

rgds
Phil

Gert · November 11th, 2003, 06:20 PM

Thank you Phil. I haven't had time to try it out yet, but it looks like a good sugestion. I thanks for the Macro-tip.

Gert

vbnetgeek · May 10th, 2005, 01:56 PM

I have same problem, but I want to know how to change from text format to number format.

I am generating a report with vb .net in a .xls sheet but my numbers are stored as text. how can I fix this?

The number of hours is saved as text and not as number. Any ideas?
[vb]

Code:

...
While i < dsetTask.Tables("tblTask").Rows.Count
            drowData = Data.NewRow()
            drowData("Name") = dsetTask.Tables("tblTask").Rows(i)("Name")
            drowData("Hour") = dsetTask.Tables("tblTask").Rows(i)("Hour")
End While
...

[/vb]

any ideas?