?
Solved

Need to programmatically set Excel cell format to text

Posted on 2011-10-13
5
Medium Priority
?
2,790 Views
Last Modified: 2012-05-12
I am writing data into Excel from vb.net and need to set some cell formats to text, to ensure that leading zeroes are not lost. So far, I have tried adding a leading apostrophe to the string I write into the cell, setting the cell's numberformat to "Text" and setting it to "000000". None of these work.

There must be a magic solution??
0
Comment
Question by:christinaemmm
  • 2
  • 2
5 Comments
 
LVL 5

Accepted Solution

by:
slycoder earned 1000 total points
ID: 36963677
Both of these should work:

    ' uses apostrophe
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "'000000"

    ' uses text formatting
    Range("A2").Select
    Selection.NumberFormat = "@"
    ActiveCell.FormulaR1C1 = "000000"

Hope this helps.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36963724
what do you mean by You tried an appostroph and 00000 formating and it didn't work what number you have at first and what did you get can you post some examples as what you indicate should work with a slight change
gowflow
0
 

Author Comment

by:christinaemmm
ID: 36966880
I suddenly wondered whether it was because I save the ss as a csv file; when I tested, I found that the control settings for some of the values were not being passed correctly. So mea culpa, having fixed my code, it does work, including saving the ss as a csv file.

slycoder, I didn't know about using @ - is there any practical difference between the options of this or inserting an apostrophe? I have now tried both and they both seem fine, but it would be good to know if there are any little 'gotcha's with either solution.
0
 

Author Closing Comment

by:christinaemmm
ID: 36966884
Thanks, slycoder!
0
 
LVL 5

Expert Comment

by:slycoder
ID: 36969351
There are no 'gotchas' that I know of with using either method.

the apostrophe is a "literal" mark and has been used since the days of VisiCalc

the @ formatting is what I got when I recorded a macro and changed the formatting of the cell to Text.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question