VB.Net: Writing To Excel Workbook as Text

Question

I have written the following function...

Public Function writeAUTLSheet(doc As XDocument, myNameSpaces As Dictionary(Of String, XNamespace), theSheet As Excel.Worksheet)
    'Grab the number of the last row to not overwrite 
    Dim rowNumber As Integer = getLastRow(theSheet)

    For Each myName As XElement In doc.Descendants.Elements(myNameSpaces("ns4") + "autl")
        theSheet.Cells(rowNumber, 1) = doc.Descendants.Elements(myNameSpaces("ns") + "number").Value
        theSheet.Cells(rowNumber, 2) = myName.Descendants(myNameSpaces("ns") + "id").Value
        theSheet.Cells(rowNumber, 3) = myName.Descendants(myNameSpaces("ns") + "name").Value
        rowNumber = rowNumber + 1
    Next

End Function

It runs as expected, but some values are written as "General" and others are written as "Date". This leads to values such as 07-2-3018 being turned into 7/2/3018. Then if I change that cell to "Text" or "General"(manually) it turns into "408525".

Is there a way to specify that I want it to be written as text to achieve 07-2-3-18 being written?

Answer 1

When you put data that might be a string, or might be a date, Excel does wacky things with it, as you've observed, because it has no way of knowing whether the data you've provided should be one type or another.

Usually, anything that can be interpreted as a Date type, is interpreted as such. So, 1/1/2017 would be a valid date but 13/47/5047 cannot be, so Excel will treat the latter as a string literal, the former as a Date type. If a value is considered a Date, then it is also a Long numeric. The 408525 value is the long numeric representation of the Date value 7/2/3018.

There should be a NumberFormat property of your Cells object (which is an Excel.Range type), but the NumberFormat doesn't change the underlying value, and it's the interpretation of the value which is confusing Excel and causing it to represent date-like values as Dates.

You can always prepend the cell value with an apostrophe, which will force Excel to interpret as string, e.g.:

theSheet.Cells(rowNumber, 2) = "'" + myName.Descendants(myNameSpaces("ns") + "id").Value