943,074 Members | Top Members by Rank

Software Development > VB.NET > Need advice on what language to use (SQL to Excel)
Ad:
  • VB.NET Discussion Thread
  • Unsolved
  • Views: 394
  • VB.NET RSS
Permalink
Feb 2nd, 2010
0

Need advice on what language to use (SQL to Excel)

Expand Post »
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
Similar Threads
Reputation Points: 10
Solved Threads: 0
Newbie Poster
ssgatbliss is offline Offline
9 posts
since May 2007
Permalink
Feb 3rd, 2010
0
Re: Need advice on what language to use (SQL to Excel)
Click to Expand / Collapse  Quote originally posted by ssgatbliss ...
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
Reputation Points: 10
Solved Threads: 0
Newbie Poster
Brick is offline Offline
8 posts
since Jan 2008
Permalink
Feb 3rd, 2010
-1
Re: Need advice on what language to use (SQL to Excel)
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
Reputation Points: 10
Solved Threads: 0
Newbie Poster
ssgatbliss is offline Offline
9 posts
since May 2007
Permalink
Feb 4th, 2010
0
Re: Need advice on what language to use (SQL to Excel)
Click to Expand / Collapse  Quote originally posted by ssgatbliss ...
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.
Reputation Points: 37
Solved Threads: 24
Posting Whiz in Training
G_Waddell is offline Offline
207 posts
since Nov 2009
Permalink
Feb 4th, 2010
0
Re: Need advice on what language to use (SQL to Excel)
Great, I'll try that!

Thanks,

CJ
Reputation Points: 10
Solved Threads: 0
Newbie Poster
ssgatbliss is offline Offline
9 posts
since May 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in VB.NET Forum Timeline: Start Menu Items
Next Thread in VB.NET Forum Timeline: TCPlistener and server





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC