I'm here to pester all of you again. ><
I managed to get one table to export to Excel with some adjustments and had no problems. However, there's more than one table in the database and I want to export every table(I know it has to be done one table at a time) without the file being overwritten with the data from the second table I'm trying to export.
I will attach the code I have so far.
Thanks in advance,
Kat
Code:
If checked = True Then
strConnect = "Data Source=" & server & ";Trusted_Connection=True;Initial Catalog=" & catalog & ""
Else
strConnect = "Data Source=" & server & ";User ID=" & userText & ";Password=" & userPass & ";Initial Catalog=" & catalog & ""
End If
Using exportConn As New SqlConnection(strConnect)
Dim xlApp As New Microsoft.Office.Interop.Excel.Application
Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Add
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 = "Select * From" & " " & txtTblExp.Text & ""
'Dim sqlExport As String = "Insert Into OPENROWSET('Microsoft.Jet.OleDb.4.0','Excel 8.0;Database=C:\TestExcel.xlsx;','Select * from [Sheet1$]') select * from" & " " & txtTblExp.Text & " "
Dim dscmd As New SqlDataAdapter(sqlExport, exportConn)
Dim ds As New DataSet
exportConn.Open()
dscmd.Fill(ds)
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)
xlWorkSheet.Cells(i + 1, j + 1) = _
ds.Tables(0).Columns(i).ColumnName.ToString
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