I have a Macro which imports data from an Access Table (Tbl1mdb) to an Excel Workbook (Book 1 - Sheet1). The Macro runs as expected.
Within the Macro, there are two dates which are hardcoded, an extract from the VBA is below:
"Select 'Tbl1mdb'.compdate, 'Tbl1mdb'.targdate, 'Tbl1mdb.code & "FROM 'C:\mymdb" & "WHERE ('Tbl1mdb.targdate<={ts '2007-05-22 00:00:00'} And 'Tbl1mdb.targdate>={ts '2007-04-29 00:00:00'})"
An Extract of the imported Data in Excel is also shown (columns A - C)
At the front of this Macro I would like to have one input box (or Two if one isnt possible) which would ask the user for a 'Startdate' and 'Enddate'.
These dates would then replace the dates in the code above. I am not sure how to set these dates in the code and would welcome any ideas or alternatives.
Also, at the end of the Macro I would like to run 'CountIf' Statements on the imported data in Excel. For example, if Column C (Code) = 'A' then I want the Macro to return a count of rows which meet the Statement to Sheet 2 (A1). I will be running lots of 'Countif' statements but this is an example. I would prefer this to be done in the Macro as the data in Sheet 1 will always change by number of rows depending on the dates. I also want to run a 'Countif' Statement in the Macro which compares compdate (column A) and targdate (column B) and counts if compdate > Targdate. I am really not sure how to write this in the Macro but am sure it can be done.
Many Thanks for listening
Within the Macro, there are two dates which are hardcoded, an extract from the VBA is below:
"Select 'Tbl1mdb'.compdate, 'Tbl1mdb'.targdate, 'Tbl1mdb.code & "FROM 'C:\mymdb" & "WHERE ('Tbl1mdb.targdate<={ts '2007-05-22 00:00:00'} And 'Tbl1mdb.targdate>={ts '2007-04-29 00:00:00'})"
An Extract of the imported Data in Excel is also shown (columns A - C)
| Book1 | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | compdate | targdate | code | |||
| 2 | 25/05/200700:00 | 25/05/200700:00 | A | |||
| 3 | 18/06/200700:00 | 18/06/200700:00 | D | |||
| 4 | 15/06/200700:00 | 15/06/200700:00 | A | |||
| 5 | 20/06/200700:00 | 19/06/200700:00 | A | |||
| 6 | 22/04/200700:00 | 21/04/200700:00 | A | |||
| 7 | 21/04/200700:00 | 20/04/200700:00 | A | |||
| 8 | 12/04/200700:00 | 12/04/200700:00 | B | |||
| 9 | 18/05/200700:00 | 16/05/200700:00 | D | |||
| 10 | 22/05/200700:00 | 19/05/200700:00 | D | |||
| 11 | 19/05/200700:00 | 20/05/200700:00 | D | |||
| 12 | 12/05/200700:00 | 13/05/200700:00 | E | |||
| 13 | 13/05/200700:00 | 13/05/200700:00 | E | |||
| 14 | 16/05/200700:00 | 17/05/200700:00 | A | |||
| 15 | 28/05/200700:00 | 28/05/200700:00 | A | |||
Sheet1 | ||||||
At the front of this Macro I would like to have one input box (or Two if one isnt possible) which would ask the user for a 'Startdate' and 'Enddate'.
These dates would then replace the dates in the code above. I am not sure how to set these dates in the code and would welcome any ideas or alternatives.
Also, at the end of the Macro I would like to run 'CountIf' Statements on the imported data in Excel. For example, if Column C (Code) = 'A' then I want the Macro to return a count of rows which meet the Statement to Sheet 2 (A1). I will be running lots of 'Countif' statements but this is an example. I would prefer this to be done in the Macro as the data in Sheet 1 will always change by number of rows depending on the dates. I also want to run a 'Countif' Statement in the Macro which compares compdate (column A) and targdate (column B) and counts if compdate > Targdate. I am really not sure how to write this in the Macro but am sure it can be done.
Many Thanks for listening
