Import Data from SQL server to excel

abhay_547 · May 18th, 2010, 12:09 AM

Hi,

I have recorded below macro which works fine to extract data from SQL server but If I have more then 65536 rows of data in my sql table for the criteria which i entered in the below macro but still the macro exports only 65536 rows of data and then stops. ideally it should populate rest of the data in a new worksheet.

Can we modify the below code in such a way that it imports the rest of the data to new worksheet if the number of records exceed 65536.

Code:

Sub Extractdata()

'
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DRIVER=SQL Native Client;SERVER=XXXXXX;UID=admin;PWD=****;APP=Microsoft Office XP;WSID=XXXXXXX" _
        ), Array(";DATABASE=meta_data;")), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT mydata.CAC, mydata.Year, mydata.""Cost Element"", mydata.""Cost Element Name"", mydata.Name, mydata.""Cost Center"", mydata.""Company Code"", mydata.""Unique Indentifier 1"", ""Cost Center mapping"".""Produ" _
        , _
        "ct UBR Code"", ""Cost Element Mapping"".FSI_LINE2_code" & Chr(13) & "" & Chr(10) & "FROM sap_data.dbo.""Cost Center mapping"" ""Cost Center mapping"", sap_data.dbo.""Cost Element Mapping"" ""Cost Element Mapping"", sap_data.dbo.mydata myda" _
        , _
        "ta" & Chr(13) & "" & Chr(10) & "WHERE mydata.""Unique Indentifier 1"" = ""Cost Element Mapping"".CE_SR_NO AND mydata.""Cost Center"" = ""Cost Center mapping"".""Cost Center"" AND ((""Cost Center mapping"".""Product UBR Code""='G_0768') AND (""" _
        , "Cost Element Mapping"".FSI_LINE2_code='F1547000000'))")
        .Name = "Query from mydatanew"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Thanks for your help in advance.