Here is a preliminary (very preliminary) macro to "create" the Summary worksheet.
As written, it creates a new sheet each time it runs -- but this is only for debugging purposes.
Hmmm. Seems to be a problem. When I save the workbook, and try to re-open, Excel finds bad data; it removes the data validation.
We'll need a new approach -- it's probably for the Type Code Labels. I'll have to look into that.
While I look into this further, I have found that saving the book in the binary format (.xlsb) enables it to be re-opened without causing that error message. (It also reduces the file size to 1/3 of the .xlsm version). So you could do this while examiining the format.
More questions (getting into the listing of differences):
Will data on the Summary sheet be deleted? In other words, could a device be completely removed?
My initial thinking was that we only need to check rows that have data in any of the user-modifiable fields on the Summary sheet.
If the engineers can DELETE data, we would also need to check rows that have data in those fields on the CompareData sheet.
to ensure the latest dataSince it is created from the CompareData worksheet, so it requires tha
the CompareData worksheet be "up to date" before running. So I a
thinking that before Summary is created, it will need to Call th
CreateCompareData macro -- do you see any problem with that? No i don't see any issues with this, in fact it would be the best wa
I think the formatting/locking as suggested will work out great but wilComments and suggestions on the formatting, locking, etc would b
appreciated. Also if you have any comments, thoughts on the dropdow
for the TypeCodeLabel. At present, there are over 100 entries, but the
don't seem to be organized in such a way as to make use of cascadin
lists. I did alphabetize the list, so that might help a bit.
The next step will be to mark those lines which are NOT present in th
CompareData sheet, so as to preserve them when changes such as addin
nodes or loops are made. And will also give a start to generating
sheet with the "differences".
Hidden sheets are fine, I don't see any problems with thatA further note on this issue. By placing the validation list on
separate worksheet, rather than setting it up as a comma delimite
string, the workbook can be saved (and re-opened) as an .xslm file. S
this will not impede anything. Instead of developing the string, w
merely add a hidden worksheet, and place the elements there). I stil
want to figure out what's going on, though.
Yes there will be times that the engineer will delete/remove data fromMore questions (getting into the listing of differences):
Will data on the Summary sheet be deleted? In other words, could
device be completely removed?
My initial thinking was that we only need to check rows that have dat
in any of the user-modifiable fields on the Summary sheet.
If the engineers can DELETE data, we would also need to check rows tha
have data in those fields on the CompareData sheet.
I agree with you, infact the [DeviceType] column servers no purpose oncFurther question as I am getting into this Summary sheet.
It seems to me there is no need to allow changes by the end user in th
Device Types column.
Because of how the sheet is set up, with all possible node/loop/devic
addresses, the DeviceType and Device Types are already defined by thei
row. They have already been assigned a merged address. So an Enginee
should not be adding a Monitor to the Detector segment, etc.
Really since the [Merged Address] and the [DeviceType] is prepopulatedRight now, the Device Types column is blank if there is no devic
(although the DeviceType column is populated; and the MergedAddres
reflects the DeviceType appropriate for that line). But we could jus
add event code so that if anything gets filled in on the other editabl
columns in that row, Device Types would be populated appropriately also
Sorry for the delayed responce
SNIPPED
Here are some updated macros to try out.
You should install them in separate modules.
The first is to create the compare sheet, but it also has some universally used constants defined or declared.
The second creates both the summary sheet, and also adds in event code for that sheet so as to enable the automatic updating of the Device Types column if an editable column is changed. The event code within that module is not easily understood, but after the sheet is created, you can see it by right clicking on the worksheet "Summary" tab, and viewing the code in the window that opens.
Some caveats:
1. Save the workbook in BINARY format (.xlsb). I will eventually get around to putting the dropdown on a separate worksheet, but haven't yet.
2. In order to create the event code in the worksheet, you must allow the vbe project module to be "trusted"
Excel Options / Trust Center / Trust Center Settings / Macro Settings / Select "Trust access to the VBA project object model"
This probably only needs to be selected when creating the Summary worksheet. If you distribute workbooks with the Summary worksheet already present, after we have things up and running, that option probably will not need to be checked, as the event code should already be there.
Since these are getting kind of long, I will post links to where they are in Skydrive. You should be able to download them. If not, I'll post here in full.
RonCompareData: http://sdrv.ms/YXntiR
RonSummary: http://sdrv.ms/Wq4An2
Download the files; then open your workbook with the PanelData, DeviceType worksheets.
Open the VBEditor. Ensure your project is highlighted; then select File/Import and import these .bas files.
Let me know.
Ok I have done both of theseSome caveats:
1. Save the workbook in BINARY format (.xlsb). I will eventually ge
around to putting the dropdown on a separate worksheet, but haven't yet
2. In order to create the event code in the worksheet, you must allo
the vbe project module to be "trusted"
Excel Options / Trust Center / Trust Center Settings / Macr
Settings / Select "Trust access to the VBA project object model"
And I have done this....But it give the following complie error and won1. Setting the reference programmatically doesn't work as I thought i
would. I have it fixed but, until I post a revised set of macros
you'll need to do it yourself.
From the top menu in the VBE, select Tools / References and check th
entry for Microsoft Visual Basic for Applications Extensibility. Yo
can leave the code alone, as it won't run if the reference is alread
checked.
And I have done this....But it give the following complie error and wont
run
"Ambiguous name detected: sTCL"
+-------------------------------------------------------------------+
+-------------------------------------------------------------------+
Yep had another copy... once removed it worked fine...Hmmm. I did not have that problem. It means the name has been declare
(preceded by Dim or by Public) more than once.
Do you possibly have a module in the workbook containing a previou
version of the CreateCompareDataSheet macro?
OK I have downloaded both macros and imported them
Yes this would be true...You have already established that the Node Addresses may not necessaril
be consecutive, nor filled. So that one could have
Node Loops
1 3
2 0
3 5
In which case we would populate CompareData (and Summary) with the ful
range of Merge Addresses for
Node Loop
1 1 2 3
3 1 2 3 4 5
(excluding Node 2)
99% of the time the loops would be installed consecutive from 1 to 1This implies that the Loops are consecutively numbered. But I neve
confirmed with you that that was, indeed, the case.
If it is the case, we are all set. If it is not the case, then yo
should know that, as written, the code will populate the range of Merg
Addresses up to the highest numbered loop. In other words, in th
example above, even if the only loop being used in node 3 were loop 5
the range of Merge Addresses (and rows) populated would be the same.
Yes this would be true...
However in the example Node 2 could be a panel that does not use the
loops such as a remote annunciator or a voice panel...The engineer
should know that the node address has been used so that they don't try
to assign that number to a panel that does use the loops...
99% of the time the loops would be installed consecutive from 1 to 10
but there could be times when it would skip. The loops get installed
in a manner where loops 1,3,5,7,9 would be the primary and loops
2,4,6,8,10 are the expanders. So it is possible to have six loops
installed in the following manner 1,2,3,4,5,7 because loop 2 mounts on
to of loop 1 and loop 4 mounts on top of loop 3 and loops 5 and 7 have
no expanders. This example is a rare situation but it has happened.
The fact that it would create the spare mergedaddresses for loop 6 even
though its not installed would require a work around but if is
correctable then that would be even better...
THere are a few ways nodes can be present with no loops for exampleHOW can the program know that Node 2 is being used if there are no loop
selected?
Yes i did, there was two macros with the same namDid you solve the problem of the ambiguous name?
99% of the time the loops would be installed consecutive from 1 to 10
but there could be times when it would skip. The loops get installed
in a manner where loops 1,3,5,7,9 would be the primary and loops
2,4,6,8,10 are the expanders. So it is possible to have six loops
installed in the following manner 1,2,3,4,5,7 because loop 2 mounts on
to of loop 1 and loop 4 mounts on top of loop 3 and loops 5 and 7 have
no expanders. This example is a rare situation but it has happened.
The fact that it would create the spare mergedaddresses for loop 6 even
though its not installed would require a work around but if is
correctable then that would be even better...
THere are a few ways nodes can be present with no loops for example
--A Voice panel, this one gets programmed using only zones...
--A Remote Annunciator is a node that is only to display what the panel
displays, but it would be located at the main entrance of a building for
the fire department to see the status of the main panel as they enter
the building.
--A computer workstation that is used to show the status of the system
on a computer with graphics is a node
--And there are cards that can page or email when a device is in alarm
or trouble that is considered a node...All of these have no loops.
As far as how the system knows...It just complies all the node
information down the list on the PanelData spreadsheet. I know on the
voice panel it just shows the node number and uses zeros for the loop
and has the zones Z000-Z999. I will need to double check how the others
that I mentioned are shown and if they capture the zones....I will post
this later as I will have to download it to find the answer
Yes i did, there was two macros with the same name
THere are a few ways nodes can be present with no loops for example
--A Voice panel, this one gets programmed using only zones...
--A Remote Annunciator is a node that is only to display what the panel
displays, but it would be located at the main entrance of a building for
the fire department to see the status of the main panel as they enter
the building.
--A computer workstation that is used to show the status of the system
on a computer with graphics is a node
--And there are cards that can page or email when a device is in alarm
or trouble that is considered a node...All of these have no loops.
As far as how the system knows...It just complies all the node
information down the list on the PanelData spreadsheet. I know on the
voice panel it just shows the node number and uses zeros for the loop
and has the zones Z000-Z999. I will need to double check how the others
that I mentioned are shown and if they capture the zones....I will post
this later as I will have to download it to find the answer
Yes i did, there was two macros with the same name
+-------------------------------------------------------------------+
+-------------------------------------------------------------------+
This is true...I have made some assumptions with I need to check with you.
I am considering differences to be either
Changes
Deletions
Additions
The comparison is run on the SUBSET of rows (on the Summary an
CompareData sheets) that have an entry in the DeviceTypes column.
On Summary, if the engineer deletes the data in all of the columns w
allow him to edit, the DeviceTypes entry will blank.
Since CompareData is derived from PanelData, and PanelData i
downloaded from the field, I am assuming that PanelData will not includ
a DeviceType entry of 1 or 2 or 3 unless there is a Device present
If that assumption is true, then:.
1. Any row on the Summary sheet that has data in the firs
(ProjectNumber) column is "Changed"
2. Any row on the Summary sheet, where the data in the columns for
DeviceLabel
ExtendedLabel and/or
TypeCodeLabel
do not agree with the data in those columns for the row on th
CompareData sheet that has the same Merged Address is "Changed"
3. Any row on the Summary sheet which contains a MergedAddress entr
that does not exist on CompareData is an "Addition"
4. Any row on the CompareData sheet for which the MergedAddress doe
not exist on the Summary sheet is a "Deletion"
So I wind up with a list of MergedAddresses that can fall into one o
the above three categories (and can be used to lookup the rest of th
row).
How exactly would you want this information presented?
One way would be to have three tables
Added -- and give the row information from the Summary sheet
Deleted -- and give the row information from the CompareData sheet
Changed -- and I suppose for this we could output the data from bot
sheets
If we present the row information, do you want to present ALL of th
columns? Or just a subset of the columns?
The spaces represent of actual number of character the field caOne further question: On PanelData, I have noted that unpopulate
Extended Label fields are, in fact, populated with thirteen (13) spaces
Do you have any objection if, in creating the CompareData and Summar
sheets, we remove these spaces?
Also, where are you located. I'm in Maine, in the US, and your
responses usually get here in the evening local time.
4. This one is a little tricky or misleading. In one case the
PanelData/CompareData sheet could have a device not on the summary sheet
due to having to add additional devices in the field, which will
eventually be populated on the summary sheet but not until the as-built
of the drawings and update of the summary sheet. In another case the
field technician could delete more devices and the engineer expected and
thus the summary sheet would not reflect this. In this case it truly
would be a deletion that would need to be reflected on the summary
sheet... So it should have the option to update the summary sheet with
the differences found if possible
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.