Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
is there a way to do this
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
is there a way to do this
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
is there a way to do this
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
is there a way to do this
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
is there a way to do this
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
is there a way to do this
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
is there a way to do this
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
is there a way to do this
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|