troubleshooting Question

VB.NET - Export to Excel in TEXT Format

Avatar of Wilder1626
Wilder1626Flag for Canada asked on
Visual Basic.NET
2 Comments1 Solution8 ViewsLast Modified:
Hi.

I'm new to VB.Net but i'm trying to adjust myself compare to VB6.

I have below code that i'm using to export to excel what i have in my DataGridView1.
What i have in my DataGrid:

In my column ID, as you can see, I'm having leading zeros.

But when I export to excel, I'm loosing them.

How can i export to excel as Text format all columns so that i can keep leading zeros.

At the same time, i would like to change the sheet name from "Sheet1" to "Data".

Thanks for your help.

    Private Sub btnImport_Click(sender As Object, e As EventArgs) Handles btnImport.Click
        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer
        Dim Handler As MessageBox

        xlApp = New Microsoft.Office.Interop.Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        For i = 0 To DataGridView1.RowCount - 2
            For j = 0 To DataGridView1.ColumnCount - 1
                For k As Integer = 1 To DataGridView1.Columns.Count
                    xlWorkSheet.Cells(1, k) = DataGridView1.Columns(k - 1).HeaderText
                    xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()
                Next
            Next
        Next
        On Error GoTo Handler
        xlWorkSheet.SaveAs("C:\Users\" & strUserLabel.Text & "\Desktop\vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        MsgBox("You can find the file C:\Users\" & strUserLabel.Text & "\Desktop\vbexcel.xlsx")
        Exit Sub
Handler:
        MsgBox("The Excel file his openned. Please close it and then resume.")
    End Sub

Open in new window


ASKER CERTIFIED SOLUTION
Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 2 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 2 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004