Question

[Les Stout]

Hi all, is it possible to do a vlook up to a closed access table from
excel ?

Les Stout

 

[Bob Phillips]

Les,

Here is an example of using ADO to query an access database

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 * 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) & ", " & ary(2, 0)
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing
End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Not even to an open access table. Vlookup only works with ranges and to a
limited extent with an array.

 

[Luis Fernando Ortiz M.]

Les:

Yes. I can wrote Excel functions with this feauture. Just name a table in
the source workbook and build de formula with the two workbooks open. Excel
store the path and the name workbook and table name when the source workbook
is saved and close.

when you open the destination workbook you recive a notice of links, just
clic in yes

Regards,


Luis F. Ortiz