is there a way to do this

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

is there a vb.net equivalent to this process?

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)

 

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Tom Ogilvy]

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

 

[Guest]

thanks everyone for the feedback.

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