pull data from an Access database into Excel template

R

Randal

Is there a way to fill the cells in a report set up in Excel from data in an
Access table? I don't want to create a new Excel file, but just populate
the cells with Access data. Thanks,
 
A

Arvi Laanemets

Hi

You can read data from Access into table on worksheet through an ODBC query.
Now create your report using queried data (through links) as source.
 
R

Randal

This works. But is there a way to filter based on the contents of a cell in
the spreadsheet? I need only the data that matches the ID in the cell.
 
A

Arvi Laanemets

Hi


Randal said:
This works. But is there a way to filter based on the contents of a cell in
the spreadsheet? I need only the data that matches the ID in the cell.
Click to expand...

At first create a query with fixed ID.
Then read CommandText propertie for QueryTable object for this query
(QueryTable object is attached to worksheet), using p.e. Watch window (so
you get an idea about syntax for it).
Create a procedure to change CommandText propertie per VBA (so that value
for WHERE clause is read from cell), and then to refresh the query.
Attach the created procedute to command button on worksheet, or attach a
hot-key to it


Arvi Laanemets
 
A

Arvi Laanemets

Hi again

Here is an example:

Public Sub UuendaPäringud()
kuu = ActiveSheet.Range("F1").Value
aasta = ActiveSheet.Range("F2").Value
...
Set qtQtrResults = Worksheets("Tasud").QueryTables(1)
Sheets("Tasud").Activate
ActiveSheet.Range("A2").Select
With qtQtrResults
.CommandType = xlCmdSql
.CommandText = _
"SELECT a.tabn, SUM(a.summa) AS arvest, "_
& "SUM(IIF(a.sotsmaks,1,0)*a.summa) AS sotsalus, "_
& "a.allyksus "_
& "FROM tasud a "_
& "WHERE a.tabn<>'' AND a.summa>0 AND a.tl<'060' AND "_
& "Year(a.sisestus)=" & aasta & " AND Month(a.sisestus)=" & kuu
_
& " GROUP BY a.allyksus, a.tabn ORDER BY a.tabn"
.Refresh
End With
....
End Sub

Arvi Laanemets
 
O

onedaywhen

If you are running a query in this way, rather than using dynamic SQL
(which is a little evil), create a 'stored procedure' in the database.
Below is an example (depending on permissions, you may actually get
away with executing this DDL from MS Query's SQL window!):

CREATE PROCEDURE
MyStoredProc (
start_date DATETIME,
end_date DATETIME
)
AS
SELECT
RefID,
DateEffective,
Earnings
FROM
EarningsHistory
WHERE
DateEffective
BETWEEN start_date AND end_date;

For MS Query, here's what should appear in the SQL window to run the
above procedure with parameters:

{Call MyStoredProc('01 JAN 2001', '01 JAN 2004')}

--
 
Top