I am trying to do something like a vlookup in an Excel maco using ADO/VBA,
which looks up a value in an Access table and returns the corresponding value
to a variable in Excel. I have tried the this:

loc = "Select AE from table2 where ECT = Range("A" & rws).value"

where AE and ECT are columns in table2 and rws is a counter-like variable.
Any suggestions (websites, references) would be greatly appreciated!!

Brent Try this sub below.

Note if the lookup value is not text then change line:

.CommandText = "Select AE from table2 where ECT = '" &
Cells(iLoopRow, 1) & "'"

with:
.CommandText = "Select AE from table2 where ECT = " &
Cells(iLoopRow, 1)

This code assumes you have an Access database called: C:\temp\yourfile.mdb
And that your lookup data is in column 1(A) and you want to return your data
to Column 2 (B)


Sub Lookup

Dim iLoopRow As Integer

Set cnnConn = New ADODB.Connection
With cnnConn
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
.Open "C:\temp\yourfile.mdb"
End With

Set cmdcommand = New ADODB.Command
Set cmdcommand.ActiveConnection = cnnConn

Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn

With cmdcommand
.CommandType = adCmdText

'*** Get data for 100 rows
For iLoopRow = 1 To 100
.CommandText = "Select AE from table2 where ECT = '" &
Cells(iLoopRow, 1) & "'"
rstRecordset.Open cmdcommand

'*** Test to see if a lookup value was returned
If rstRecordset.EOF Then
Cells(iLoopRow, 2) = "Unknown"
Else
Cells(iLoopRow, 2) = rstRecordset("AE")
End If '*** No record returned

rstRecordset.Close

Next iLoopRow
End With '*** cmdcommand


'*** Cleanup
Set cmdcommand = Nothing
Set rstRecordset = Nothing
cnnConn.Close
Set cnnConn = Nothing


End Sub


Please post if this was helpful. Good luck.
--
Stewart Rogers
DataSort Software, L.C.


"Brent" wrote:

I am trying to do something like a vlookup in an Excel maco using ADO/VBA,
which looks up a value in an Access table and returns the corresponding value
to a variable in Excel. I have tried the this:

loc = "Select AE from table2 where ECT = Range("A" & rws).value"

where AE and ECT are columns in table2 and rws is a counter-like variable.
Any suggestions (websites, references) would be greatly appreciated!!

AE, ECT, and table2 will be in Access, and Range("A" & rws).value is in
excel. I want to loop through the column ERCOT in table2 of Access until I
find my value from Excel and return the corresponding value in column AE in
table2 of Access to a variable in Excel. I think the below searches through
Excel and then places a value into Excel??


"Datasort" wrote:

Brent Try this sub below.

Note if the lookup value is not text then change line:

.CommandText = "Select AE from table2 where ECT = '" &
Cells(iLoopRow, 1) & "'"

with:
.CommandText = "Select AE from table2 where ECT = " &
Cells(iLoopRow, 1)

This code assumes you have an Access database called: C:\temp\yourfile.mdb
And that your lookup data is in column 1(A) and you want to return your data
to Column 2 (B)


Sub Lookup

Dim iLoopRow As Integer

Set cnnConn = New ADODB.Connection
With cnnConn
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
.Open "C:\temp\yourfile.mdb"
End With

Set cmdcommand = New ADODB.Command
Set cmdcommand.ActiveConnection = cnnConn

Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn

With cmdcommand
.CommandType = adCmdText

'*** Get data for 100 rows
For iLoopRow = 1 To 100
.CommandText = "Select AE from table2 where ECT = '" &
Cells(iLoopRow, 1) & "'"
rstRecordset.Open cmdcommand

'*** Test to see if a lookup value was returned
If rstRecordset.EOF Then
Cells(iLoopRow, 2) = "Unknown"
Else
Cells(iLoopRow, 2) = rstRecordset("AE")
End If '*** No record returned

rstRecordset.Close

Next iLoopRow
End With '*** cmdcommand


'*** Cleanup
Set cmdcommand = Nothing
Set rstRecordset = Nothing
cnnConn.Close
Set cnnConn = Nothing


End Sub


Please post if this was helpful. Good luck.
--
Stewart Rogers
DataSort Software, L.C.


"Brent" wrote:

I am trying to do something like a vlookup in an Excel maco using ADO/VBA,
which looks up a value in an Access table and returns the corresponding value
to a variable in Excel. I have tried the this:

loc = "Select AE from table2 where ECT = Range("A" & rws).value"

where AE and ECT are columns in table2 and rws is a counter-like variable.
Any suggestions (websites, references) would be greatly appreciated!!

Brent,

The code below will look at each row in Excel column A, Make a call to
Access to find the lookup value in the table 2 and return the lookup value
into column B in Excel.

I was under the impression that you were passing an Excel value to Access to
return some value that you would store in Excel. That is what this code does.

Try it then let me know if it works for your solution.

--
Stewart Rogers
DataSort Software, L.C.