p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Visual Basic 2012 General Discussion (http://p2p.wrox.com/forumdisplay.php?f=693)
-   -   Export SQL database to Excel in VB 2012/VS 2012 (http://p2p.wrox.com/showthread.php?t=91605)

KLDeWitt87 December 3rd, 2013 11:01 AM

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,

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


server = Me.cboSQL.Text

        If chkTrust.Checked Then
            strConnect = "Data Source=" & server & ";Trusted_Connection=True;Initial Catalog=" & cboCatalog.Text & ""
            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

            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) = _
            MsgBox("You can find the file C:\TestExcel.xlsx")
        End Using
        If Err.Number <> 0 Then
            MsgBox(Err.Source & "-->" & Err.Description, , "Error")
        End If

KLDeWitt87 December 3rd, 2013 12:21 PM

Ignore this thread
Never mind, I just realized it will have to be done table by table.

Sorry for the unnecessary question.

Hope everyone has a great day! :)

KLDeWitt87 December 4th, 2013 05:00 PM

Can an administrator delete this please?

Thank you!

All times are GMT -4. The time now is 02:02 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.