Export to Excel with colour coded cells

[smally]

Hi all.

I'm currently developing a database that stores and keeps track of training that employees have done.

I'm hoping for a bit of advice for the best way to design the database and get it to export to spreadsheets for other people to see.
Currently all training information is stored on spreadsheets. However this contains limited information so I've moved it to Access. However I still need to present the data in those spreadsheets for viewing by other employees.

If you look at my attached spreadsheet, that is how I need my data to be displayed. The spreadsheet shows expiry dates for those particular training courses. For each employee you can easily see what training is and isn't required, and when the training expires. Also it has conditional formatting to colourise when training will expire.

I've created my database with a few tables
tblEmployees -- Stores employees details, name, address etc
tblTrainingDetails -- Stores employees training course, date taken, expiry date etc.

The spreadsheet would only store the latest training expiry date the employee did for that course. The database now stores all previous training they've done too. This is all within tblTrainingDetails.

I've managed to make a crosstab query, and another query on top of that to re-arrange the data to the way the spreadsheet has it.


The problem I'm currently facing is: some employees don't require certain training. If this were on the spreadsheet then I'd simply grey out the cell to show it's not required. I need to be able to do this within my database and output the data into the spreadsheet with the same formatting and colour coded cells.


I've attached a trimmed down version of my database and example records. If anyone's got any suggestions or improvements to my database I'd love to hear them.

 

[spikepl]

A brief comment: training requirement is given by the position somebody fills. Training history, in turn, ties to a person. That person can fill, with time, one or more positions.

 

[The_Doc_Man]

spike commented on your design. I'm going to ask this: Were you looking for info on how to do color coding directly from Access for the spreadsheet you built or were you really looking for structural guidance? Or were you up for anything you could get?

 

[smally]

Training requirements has nothing to do with their position. Sorry I shouldn't have included the department field. That's a different problem I am facing for how to get it grouped like it is in the spreadsheet.

I'm looking for a bit of advice on both my structure, and how to get colour coding for the spreadsheet.

For starters I don't know how/where to store information regarding what training is required for individuals.

If we take Eugene Stawart as an example. From the spreadsheet we can see he requires being trained in Fire Safety, First Aid, Fork Lift, Fuel Efficiency, TM T1 or T7, the Occupation H&S (IOSH), and Lorry Loading. We can see he already has training for all except Fork Lift and IOSH from the expiry dates shown.
In the spreadsheet I'll either leave the cell white or change it to grey, and let conditional formatting take care of the rest. But in the database I'm not sure how/where to store this info.

 

[The_Doc_Man]

OK, I'll give you some shorthand advice on where to look for good examples. You will need to understand the following topics, for which either online Help or the great Google Brain would be helpful:

In ALL Office products that support VBA: Collections (and selection/traversal of same by name or index)

Excel Application Objects (to see components and properties thereof).

A Workbook is a member of the Workbooks collection, which represents open .XLSx files

A Worksheet is a member of Workbooks.Worksheets collection

A Cell is a member of the Workbooks.Worksheets(m) layered collection.

Cells can be selected by Row and Column numbers (both LONG variables)

Once you have a cell or group of cells, you can declare a Range by specifying the corners of the range as ("A1:B2") for example. Or you can do it by specifying the cells as in the sample below, where shtXL is an object that was SET to a single worksheet using the collection methods mentioned earlier.

shtXL.Range(shtXL.Cells(lRowX, lColX), shtXL.Cells( lRowY, lColY )

Again, the two cells are the corners of the range's rectangle - and yes, the rectangle can be a single sell if the row numbers are the same and the column numbers are the same for the Cell selection.

Now, to put something in the cells, you select the cells either as a range (to do the same thing to all cells in the range) or as a single cell.

Assuming an object variable named Amoeba as having been set to a single cell,

Amoeba.Value = text - sets the text of the cell.

Amoeba.Orientation = (one of Excel's orientation constants) - sets the direction of text

Amoeba.Font.Name = "{name of a font}" - sets the font for that cell

Amoeba.HorizontalAlignment = (one of Excel's alignment constants ) - sets position of text inside cell, as e.g. exCenter to center the text

Amoeba.Font.Color = {Excel color constant} - set text color

Amoeba.Interior.Color = {Excel color constants} - set cell "fill" color

The above examples are by NO means exhaustive. Your "great Google Brain" search will tell you about the objects and properties. Coloration and special effects will apply to Cell and Range objects, so maybe print out or take good notes from those articles.

A word of warning applies here: Look up and maybe even print out some on-line article that nicely organizes the Excel color constants. Their order and orientation are NOT particularly intuitive. Use the actual numbers from the article, numbers which will range from something like 0 to 58.

How do you do this in code?

Create am Excel Application Object

Open a file for an idle Workbook object in the Workbooks collection. This is basically a call for the objXL.Workbooks.Open with parameters for filename, readonly, editable, and other properties. THIS is where you specify the name of the workbook, not at the time when you save and close it.

Select a worksheet from the Workbook's Worksheets collection. A shortcut here is to make the selected sheet the ActiveSheet, which effectively is the same as the longer string Workbooks.Worksheets(m).

Randomly or in a loop, fill in the blanks for your cells. Other than the guidelines I mentioned above regarding property usage, you're on your own because it is YOUR design that you have to implement.

When ready to exit, remember to CLOSE the workbook and SAVE it. The Google-Brain search will tell you how to do that, too. It is basically an objXL.Workbooks.Close with some parameters.

 

[smally]

Massive thanks. I've gone through what you've said. I've tried to do it through excel.

See attached macro enabled spreadsheet. The refresh button clears the table, imports the Exporter table from the database, adds sub headings and sorts in a specified order.
Column B shows department, and is hidden.

In the database, the Exporter table is created via a series of queries (Step1, Step2, Step3).

The problem I'm facing now is I don't know how to store the data to determine whether the employee does or doesn't require that training (represented as a grey cell in the spreadsheet).

 

[The_Doc_Man]

You store two things.

1. The training requirement comes in as a table of a PK for the training course number plus a course title, any other descriptive data, and a number of (days, months, or years) after which the training expires, thus requiring a refresher or other update.

2. The personal compliance comes in as a "junction table" with person ID as a FK to the PK of the personnel table, a course ID as an FK to training table, a date of taking the course, and any other info related to the specific instance of the course.

Now, to decide whether a person does NOT require the training, you need a query that takes the person ID, the course ID, and the course expiration. Compute the oldest date for which the course would be valid (i.e. today - expiration)

Do a DCOUNT of the entries in the junction table with the Person ID, Course ID, and the "take date" is greater than that oldest acceptable date. If the count is 1, the employee does NOT require the course. If 0, s/he DOES require the course.

 

[smally]

Ok, so I've now changed the way my database works. Similar but slightly different to what you've suggested because either I haven't fully understood, or I haven't properly explained what I require.

I'll explain the new tables first:
I've created 2 new tables.
- tblTrainingCourses. This table stores all the training courses. CourseID (PK); CourseName; CourseCategory (FK)

-tblCourseCategories. This table store all the possible categories a training course could be (it is also the headers in the spreadsheet). CategoryID (PK); CategoryLabel.

A training course can be classed as a category (or be null). And a category may have many courses.

- NEWtblTrainingDetails. This table is exactly the same as it's earlier version, but now stores CourseID (FK) instead of a course name. I've kept all other course details within this table as the same course may have varying expiry dates depending on who provides the training.

I've created a bunch of new queries, that ultimately provides the same results.

Now what I still can't quite get is those grey cells.

Example: "An employee could do training in First Aid. That training will be recorded in tblTrainingDetails." However I need to somehow/somewhere store information for whether an employee requires training in that area (regardless of if he's done the training or not).
In the spreadsheet shown, many people have ADR training, but due to a change in company rules, or employee position etc., it is now not required, therefore has been coloured grey.

Something like frmCategorySelection [see attachment], where I'm to select who needs what is what I'm aiming for.
Then somehow get that in the spreadsheet, which is determined by a white cell, or a grey cell. (The green, amber and red colours soley determine the expiry date's status).

 

[AnthonyGerrard]

Can you store it instead in SQL Server and use SS Reporting Services.
Would seem to be easier that way to me. I think the latest version of free sql allows reporting services.

 

[The_Doc_Man]

Example: "An employee could do training in First Aid. That training will be recorded in tblTrainingDetails." However I need to somehow/somewhere store information for whether an employee requires training in that area (regardless of if he's done the training or not).
In the spreadsheet shown, many people have ADR training, but due to a change in company rules, or employee position etc., it is now not required, therefore has been coloured grey.

Extend the model I gave you so that if training is required, there is an entry in the junction table with Employee ID and Course ID but no date. Or add a flag to the junction table to record "no course taken yet" - or something equivalent.

Now, if the course is not required, no entry. If required but not fulfilled - entry with no date. If required and fulfilled - entry with date.

This is based on the idea of keeping the tables sparse - i.e. not having one entry for every possible combination of course and employee. Note, however, that if you have less than 50 courses and less than 50 employees, 2500 records is not such a burden. It is when you start to get hundreds of courses or hundreds of employees that you have to decide when you need to think "sparse."

 

[smally]

Ok so I've done what you've suggested, with a little tinkering.

I didn't like the idea of having some training records entered, but not showing a date. Though I have created a junction table between employees and the new course categories table. In the new junction table I've done what you've suggested.

Then after a few new crosstab and make table queries, I'm able to make a new table (Exporter2) with the sole purpose of deciding whether a cell should be white or greyed. Currently it shows a 1 or null, I'd be very appreciative if someone could tell me how to make the values True or False instead.

Now in my Excel macro, it imports 2 tables. Table Exporter2 just for determining whether training is required for that individual (white or grey), and table Exporter just for the actual expiry dates.

Everything seems to work. My full database has a whole lot more employees, training courses, and categories. But before I'll start to implement this and since I'm still reasonably new to Access, I'd thought to check see if anyone's got any suggestions for improvements to my coding or to my database/queries

Thank you.

 

[liddlem]

Hi Smally
Not sure if you managed to resolve the "grey cell" matter yet.

This may require a slight change in thinking but....
What about one or a combo of the following.
1. Is there any reason that you cannot drop the Excel spreadsheet and simply create a report ion Access? You can format that record/field at run time.

2. In access, include a column (in your query) that identifies the grey cells. Then apply conditional formatting to that column.

 

[smally]

Hi liddlem

I've managed to resolve the grey cell by creating another table which holds data that determine whether an employee does or doesn't need said training, and that is imported via excel through the same import macro.

The reason why I'm using excel is because currently, all information at my workplace is stored and viewed on multiple excel files on a public folder (but all sheets are protected, so only I can change data). I have transfered and converted all this information to an MS Access database. However only I have Access installed.

So what I'm planning is using a database on my PC, I'll then use the make table queries to update a seperate access db on the public folder, and the excel files can update their data from that.