Jehzalahman
New Member
- Joined
- Aug 30, 2006
- Messages
- 9
I am using some code I picked up off a website to extract some data from a SQL table into an excel sheet.
It is getting the first four columns fine, but only returning each 512th entry from column 5 (i.e. entry 512, entry 1024 etc.). I have tried it both as a string query and a stored procedure with the same results. Any ideas?
Sub DataExtract()
' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection
Dim SQLStr As String
' Provide the connection string.
Dim strConn As String
strConn = "Driver={SQL Server};Server=[server];Database=[db];Uid=[ID];Pwd=[PW];"
'Now open the connection.
cnPubs.Open strConn
'Create SQL query
SQLStr = ""
SQLStr = SQLStr & "Select Col1,Col2,Col3,Col4,Col5"
SQLStr = SQLStr & " FROM [table1]"
SQLStr = SQLStr & " WHERE [field]=1 and Col2 not in (select email from [table2])"
' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
With rsPubs
' Assign the Connection object.
.ActiveConnection = cnPubs
' Extract the required records.
.Open SQLStr
' Copy the records into cell A1 on Sheet1.
Sheets("SQL Returns").Range("A1").CopyFromRecordset rsPubs
' Tidy up
.Close
End With
cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
End Sub
It is getting the first four columns fine, but only returning each 512th entry from column 5 (i.e. entry 512, entry 1024 etc.). I have tried it both as a string query and a stored procedure with the same results. Any ideas?
Sub DataExtract()
' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection
Dim SQLStr As String
' Provide the connection string.
Dim strConn As String
strConn = "Driver={SQL Server};Server=[server];Database=[db];Uid=[ID];Pwd=[PW];"
'Now open the connection.
cnPubs.Open strConn
'Create SQL query
SQLStr = ""
SQLStr = SQLStr & "Select Col1,Col2,Col3,Col4,Col5"
SQLStr = SQLStr & " FROM [table1]"
SQLStr = SQLStr & " WHERE [field]=1 and Col2 not in (select email from [table2])"
' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
With rsPubs
' Assign the Connection object.
.ActiveConnection = cnPubs
' Extract the required records.
.Open SQLStr
' Copy the records into cell A1 on Sheet1.
Sheets("SQL Returns").Range("A1").CopyFromRecordset rsPubs
' Tidy up
.Close
End With
cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
End Sub