pull data from an Access database into Excel template

  • Thread starter Thread starter Randal
  • Start date Start date
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,
 
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.
 
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.
 
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
 
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
 
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')}

--
 
Back
Top