I need a macro which will export data from tables

sharhari

New Member
Joined
Nov 28, 2013
Messages
12
....my actual requirement is...i have many tables in sql....i need to export all the data one shot in to excel......can u plz help me on this

i have a sample macro...but this throws object required error...need to know whether this code is correct or need some modification or need a new code...
Code:
Sub Rectangle1_Click()
'TRUSTED CONNECTION
    On Error GoTo errH

    Dim con As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strPath As String
    Dim intImportRow As Integer
    Dim strFirstName, strLastName As String

    Dim server, username, password, table, database As String


    With Sheets("Sheet1")

            server = .TextBox1.Text
            table = .TextBox4.Text
            database = .TextBox5.Text


            If con.State <> 1 Then

                con.Open "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";user id=username;password=password;"
                'con.Open

            End If
            'this is the TRUSTED connection string

            Set rs.ActiveConnection = con

            'delete all records first if checkbox checked
            If .CheckBox1 Then
                con.Execute "delete from tbl_demo"
            End If

            'set first row with records to import
            'you could also just loop thru a range if you want.
            intImportRow = 10

            Do Until .Cells(intImportRow, 1) = ""
                strFirstName = .Cells(intImportRow, 1)
                strLastName = .Cells(intImportRow, 2)

                'insert row into database
                con.Execute "insert into tbl_demo (firstname, lastname) values ('" & strFirstName & "', '" & strLastName & "')"

                intImportRow = intImportRow + 1
            Loop

            MsgBox "Done importing", vbInformation

            con.Close
            Set con = Nothing

    End With

Exit Sub
errH:
    MsgBox Err.Description
End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

sharhari

New Member
Joined
Nov 28, 2013
Messages
12
i modified the code......now im getting automation error.......
Sub DbConnection()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConn As String
strConn = "Driver={SQL Server};Server=; Database=; UID=; PWD="
cn.Open strConn
Dim queryArr, i
queryArr = Array("SELECT * FROM [xx]", "SELECT * FROM [xx]")
For i = LBound(queryArr) To UBound(queryArr)
ExecuteQuery queryArr(i), cn, rs
Next i
cn.Close
Set cn = Nothing
End Sub

Private Sub ExecuteQuery(query As Variant, ByRef cn As ADODB.Connection, ByRef rs As ADODB.Recordset)
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.Open CStr(query)
Sheets(1).Range("A1").CopyFromRecordset rs
.Close
End With
Set rs = Nothing
End Sub



In the line "Sheets(1).Range("A1").CopyFromRecordset rs" im getting Automation error
 

jsotola

Well-known Member
Joined
Nov 15, 2013
Messages
524

ADVERTISEMENT

try this

note: when you post code click "go advanced" then click # symbol on the toolbar

then paste your code between the CODE <c0de></c0de>tags


Code:
Sub DbConnection()

    Dim cn As Object
    Dim rs As Object
    
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")


    Dim strConn As String
    Dim query As String
    
    strConn = "Driver={SQL Server};Server=; Database=; UID=; PWD="
    cn.Open strConn
    
    query = "SELECT * FROM [xx];"
    rs.Open query, cn
    
    Sheets("Sheet1").Range("A1").CopyFromRecordset rs


    
    rs.Close
    cn.Close
    
    Set rs = Nothing
    Set cn = Nothing
    
End Sub
 
Last edited:

sharhari

New Member
Joined
Nov 28, 2013
Messages
12
im very sorry where should i click this "go advanced" then click # symbol on the toolbar
 

jsotola

Well-known Member
Joined
Nov 15, 2013
Messages
524

ADVERTISEMENT

when you reply to the post there are three buttons on lower right bottom of editing window [post quick reply] [go advanced] [cancel]

click the middle one [go advanced]

and you will get new window with three toolbars

middle toolbar third button from right
 

sharhari

New Member
Joined
Nov 28, 2013
Messages
12
tanks jsotola for ur detailed explanation......i tried ur code stil getting the automation error....below is the code i tried...

Code:
Sub DbConnection()
    Dim cn As Object
    Dim rs As Object
    
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    Dim strConn As String
    Dim query As String
    
    strConn = "Driver={SQL Server};Server=xx; Database=xx; UID=xx; PWD=xx"
    cn.Open strConn
    
    query = "SELECT * FROM [xx];"
    rs.Open query, cn
    
    Sheets("Sheet1").Range("A1").CopyFromRecordset rs

    
    rs.Close
    cn.Close
    
    Set rs = Nothing
    Set cn = Nothing
    
End Sub

Gave values for all the useridpassword server and table name and database.....i debugged it the error was in the line
Code:
Sheets("Sheet1").Range("A1").CopyFromRecordset rs
 

Watch MrExcel Video

Forum statistics

Threads
1,127,734
Messages
5,626,571
Members
416,192
Latest member
steinach

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top