Can I set a Lookup formula in Excel to read data in an access table
using a macro?

What I'm after is for the user to put in a reference number in a cell
and the clients details to appear in an adjacent cell. I know this is
pretty easy to do completely in Excel but all my data appears in
Access. I do not want to export the Access data into Excel every time
I want to perform this function.

Thanks

Look into Dataget External datanew database query, and setting a cell
value as an input parameter.

NickHK

"monkey harry" wrote in message
oups.com...
Can I set a Lookup formula in Excel to read data in an access table
using a macro?

What I'm after is for the user to put in a reference number in a cell
and the clients details to appear in an adjacent cell. I know this is
pretty easy to do completely in Excel but all my data appears in
Access. I do not want to export the Access data into Excel every time
I want to perform this function.

Thanks

I've got as far as recording as a macro, whcih works Ok, but I still
can't figure out how to make it only return records depending on the
reference number in a specific cell.

If you right click with the query data, you should see the "Parameters" menu
item greyed out (as you do not have any parameters yet). So select "Edit
Query", step through the wizard until you get to last step, select the 'View
data or edit query in MS Query", click finish.
Now in MS Query, click the "SQL" button and edit the query to read <Your
SQL WHERE ID=[YourParameter] (or whatever the SQL should be). Enter any
suitable value at the input box, OK and "Return data to Excel".
Now when you right click on the query data, the "Parameters" menu items will
be active. From there you can set the cell whose value you will use.

Unless any knows a simpler method ...

NickHK

"monkey harry" wrote in message
ups.com...
I've got as far as recording as a macro, whcih works Ok, but I still
can't figure out how to make it only return records depending on the
reference number in a specific cell.

Thanks, can't get it to work though.

Here is my code. It opens a new access query and will filter it
according to what is used as "ID". This is the part I'm struggling
with as I want it to ask the user to input the ID:

' Open query
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=G:\My
Documents\db3.mdb;DefaultDir=G:\My Documents;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;Page" _
), Array("Timeout=5;")), Destination:=Range("F11"))
.CommandText = Array( _
"SELECT `Section 4 list`.ID, `Section 4 list`.FullName,
`Section 4 list`.FlatRoom, `Section 4 list`.HouseName, `Section 4
list`.HouseNumber, `Section 4 list`.StreetName, `Section 4
list`.TownName, `Sec" _
, _
"tion 4 list`.PostCode" & Chr(13) & "" & Chr(10) & "FROM `G:\My
Documents\db3`.`Section 4 list` `Section 4 list`" & Chr(13) & "" &
Chr(10) & "WHERE (`Section 4 list`.ID=1)" _
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

.....WHERE `Section 4 list`.ID=" &
Thisworkbook.Worksheet(1).Range("A1').Value
Change the range to match your requirements.

But do you need to use code to create the whole query every time (and delete
the previous one) if you are only changing the value of the one parameter ?
That's why I suggested the previous method.

NickHK

"monkey harry" wrote in message
oups.com...
Thanks, can't get it to work though.

Here is my code. It opens a new access query and will filter it
according to what is used as "ID". This is the part I'm struggling
with as I want it to ask the user to input the ID:

' Open query
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=G:\My
Documents\db3.mdb;DefaultDir=G:\My Documents;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;Page" _
), Array("Timeout=5;")), Destination:=Range("F11"))
.CommandText = Array( _
"SELECT `Section 4 list`.ID, `Section 4 list`.FullName,
`Section 4 list`.FlatRoom, `Section 4 list`.HouseName, `Section 4
list`.HouseNumber, `Section 4 list`.StreetName, `Section 4
list`.TownName, `Sec" _
, _
"tion 4 list`.PostCode" & Chr(13) & "" & Chr(10) & "FROM `G:\My
Documents\db3`.`Section 4 list` `Section 4 list`" & Chr(13) & "" &
Chr(10) & "WHERE (`Section 4 list`.ID=1)" _
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

NickHK,

I'm trying to do something similar to "monkey harry". An Excel spreadsheet
column contains inventory item numbers that the user types in. The inventory
data is in an Access database. When I use the DataImport External DataNew
Database Query method, I don't see anywhere to set a cell value as an input
parameter. I'm obviously missing a step or something. Any help please?
--
Thanks


"NickHK" wrote:

Look into Dataget External datanew database query, and setting a cell
value as an input parameter.

NickHK

"monkey harry" wrote in message
oups.com...
Can I set a Lookup formula in Excel to read data in an access table
using a macro?

What I'm after is for the user to put in a reference number in a cell
and the clients details to appear in an adjacent cell. I know this is
pretty easy to do completely in Excel but all my data appears in
Access. I do not want to export the Access data into Excel every time
I want to perform this function.

Thanks