troubleshooting Question

VB.NET - Export to Excel in TEXT Format

Avatar of Wilder1626
Wilder1626Flag for Canada asked on
Visual Basic.NET
2 Comments1 Solution7 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

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros