Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 2012 > Visual Basic 2012 General Discussion
Password Reminder
Register
Register | FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old December 4th, 2013, 11:17 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
Default Exporting SQL Tables to Excel

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
Reply With Quote
  #2 (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!
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Exporting related tables to excel crisan Access 10 July 24th, 2012 05:23 PM
STORING IMAGES & EXPORTING SQLSERVER TABLES harsh_hot MySQL 2 September 8th, 2005 03:10 AM
Exporting all tables to text Mitch SQL Server 2000 0 September 12th, 2003 03:38 PM



All times are GMT -4. The time now is 04:08 PM.


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