Creating an Excel table from Access

[Andi]

Hi,

I have an Access database which I use to log downtime for systems. I have a
requirement to produce a monthly report based on this data, however, this
needs to be exported to excel in a specific format.

Down the left side of the report need to go the names of the services, with
the days (numerically like: 1, 2, 3 .. 29, 30 etc) across the top. Then I
need to count the number of times each service was down on a give day, and
insert that information into the necessary cell - so if intranet services
had been down twice on 16th March, for example, there would be a 2 in column
16, which would also be filled in red. Finally, the total amount of
downtime experienced by each service needs to go at the very right hand side
of the table. Where no downtime was recorded, the cells need to be green.

I can provide further information if necessary.

Many thanks in advance of any replies,

Andi

 

[(PeteCresswell)]

Per Andi:

I have an Access database which I use to log downtime for systems. I have a
requirement to produce a monthly report based on this data, however, this
needs to be exported to excel in a specific format.

Down the left side of the report need to go the names of the services, with
the days (numerically like: 1, 2, 3 .. 29, 30 etc) across the top. Then I
need to count the number of times each service was down on a give day, and
insert that information into the necessary cell - so if intranet services
had been down twice on 16th March, for example, there would be a 2 in column
16, which would also be filled in red. Finally, the total amount of
downtime experienced by each service needs to go at the very right hand side
of the table. Where no downtime was recorded, the cells need to be green.

I do stuff like this on a regular basis and here's my approach:
--------------------------------------------------------
1) Instantiate an Excel object in the Access VBA code.

2) Open up a .WorkSheet within the Excel object

3) Set .ReferenceStyle = R1C1

4) Open up a .Recordset in Access that contains what you want to
put into the workbook

5) Beat down the sheet populating myWS.Cells(R,C).Value

6) When I need to do something with a .Cell or .Range that
I've never done before, record a macro in an Excel
workbook and copy/translate the code to MS Access VBA
in such a way that there are no .Select statements.

e.g. setting a cell's .BackColor=Red or Green....
The recorded macro will do a .Select and then set the
selected cell or range's properties. I'd alter that
to set the properties of a .Cell(R,C).