How to Save excel file in vb.net

Question

Previously , I was trying to export gridview value into excel. but with the code given below i am able to export data into excel. but still not able to Save Automatically that excel file into a fixed folder suppose in C:/ drive. The code which i have written to export into excel is given below.

Private Sub ButtonExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)   Handles ButtonExport.Click
Dim rowsTotal, colsTotal As Short
Dim I, j, iC As Short
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
Dim xlApp As New Excel.Application
Try
    Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
    Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
    xlApp.Visible = True
    rowsTotal = DataGridView1.RowCount - 1
    colsTotal = DataGridView1.Columns.Count - 1
    With excelWorksheet
        .Cells.Select()
        .Cells.Delete()
        For iC = 0 To colsTotal
            .Cells(1, iC + 1).Value = DataGridView1.Columns(iC).HeaderText
        Next
        For I = 0 To rowsTotal - 1
            For j = 0 To colsTotal
                .Cells(I + 2, j + 1).value = DataGrid1.Rows(I).Cells(j).Value
            Next j
        Next I
        .Rows("1:1").Font.FontStyle = "Bold"
        .Rows("1:1").Font.Size = 10
        .Cells.Columns.AutoFit()
        .Cells.Select()
        .Cells.EntireColumn.AutoFit()
        .Cells(1, 1).Select()
    End With
Catch ex As Exception
    MsgBox("Export Excel Error " & ex.Message)
Finally
    'RELEASE ALLOACTED RESOURCES
    System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
    xlApp = Nothing
End Try
End Sub

can anybody over here please help me out from this problem that how to save that excel file automatically in VB.NET??

yes i can save it externally by save as option of excel. but i want to save it automatically after completing the export. — SoumitaP
– SoumitaP, Commented May 14, 2013 at 6:25
i am not getting that code. where to write? Me.FileFormat, Me.SaveAs and Me.Path is showing error — SoumitaP
– SoumitaP, Commented May 14, 2013 at 6:31

Adrien Lacroix · Accepted Answer · 2013-05-14 06:40:20Z

5

The SaveAs method is defined for Excel.Workbook

At the end of your Try, just before the Catch, write :

excelBook.SaveAs(<some path here>, etc...)

Refers to here for more informations.

And to exit properly Excel, write in your Finally block at the start :

xlApp.Workbooks.Close()
xlApp.Quit()

answered May 14, 2013 at 6:40

Adrien Lacroix

3,6221 gold badge22 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

SoumitaP Over a year ago

"Export excel error the file could not be accessed. Try one of the following :

SoumitaP Over a year ago

after completing into export into excel file. i have written below code in the try block.please check it.excelBook.SaveAs("c:/excelfile.xlsx", Excel.XlFileFormat.xlXMLSpreadsheet, AccessMode:=Excel.XlSaveAsAccessMode.xlNoChange)

Adrien Lacroix Over a year ago

Try "c:\excelfile.xlsx". And change your to Excel.XlFileFormat.xlXMLSpreadsheet to Excel.XlFileFormat.xlExcel12 if you want an xlsx

SoumitaP Over a year ago

now it is saving the file but i am not open the file. showing error that " excel cannot open the file "name.xlsx" because the file format or the file extension is not valid. verify that the file has not been corrupt and that the file extension matches the format of the file"

Adrien Lacroix Over a year ago

Maybe just try excelBook.SaveAs("C:\excelfile.xlsx")

|

user3612833 · Accepted Answer · 2014-05-07 15:25:44Z

0

i just used :

    Dim oexcel As Object
    Dim obook As Object
    Dim owrite As New Microsoft.Office.Interop.Excel.Worksheet

    < your code > 

   owrite.SaveAs("c:\" & foldername)

answered May 7, 2014 at 15:25

user3612833

1

Comments

marcus · Accepted Answer · 2017-01-09 07:51:12Z

0

This is an older question but maybe this'll help someone still. I recently needed to read, parse and write xlsx files. I used the OpenXML SDK with C# for this purpose. MSDN provides some good tutorials on how to do this. If anyone has questions I can provide my code. Just one last note...when I "published" the app an installation of the SDK on the client's computer seemed to be required.

answered Jan 9, 2017 at 7:51

marcus

1381 gold badge1 silver badge8 bronze badges

Collectives™ on Stack Overflow

How to Save excel file in vb.net

3 Answers 3

16 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

16 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related