Excel - Access interfacing
Darragh
Wondering if anyone could point me in the direction of resources that
could assist with using a spreadsheet grid in excel as the interface
for dropping values into an Access table. I'm not having much luck
searching the internet.
Basically, I'm after something like this pitiful graphic demonstrates
| 1 | 2 | 3 | 4 | 5 |
----------------------------------
Criteria | X | | | | |
----------------------------------
Basically, when a user puts an X or somehow marks a box in an excel
spreadsheet, this translates to something like this in an access table
Criteria Name | Score
--------------------------
Criteria | 1
If you know what I mean.
KARL DEWEY
table.
But why not just add a criteria parameter to your query. Open the query in
design view.
In the criteria row of the grid for your field enter this --
[Enter your criteria]
It will prompt you for the input when the query is run.
Jamie Collins
On Oct 18, 6:10 am, KARL DEWEY <KARLDE...@discussions.microsoft.com>
wrote:
> You can interface by clicking on Access menu FILE - Get External Data - Link
> table.
>
> But why not just add a criteria parameter to your query. Open the query in
> design view.
> In the criteria row of the grid for your field enter this --
> [Enter your criteria]
>
> It will prompt you for the input when the query is run.
You won't get the prompt in Excel :(
Jamie.
--
Jamie Collins
>
...but the OP could use VBA on the Excel side to get the parameter
value e.g.
Excel.Application.MATCH("x", Sheet1.Range("A2:E2"))
then choose your method of execution: an ADO command object (no dynamic
SQL required), ADO connection object requiring dynamic SQL e.g.
sSQL = "EXECUTE KarlsParamQuery " & Excel.Application.MATCH("x",
Sheet1.Range("A2:E2")) & ";"
conADO.Execute sSQL
Unfortunately, an Access/Jet parameter query cannot be invoked using
MSQuery; dynamic SQL could be used (for which the SQL syntax needs to
be ODBC: curly braces, etc) but you need to use VBA and why bother to
use MSQuery in VBA?!
Jamie.
--
Jamie Collins
On Oct 18, 10:30 am, "Jamie Collins" <jamiecoll...@xsmail.com> wrote:
> the OP could use VBA on the Excel side to get the parameter
> value
They could use Jet SQL to get the value from Excel e.g.
SELECT SWITCH(
F1 = 'x', 1,
F2 = 'x', 2,
F3 = 'x', 3,
F4 = 'x', 4,
F5 = 'x', 5,
TRUE, 0) AS excel_result
FROM [Excel
8.0;DATABASE=C:\Workbookname.xls;HDR=NO;].[Worksheetname$A2:E2];
Jamie.
--