Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 2012 > Visual Basic 2012 General Discussion
| Search | Today's Posts | Mark Forums Read
Visual Basic 2012 General Discussion For any discussions about Visual Basic 2012topics which aren't related to a specific Wrox book
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Visual Basic 2012 General Discussion section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old December 3rd, 2013, 11:01 AM
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
  #2 (permalink)  
Old December 3rd, 2013, 12:21 PM
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
Default 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! :)
  #3 (permalink)  
Old December 4th, 2013, 05:00 PM
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
Default Delete

Can an administrator delete this please?

Thank you!


Similar Threads
Thread Thread Starter Forum Replies Last Post
vb 2012 access napatel2007 Visual Basic 2012 General Discussion 0 May 30th, 2013 10:10 PM
Force SQL Server 2012 to Use Visual Studio 2012 Titera BOOK: Professional Microsoft SQL Server 2012 Administration 1 May 6th, 2013 02:22 PM
Completing Lessons in SQL 2012 SSDT ITGuy268 BOOK: Knight's Microsoft Business Intelligence 24-Hour Trainer 0 May 2nd, 2013 11:48 AM
Welcome to Beginning SQL Server 2012 Programming! patkinson BOOK: Beginning Microsoft SQL Server 2012 Programming 2 November 27th, 2012 06:37 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.