Import Data from SQL server to excel

[abhay_547]

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.

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.