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
Export SQL database to Excel in VB 2012/VS 2012
