All,
I need help in deciding what coding language would be easiest to use for a project (read below):

So at work we have a scheduling system that is SQL based. The programmers already have all the code in place to parse the database into an excel sheet with columns like asset name, scheduled downtime, estimated return to operations, etc. Currently one of my guys takes this excel sheet and 'builds' a daily report (which is an excel grid imported to a power point slide) which shows the scheduled downtime of items by indicating red for not available. Currently we have macros built so my guy just highlights the excel cells (each one represents an hour of the day) and hits a key to color the cells red (green is default). Since we are taking data that is already in an excel sheet and just displaying it graphically in another excel sheet I'm sure there is a way to automate this. The question is what language would I use? I'm guesing some form of Visual Basic? I'm not much of a programmer (had some basic classes in C++ and Java years ago.) but I've decided to take this on to help my guys out as they spend a few hours a day going over the data to produce this power point slide. Even if I could just produce the excel output and they manually cut and paste it into the powerpoint slide would save a lot of time not to mention eliminating the human error invlolved in reading data then coloring excel cells red or green.

I'm not looking to pull the data from the SQL database, it's just excel data like dates and times into a bar chart that is also an excel sheet. I can't use the 'make a chart' functions as this excel sheet is modified manually throughout the day to show what actually occured as to what is scheduled in the database. (No they don't update the SQL database as itmes change).

Yes I know there are better ways to do this and I've already been told that they don't want the excel bar chart changed so I'm stuck with coloring this excel grid until management changes over.

Thanks in advance,

CJ

The programmers already have all the code in place to parse the database into an excel sheet with columns like asset name, scheduled downtime, estimated return to operations, etc. CJ

Can you ask the programmers to colorize the cells when they parse the data to excel based on rules you give them?
Don't let them tell you it can't be done, because I've done it.

If not, you are probably going to have to use VB Script within Excel. I am not familiar with this, but if you are not a programmer you may have a hard time with it

The programmers no doubt would charge too much money for this. It's an out of cycle request. Times being what they are the programmers would charge us too much money for something relatively simple, that's why I'm going to at least give it a shot. There's no incredible rush, the works is getting done, I just thought it could be automated.

Thanks,

CJ

All,
Currently we have macros built so my guy just highlights the excel cells (each one represents an hour of the day) and hits a key to color the cells red (green is default).

I'd open up those macros and expand on them to build one that parses through the cells in Excel that you wish to evaluate and then colour according to your criteria. - being as they're Excel macros they're probably written in VBA.

You can work through Excel cells by specifying them like this mysheet.Cell(y,x) - note the row comes first in excel then the column or you can use ranges.

Alternatively, if you run the record macro feature in Excel and manually carry out the steps i.e. select the cell turn it red move on to next cell turn it red then when you stop recording the code to turn one cell red and then move to the next and turn it red will be written and you can expand on it to include your conditionals and other variables.

This article has been dead for over six months. Start a new discussion instead.