Excel and Variables

[MaCgyver]

Someone told someone I could help them out. I thought it would be easy from what he told me on the phone. However when I get to his location that didn't seem to be the case. Here is the situation. I pasted a small fraction of the excel spreadsheet for reference. I do know if I should work with excel or convert to a csv file. I've managed create a script for a csv file in the past however this situation has a few twists.

Column A and F are the two columns that has the important data. Column "A" has numbers which represent

a certain description. For example "1" refers to LIGHT FIXTURES NORMAL POWER across the board and "7" represents LIGHTING CONTACTORS across the board. The excel spreadsheet is broken down into groups or categories. In this example we have a SWITCH YARD, LIGHT MAINTENANCE and HEAVY MAINTENANCE. However his list goes on and on.

The desired result is to run a report that has the average percentage of each description. So for example he wants to see the average for "6" BRANCH CIRCUIT DEVICES LIGHT SWITCHES, for "3" BRANCH CIRCUIT PIPE NORMAL POWER and so on and so on. This will tell him how much is done in a particular description for reporting needs. Here is what makes this a problem or a challenge I should say. If the cell in column "F" is left blank that means it doesn't exist on this job(at location) for him. However he could go to another location and that description would exist. So the percentage can rage from 0 which means nothing has be done yet up to 100 which means its complete. If it is blank it doesn't exist at the job site So in order to get the correct average I cannot have the script including a line that doesn't have data in column "F".

The second catch is that the list would grow at some point ..meaning he could add another description in the future and the script has to account for that. Basically adding anothe number that would represent a particular description. I don't think that part is too hard to work on, but who knows..I maybe wrong.

I spent quite a few hours trying to work on this at home and I just keep spinning wheels. So basically I need to know if can this be done. And if so should I convert his excel file to CSV or is it ok to work with Excel. I spent a lot of wasted time trying to work with excel file and using for example StringSplit only to find out it will not work with excel. :"> So that was a good 2 or 3 hours of frustration. After searching this forum I found some info on excelcom but its 5:50 in the morning and I've been up all day. So I will have to rest and come back to figure out how can I use excelCom for what I am doing. I tried to use it but I got a guiXXX error and decided that it was time to post for some help. :">

Please see the attached file to see the setup of his excel sheet. Below is not coming up how I see it before I hit submit/post

Thanks,

A B C D E F

SWITCH YARD

DESCRIPTION PERCENTAGE COMPLETE

1 LIGHT FIXTURES NORMAL POWER 65

2 LIGHT FIXTURES EMERGENCY POWER 60

3 BRANCH CIRCUIT PIPE NORMAL POWER 57

4 BRANCH CIRCIUIT PIPE EMERGENCY POWER 46

5 BRANCH CIRCUIT WIRE NORMAL POWER 0

6 BRANCH CIRCUIT DEVICES LIGHT SWITCHES 20

7 LIGHTING CONTACTORS 35

LIGHT MAINTENANCE

DESCRIPTION PERCENTAGE COMPLETE

8 LIGHT FIXTURES NORMAL POWER HI-BAY LIGHTS 25

9 LIGHT FIXTURES EMERGENCY POWER, STRIP LIGHTS 0

3 BRANCH CIRCUIT PIPE NORMAL POWER 75

4 BRANCH CIRCIUIT PIPE EMERGENCY POWER 30

5 BRANCH CIRCUIT WIRE NORMAL POWER 10

6 BRANCH CIRCUIT DEVICES LIGHT SWITCHES 15

7 LIGHTING CONTACTORS

HEAVY MAINTENANCE

DESCRIPTION PERCENTAGE COMPLETE

1 LIGHT FIXTURES NORMAL POWER 60

2 LIGHT FIXTURES EMERGENCY POWER 75

3 BRANCH CIRCUIT PIPE NORMAL POWER 40

4 BRANCH CIRCIUIT PIPE EMERGENCY POWER 30

5 BRANCH CIRCUIT WIRE NORMAL POWER 10

6 BRANCH CIRCUIT DEVICES LIGHT SWITCHES

7 LIGHTING CONTACTORS

Edited July 23, 2006 by MaCgyver

[Skruge]

_ExcelCOM will do what you need. Post your code and you'll get better responses.

[Paulie]

...The desired result is to run a report that has the average percentage of each description...

Why use autoit for this? You can do it WAY easier in Excel itself

=AVG(F3:F7)

[MaCgyver]

Why use autoit for this? You can do it WAY easier in Excel itself

=AVG(F3:F7)

The person wants to be able to give the sheet to someone for data entry but doesnt want them to be able to copy the excel spreadsheet loaded with formulas. This is why he wants a script. Is the excelcom working good for others? Its in beta so i was worried about starting something with it.

Edited July 24, 2006 by MaCgyver

[Paulie]

The person wants to be able to give the sheet to someone for data entry but doesnt want them to be able to copy the excel spreadsheet loaded with formulas. This is why he wants a script. Is the excelcom working good for others? Its in beta so i was worried about starting something with it.

In autoit, the term "beta" is misleading, most people see "beta" and think,

"oh, these are dangerous functions that have never been tested and will screw up my computer"

But in autoit beta is more like:

" this is being updated constantly, therefore, it is impossible to declare it as an 'official version' even though, its is WAY better than the most recent 'official' one"

[bluebearr]

I'm with Paulie, I would use Excel for this and turn on worksheet protection. The data entry person will only be able to edit cells that are unlocked, and any cells that are marked as "hidden" will have their formulas hidden (though the value will show).

Go into the Help and look for "protect worksheet" for more info.

[MaCgyver]

I'm with Paulie, I would use Excel for this and turn on worksheet protection. The data entry person will only be able to edit cells that are unlocked, and any cells that are marked as "hidden" will have their formulas hidden (though the value will show).

Go into the Help and look for "protect worksheet" for more info.

You are correct. I automatically jumped to thinking I need a script. I just linked the cells that need to be included in the formula to another sheet and protected the sheet. I hid the cells that show results and locked the sheet, so no one can do anything like unhide or select a cell. They would need the password. I will just teach the client on how to update the formula on the locked sheet when he updates the spreadsheet with new descriptions that need to included in the formula.

I still want to learn how to use excelcom because it will be very useful at work.

Thanks guys.