Format String

[matrixindicator]

xlWorkSheet.Cells(iRow, iCol + 3).NumberFormat = "@"
xlWorkSheet.Cells(iRow, iCol + 3) = Format(rst1.Fields(iCol - 1).Name)

I have an export from access to excel along vba. I have a little format problem.

A value like "01-04" should be seen as a string. However when writing to excel. Excel sees this as a date and write januari-4.

I tried something with numberformat, but this is not working. What am I missing ?

 

[AnotherHiggins]

The field should be considered a string, not a number. Make sure the field is stored as TEXT in access before exporting.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

what is the data defined as in the database ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[RivetHed]

And if Excel still tries to be "helpful" add a leading apostrophe to the value when you export from access to force Excel to treat as text.

 

[PHV]

You really have a column named 01-04 ?

Anyway you may try to prepend a single quote at front of the value.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WinblowsME]

Try

xlWorkSheet.Cells(iRow, iCol + 3).NumberFormat = "@"
xlWorkSheet.Cells(iRow, iCol + 3) = [COLOR=red]CStr[/color](rst1.Fields(iCol - 1).Name)

 

[SkipVought]

"A value like "01-04" should be seen as a string. However when writing to excel. Excel sees this as a date and write januari-4."

Excel is just trying to be helpful. Read the link, regarding how Excel interprets data that is entered, like 01-04.

faq68-5827


Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[matrixindicator]

just to mention "01-04" is a column name, not a value of a field.

 

[matrixindicator]

PHV,

I do not have a column named 01-04.
Its the column head of a crosstab query.

 

[matrixindicator]

 xlWorkSheet.Cells(iRow, iCol + 3).NumberFormat = "@"
xlWorkSheet.Cells(iRow, iCol + 3) = "'" & rst1.Fields(iCol - 1).Name

as suggested "'" works !