View Single Post
  #1 (permalink)  
Old December 3rd, 2013, 11:01 AM
KLDeWitt87 KLDeWitt87 is offline
Registered User
Points: 19, Level: 1
Points: 19, Level: 1 Points: 19, Level: 1 Points: 19, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2013
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Post Export SQL database to Excel in VB 2012/VS 2012

I am hoping I put this thread in the right place. I'm new to these forums so please correct me if this is in the wrong location and I will move the thread or delete it.

I am trying to find a way to export an entire database to Excel using a query. I've searched Google endlessly trying to find a solution. It seems like all the queries just get the data from a table in the database as opposed to the entire database. I am hoping to find a way to export the entire database instead of getting data from one table in the database at a time, like most of the queries I found in my search seem to do.

Thanks for your time and help,
Kat

P.S. Here is what I have so far:

Code:
server = Me.cboSQL.Text

        If chkTrust.Checked Then
            strConnect = "Data Source=" & server & ";Trusted_Connection=True;Initial Catalog=" & cboCatalog.Text & ""
        Else
            strConnect = "Data Source=" & server & ";User ID=" & userText.Text & ";Password=" & userPass.Text & ";Initial Catalog=" & cboCatalog.Text & ""
        End If
        Using exportConn As New SqlConnection(strConnect)
            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


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

            Dim sqlExport As String = ""  <---query is supposed to go here
            'Dim sqlExport2 As String = "Select * from Invoice_DT"

            Dim dscmd As New SqlDataAdapter(sqlExport, exportConn)
            'Dim dscmd2 As New SqlDataAdapter(sqlExport2, exportConn)
            Dim ds As New DataSet
            'Dim dt As New DataTable


            dscmd.Fill(ds)
            xlWorkSheet.Cells(1, 1) = ""  <---column headers go here
            xlWorkSheet.Cells(1, 2) = ""   <---and here
            For i = 0 To ds.Tables(0).Rows.Count - 1
                For j = 0 To ds.Tables(0).Columns.Count - 1
                    xlWorkSheet.Cells(i + 3, j + 1) = _
                        ds.Tables(0).Rows(i).Item(j)
                Next
            Next
            
            xlWorkSheet.SaveAs("C:\TestExcel.xlsx")
            xlWorkBook.Close()
            xlApp.Quit()
            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
            MsgBox("You can find the file C:\TestExcel.xlsx")
        End Using
ExportDBError:
        If Err.Number <> 0 Then
            MsgBox(Err.Source & "-->" & Err.Description, , "Error")
        End If

Last edited by KLDeWitt87; December 3rd, 2013 at 11:05 AM. Reason: added code
Reply With Quote