I need a macro which will export data from tables

[sharhari]

....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...

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

 

[sharhari]

not fully but i know a little bit of it........

 

[jsotola]

ok, go to your code

doubleclick rs in rs.Close to select it (highlight) (any rs would work)

right-click on the highlighted rs .... you will get a popup menu (context menu)

find "Add Watch" and click it ( fourth from bottom on my computer )

you will get another dialog (add watch) ...... just click OK

a "watches" panel should appear in your code editor

there should be rs in the watches panel

now click anywhere in your code ( anywhere between Sub DbConnection() and End Sub

then hit F8

you will get a yellow line cursor in the code window

the yellow line is the command that will execute when you hit F8 next

this allows you to step through the code and see the value of the "watched" variable change

just click F8 until the code code fails

if the error dialog has a "debug" button, then press it

get back to me

 

[sharhari]

I did all, but the final stage "if the error dialog has a "debug" button, then press it" im not able to do it....in the watch window this is what appears

Watch : : rs : <Out of context> : Empty : Sheet1.DbConnection

 

[jsotola]

the recordset has no data

what version of excel do you have?

 

[sharhari]

Watch : : rs : <Out of context> : Empty : Sheet1.DbConnection

 

[sharhari]

2010

 

[sharhari]

Expression Value Type Context
Watch : : rs : < Out of context > : Empty : Sheet1.DbConnection

 

[jsotola]

good, me too

start macro recording (little blue icon all way on bottom left )

go to the main excel window / click data tab / get external data / from other sources / from sql server

do the connection to your sql server

 

[sharhari]

i did that im getting the below code

Sub Macro3()
'
' Macro3 Macro
'
'
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=SQLOLEDB.1;Persist Security Info=True;User ID=xx;Data Source=yy;Use Procedure for Prepare=1;Auto " _
        , _
        "Translate=True;Packet Size=4096;Workstation ID=PC163221;Use Encryption for Data=False;Tag with column collation when possible=Fa" _
        , "lse;Initial Catalog=zz"), Destination:=Range( _
        "$A$1")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array( _
        """zz"".""aa"".""bb""")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = _
        "C:\Users\247830\Documents\My Data Sources\yy zz aa bb.odc"
        .ListObject.DisplayName = _
        "Table_yy zz aa bb_"
        .Refresh BackgroundQuery:=False
    End With
    Range("A2").Select
    Sheets("Sheet1").Select
End Sub

 

[sharhari]

this code is fine for one column....but i need a code where we enter all the table names in a place "Text box " or hardcoded ....and run the macro, it should export all the details from all the tables one shot........