Format all columns in Excel as Text

Question

I am developing a very basic application on VB.NET.

Function: To import a delimited TXT file and perform "TextToColumn", Column-AutoFit, format all columns as TEXT and finally save as Excel.

Challenge I am facing: I am able to code for everything except all columns are not formatted as TEXT. Due to this, a column containing Numbers of 16 digits are saved in Scientific Notation.

Code:

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.IO
Public Class Form1
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Dim style As Microsoft.Office.Interop.Excel.Style
    Dim strFileName As String
    Dim pathFile As String
    Dim FileNameOnly As String
    Dim saveAsPath As String
    Dim delimiterType As String
    Dim fd As OpenFileDialog = New OpenFileDialog()
    Public Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Button2.Text = "Browse A Delimited File"
        Button1.Text = "Format and Save In Excel"
        Me.Text = "Delimiter To Excel - DEMO v1"
    End Sub

    Public Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try
            If RadioButton1.Checked = True Then
                delimiterType = RadioButton1.Text
            Else
                delimiterType = RadioButton2.Text
            End If
            xlWorkBook = xlApp.Workbooks.Open(strFileName)
            xlWorkSheet = xlWorkBook.Sheets(1)
            xlApp.Visible = False

            With xlWorkSheet
                .Columns(1).TextToColumns( _
                Destination:=.Cells(1, 1), _
                DataType:=Excel.XlTextParsingType.xlDelimited, _
                ConsecutiveDelimiter:=False, _
                TAB:=False, _
                Semicolon:=False, _
                Comma:=False, _
                Space:=False, _
                Other:=True, _
                OtherChar:=delimiterType, _
                FieldInfo:= <What_to_Fill_Here_to_Format_Every_Column_as_Text>
                TrailingMinusNumbers:=False)
            End With
        Catch ex As Exception
            MsgBox("Something is Wrong")
        End Try
        pathFile = Path.GetDirectoryName(fd.FileName)
        FileNameOnly = System.IO.Path.GetFileNameWithoutExtension(fd.FileName)
        saveAsPath = pathFile + "\" + FileNameOnly + ".xls"
        xlWorkBook.SaveAs(Filename:=saveAsPath, _
                          FileFormat:=39, _
                          ReadOnlyRecommended:=False _
                          )
        xlWorkBook.Close()
    End Sub

    Public Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        fd.Title = "Open File Dialog"
        fd.InitialDirectory = "C:\"
        fd.Filter = "All files (*.*)|*.*|All files (*.*)|*.*"
        fd.FilterIndex = 2
        fd.RestoreDirectory = True
        fd.Multiselect = False

        If fd.ShowDialog() = DialogResult.OK Then
            strFileName = fd.FileName
        End If
    End Sub
End Class

I have left FIELDINFO blank because I assume this field formats any column as TEXT, GENERAL, etc. And I don't know the values that must be assigned to this to format all columns as TEXT.

Please help me.

Answer 1

Somewhere in your code, prior to loading values into the cells, run this:

Cells.NumberFormat = "@"

this will format all the cells in the worksheet to Text.

Answer 2

After this line of your code,

   xlWorkSheet = xlWorkBook.Sheets(1)

insert this line:

  xlworksheet.Range("A:Z").NumberFormat = "@"

It will change any number formatted data in your Excel to text.

The range depends on the number of columns of data you are exporting. For example, instead of ("A:Z") it can be ("A:AB") or however many you need..