hey all,

what's the best way to take all the records from 2 fields in a database and
populate 2 specific columns in an excel spreadsheet?

thanks,
rodchar

ADO? An example using Access

Sub GetData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "SELECT LastName, FirstName From Contacts"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0)
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing
End Sub


You can get connection strings from
http://www.carlprothman.net/Default.aspx?tabid=81

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"rodchar" wrote in message
...
hey all,

what's the best way to take all the records from 2 fields in a database
and
populate 2 specific columns in an excel spreadsheet?

thanks,
rodchar

But then how does it get to Excel (the 2 specific columns)?

"Bob Phillips" wrote:

ADO? An example using Access

Sub GetData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "SELECT LastName, FirstName From Contacts"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0)
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing
End Sub


You can get connection strings from
http://www.carlprothman.net/Default.aspx?tabid=81

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"rodchar" wrote in message
...
hey all,

what's the best way to take all the records from 2 fields in a database
and
populate 2 specific columns in an excel spreadsheet?

thanks,
rodchar

This bit is getting the data into an array

If Not oRS.EOF Then
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0)
Else
MsgBox "No records returned.", vbCritical
End If

you can just dump it into a range

If Not oRS.EOF Then
Range("M1:N1").CopyFromRecordset oRS
Else
MsgBox "No records returned.", vbCritical
End If

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"rodchar" wrote in message
...
But then how does it get to Excel (the 2 specific columns)?

"Bob Phillips" wrote:

ADO? An example using Access

Sub GetData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "SELECT LastName, FirstName From Contacts"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0)
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing
End Sub


You can get connection strings from
http://www.carlprothman.net/Default.aspx?tabid=81

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"rodchar" wrote in message
...
hey all,

what's the best way to take all the records from 2 fields in a
database
and
populate 2 specific columns in an excel spreadsheet?

thanks,
rodchar

is there a vb.net equivalent to this process?

"Bob Phillips" wrote:

This bit is getting the data into an array

If Not oRS.EOF Then
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0)
Else
MsgBox "No records returned.", vbCritical
End If

you can just dump it into a range

If Not oRS.EOF Then
Range("M1:N1").CopyFromRecordset oRS
Else
MsgBox "No records returned.", vbCritical
End If

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"rodchar" wrote in message
...
But then how does it get to Excel (the 2 specific columns)?

"Bob Phillips" wrote:

ADO? An example using Access

Sub GetData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "SELECT LastName, FirstName From Contacts"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0)
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing
End Sub


You can get connection strings from
http://www.carlprothman.net/Default.aspx?tabid=81

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"rodchar" wrote in message
...
hey all,

what's the best way to take all the records from 2 fields in a
database
and
populate 2 specific columns in an excel spreadsheet?

thanks,
rodchar

Would have thought so, but I don't know.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"rodchar" wrote in message
...
is there a vb.net equivalent to this process?

"Bob Phillips" wrote:

This bit is getting the data into an array

If Not oRS.EOF Then
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0)
Else
MsgBox "No records returned.", vbCritical
End If

you can just dump it into a range

If Not oRS.EOF Then
Range("M1:N1").CopyFromRecordset oRS
Else
MsgBox "No records returned.", vbCritical
End If

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"rodchar" wrote in message
...
But then how does it get to Excel (the 2 specific columns)?

"Bob Phillips" wrote:

ADO? An example using Access

Sub GetData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "SELECT LastName, FirstName From Contacts"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0)
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing
End Sub


You can get connection strings from
http://www.carlprothman.net/Default.aspx?tabid=81

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"rodchar" wrote in message
...
hey all,

what's the best way to take all the records from 2 fields in a
database
and
populate 2 specific columns in an excel spreadsheet?

thanks,
rodchar

Easier might be to do Data=Get External Data (a non programming approach)

--
Regards,
Tom Ogilvy

"rodchar" wrote in message
...
hey all,

what's the best way to take all the records from 2 fields in a database
and
populate 2 specific columns in an excel spreadsheet?

thanks,
rodchar

thanks everyone for the feedback.

"Tom Ogilvy" wrote:

Easier might be to do Data=Get External Data (a non programming approach)

--
Regards,
Tom Ogilvy

"rodchar" wrote in message
...
hey all,

what's the best way to take all the records from 2 fields in a database
and
populate 2 specific columns in an excel spreadsheet?

thanks,
rodchar