Programmatically reading from Excel and formatting
 

I am doing a small integration between two systems. One system exports
data (I have no control over this) to Excel and I am reading from the
Excel file to use the data elsewhere.

Everything is fine except for one problem: it is possible that I will
have a value for a cell such as "0000". This is intended to be a
string (eventually, when I import it). It shows up in the Excel
interface as "0000", but whenever I read from the cell it comes back
as "0". I guess Excel assumes it is a number.

Is there a way around this where I can get the value of a cell via
code (I am using C# for reference) as is (eg "0000", not "0")?

I am sorry if this has been previously asked, I was having a hard time
getting accurate search results for this topic.

Many thanks.

Programmatically reading from Excel and formatting
 

Hi,

I know solution when importing to excel by hand. It is possible to
state particular column as text and then there is no problem when
processing '0000'.

If yo don't have any control over importing but you are sure about
format of that column (namely need 4 letter text but number inside)
you can use function TEXT(value,format_text).

TEXT(0,"0000")="0000"
TEXT(11,"0000")="0011"
etc.

other cases are more difficult.

let me know if it helps

riso



On Mar 13, 7:43 pm, "MJ" wrote:
I am doing a small integration between two systems. One system exports
data (I have no control over this) to Excel and I am reading from the
Excel file to use the data elsewhere.

Everything is fine except for one problem: it is possible that I will
have a value for a cell such as "0000". This is intended to be a
string (eventually, when I import it). It shows up in the Excel
interface as "0000", but whenever I read from the cell it comes back
as "0". I guess Excel assumes it is a number.

Is there a way around this where I can get the value of a cell via
code (I am using C# for reference) as is (eg "0000", not "0")?

I am sorry if this has been previously asked, I was having a hard time
getting accurate search results for this topic.

Many thanks.

Programmatically reading from Excel and formatting
 

If the program that populates the cell with 0000 is doing something like this:

Dim myCell as range
set mycell = somecellsomewhere
mycell.value = "0000"

Then excel will see the value as a number 0--just like if you typed in 0000.

Maybe you could go back to that programmer and make sure that he or she
populates it as a string:

Dim myCell as range
set mycell = somecellsomewhere
mycell.numberformat = "@" 'text
mycell.value = "0000"
'or
mycell.value = "'0000"

=====
If you know any value you pick up from excel is supposed to be a string 4 digits
long, couldn't you do the formatting in C#?

====
One more thought...

If the other program does something like:

Dim myCell as range
set mycell = somecellsomewhere
mycell.numberformat = "0000"
mycell.value = 0

Then that value is 0, but the display will show 0000.

Maybe you can pick up what shows in the cell by using .text instead of .value

In VBA, it would be:

msgbox mycell.text 'not mycell.value



MJ wrote:

I am doing a small integration between two systems. One system exports
data (I have no control over this) to Excel and I am reading from the
Excel file to use the data elsewhere.

Everything is fine except for one problem: it is possible that I will
have a value for a cell such as "0000". This is intended to be a
string (eventually, when I import it). It shows up in the Excel
interface as "0000", but whenever I read from the cell it comes back
as "0". I guess Excel assumes it is a number.

Is there a way around this where I can get the value of a cell via
code (I am using C# for reference) as is (eg "0000", not "0")?

I am sorry if this has been previously asked, I was having a hard time
getting accurate search results for this topic.

Many thanks.

--

Dave Peterson