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:

jsotola

Well-known Member
Joined
Nov 15, 2013
Messages
524
i cleaned it up and i'll have a closer look at it tomorrow (it's getting late)

note: the source:= array contains three strings separated by commas (second starts at Packet size)

i highlighted them , in case you want to experiment with deleting unnecessary commands

check here http://www.connectionstrings.com/


Code:
Sub Macro3()

    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;"[SIZE=5][B],[/B][/SIZE] _
                              "Packet Size=4096;" & _
                              "Workstation ID=PC163221;" & _
                              "Use Encryption for Data=False;" & _
                              "Tag with column collation when possible=False;"[B][SIZE=5],[/SIZE][/B] _
                              "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
 
Last edited:

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

jsotola

Well-known Member
Joined
Nov 15, 2013
Messages
524
nothing much found

maybe one of these connection strings may work

"Provider=SQLOLEDB;Data Source=[You DB Name];Trusted_connection=yes;"

"Provider=SQLOLEDB;Data Source=ServerIPOrName;Initial Catalog=DatabaseName;Trusted_connection=yes;"
 

Watch MrExcel Video

Forum statistics

Threads
1,127,582
Messages
5,625,620
Members
416,124
Latest member
DeMoNloK

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