format excel as text

[xxwhocaresxx]

I am having problems outputing to my excel spreadsheet. I need the entire sheet to all formatted as text. It is messing up my account numbers when formatted as a number.

This is what my code looks like, but it doesn't seem to like it.

Dim MyExcel As New Excel.Application
        Dim oWorkbooks As Excel.Workbooks = MyExcel.Workbooks
        Dim theWorkbook As Excel.Workbook = oWorkbooks.Add
        Dim oSheet As Excel.Worksheet
        Dim rng As Excel.Range
        Dim exFile As String
        Dim i As Integer
        Dim c As Integer

This is where I am trying to format as text:

oSheet.Range("A2:Z999").Select()
            oSheet.Selection.NumberFormat = "@"

Thanks in advance!

[Br1an_g]

oSheet.Range("A2:Z999").Select 'you dont need parenthesis after the select
            oSheet.Selection.NumberFormat = "@"

[Webtest]

Did the post by Brian_g resolve your problem?

What exactly do you mean by "messing up my account numbers"? That doesn't convey enough information.

[berkalex]

This

The problem occurs when the format is changed after the data has already been added. Sometimes the data will automatically update to the new format, and sometimes not (I'm sure there's a reason - I'm just going off of my experience). To resolve this, you can do one of two things.

1. You can set the format to text before adding the data. A shortened version of the code you had is:

oSheet.Range("A2:Z999").NumberFormat = "@"
(you don't need to Select it.)

2. Set the format after adding the data, then update the contents of each cell. Excel then recognizes the new format. Here's an example:

Dim c as Excel.Range
Dim r as Excel.Range
Dim txt as String

Set r = oSheet.Range("A2:Z999")
rng.NumberFormat = "@"
For Each c in r
txt = c.Text
c.Text = txt
Next

Set c = Nothing
Set r = Nothing

If you have any data with leading 0's, then you will definitely want to set the format before adding the data to the cells.