'exporting to excel' Thread RSS Feed

exporting to excel

Mon, 14 Dec 2009 06:30:43 -0700

i'd like to export to excel and this is the code i have. Problem is i'd like the user to select location and name of document by themselves..

Dim xlApp As excel.Application
Dim xlWorkBook As excel.Workbook
Dim xlWorkSheet As excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim i As Integer
Dim j As Integer

xlApp = New excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")

For i = 0 To DataGridView1.RowCount - 2
For j = 0 To DataGridView1.ColumnCount - 1
xlWorkSheet.Cells(i + 1, j + 1) = _
DataGridView1(j, i).Value.ToString()
Next
Next

xlWorkSheet.SaveAs("C:\vbexcelreport.xlsx")
xlWorkBook.Close()
xlApp.Quit()

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

MsgBox("You can find the file C:\vbexcelreport.xlsx")

Re: exporting to excel

Mon, 14 Dec 2009 07:16:06 -0700

I am going to assume that code is working for you (I don't use built in libraries to make Excel documents, I use Excel 2003/2007's XML format)....

So if it is working, all you need is to know how to Select a file location for a user to save to... This is pretty easy with .Net:

                Dim SFD As SaveFileDialog
                SFD = New SaveFileDialog()
                SFD.InitialDirectory = Application.StartupPath
                SFD.Filter = "Text files (*.txt)|*.txt|All files (*.*)|*.*"
                SFD.CheckFileExists = True

                If SFD.ShowDialog() = Windows.Forms.DialogResult.OK Then
                    'The user clicked Open or Double Clicked the File
                    'Do what we need with the file now
                    Dim FileName As String = SFD.FileName
                    MessageBox.Show(FileName)
                End If

The last If statement is short hand for this:

Dim DR as DialogResult = SFD.ShowDialog()

If DR = Windows.Forms.DialogResult.OK Then '.....

hope this helps

Re: exporting to excel

Tue, 29 Dec 2009 05:43:16 -0700

thanks. but i just discovered that the header is not exported along.. i.e the column names are not exported.