Criteria parameter from Excel

[Trinak]

I have an excel spreadsheet that pulls data from an access query, this works fine but, I need to enter date value criteria in spreadsheet an get access query to "see" these values and only return the requested info.
Has anyone got any ideas ?????
Thx.

 

[mdthornton]

The best way to do this depends very much on your set up, ie how many users, how dynamic is the data, layout of the spreadsheet etc... but assuming the simplest case to start with you could link your spreadsheet as a table in access and specify appropriate worksheet and/or range and then just use this as a regular table in a query and join on your criteria value(s).

This link could be permanent or created and dropped on the fly.

Hope this helps, Good Luck,
Mike

 

[Trinak]

Thx Mike that is way I have just done it. Just wondering if theres a vba function that could be created that could be used in the query criteria that directly accesses specific cells in a know workbook. Curiosity is good but can drive u mad !!!

 

[mdthornton]

Well I was curious and sceptical but I think I have a solution although its a bit of a hack.

Here's a bare bones function definition, you'll need to play around with file names, columns, etc.

Public Function getExcelData() As Integer
Dim rst As Recordset

CurrentDb.Execute "delete * from excel1"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "excel1", "C:\temp\book1", 1, "A1:A2"
Set rst = CurrentDb.OpenRecordset("select parm from excel1&quot
getExcelData = rst![parm]
rst.Close

End Function

Now just put getExcelData on the criteria line.

Note: I originally had a deleteobject command to drop the table but it didn't like that so "excel1" needs to be permanent and hence the "delete" to clear out the table.

Good Luck,
Mike

 

[Trinak]

Thx again Mike, r u the only one out there ??
The function works well but still involves transfering data to 'local' database table, there must surely be a way of creating a DDE,ADO etc type of link to the spreadsheet and return values to the function. The link table method appears to be working well with little overhead but will keep trying.