I am trying to run a script to pull information out of the attached spreadsheet and put it into the correct format. Here are the steps I am looking to perform:

1.) Do error-checking for special characters in the column "MAC Addr" before converting
2.) Copy of the information in the column "MAC Addr" over to the "Notes" column
3.) Convert the new "Notes" column field to the the proper MAC Address format - "ab:cd:ef:gh:ij:kl" (all lower case and colon ever 2 characters)
4.) Output the text into the following format:


provision-ap copy-provisioning-params ap-name "«MAC_Addr»"
provision-ap installation default
provision-ap no external-antenna


provision-ap server-name aruba-master
provision-ap ap-group "«AP_Group»"
provision-ap ap-name "«AP_Name»"
provision-ap no syslocation
provision-ap no remote-ap
provision-ap no fqln
provision-ap reprovision ap-name "«MAC_Addr»"


Let me know is this is something anyone can assist me with and I will send over an example of the file.

You say "spreadsheet", but your title is "Word table". Which is it?

Its a table in a word document. Sorry for the confusion

Shane: Your original post in this thread refers to "the attached spreadsheet", but nothing is attached. Consequently, although you tell us what the desired output format is, we have no idea what the input data are...

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Sorry for the delayed response. I attached a copy of an example document that I would need to run this script against. Please let me know if you need any additional information from my end. Thanks.

-Shane

Attached Files

Sample AP Inventory.doc (168.0 KB, 22 views)

OK, I've had a look at the data, but I don't see anything I would recognise as 'special characters in the column "MAC Addr"'. Please clarify. It's also not apparent where the following output data come from or to which of the first & last lines the "MAC Addr" in the table applies:

provision-ap copy-provisioning-params ap-name "«MAC_Addr»"
provision-ap installation default
provision-ap no external-antenna
provision-ap server-name aruba-master
provision-ap no syslocation
provision-ap no remote-ap
provision-ap no fqln
provision-ap reprovision ap-name "«MAC_Addr»"

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

There are not any special characters in the example I sent, but we have come across instances where someone will accidentally type one of them (!@#$%^&*(). It does not happen very often and it is easy to fix so that part of the program is more of a wish list item.

The "MAC_Addr" field in the output is "MAC Addr" in the Wrod Doc table which is also column 5. It is just without the "_".

Thanks again for the help!

OK, but what about the rest of the stuff I queried?

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I have had to zip the attached document because it contains macros.

I think the VBA macros solve your problem. The Green Arrow icon on the Quick Access toolbar runs the relevant macro, CopyValidMACAddresses().

The macro tests the mac addresses to ensure that they are 12 characters long and consist entirely of A-Z, a-z or 0-9.

Hope this is what you wanted.

Attached Files

Sample AP Inventory.zip (45.5 KB, 14 views)

@macroprod: I am not sure what you are asking? What other information are you referring to? Was there another part of your question I did not answer in me previous post?

@Geoff G: Thank you for putting that together. Its def a good start to the first part of the script. The one addition that needs to be made is the letters need to be converted to lower case as a part of the conversion.

The second part of that program is output that converted information into the format I described above. Please let me know if you need any further explanation from my end.

Thank you both for your help with this!

The attached zip file contains a slightly revised VBA macro that will additionally convert the MAC address to lower case. (Seems a strange request, given that MAC addresses can contain upper and lower case characters and numerals, 0-9 --- but no doubt you have your reasons.)

You need to be specific about the format you want the data converted to, as I do not understand the following:

provision-ap copy-provisioning-params ap-name "«MAC_Addr»"
provision-ap installation default
provision-ap no external-antenna
provision-ap server-name aruba-master
provision-ap no syslocation
provision-ap no remote-ap
provision-ap no fqln
provision-ap reprovision ap-name "«MAC_Addr»"

Attached Files

Sample AP Inventory.zip (46.9 KB, 10 views)

You don't seem to have addressed this part:
I see Geoff_G has now queried the same aspects of the output.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thank you for fixing the first part of that script Geoff G. It works great so far.

My apologies to both of you for not explaining this better. The table in the Word Doc contains the following 7 columns:

UserID, AP Name, AP Group, Model, Mounting, MAC Addr, Notes

These columns are where the output pulls the information from. The items in the output in " " need to be pulled from the column tables. Here is what the output template looks like:

provision-ap copy-provisioning-params ap-name "«MAC_Addr»"
provision-ap installation default
provision-ap no external-antenna
provision-ap server-name aruba-master
provision-ap ap-group "«AP_Group»"
provision-ap ap-name "«AP_Name»"
provision-ap no syslocation
provision-ap no remote-ap
provision-ap no fqln
provision-ap reprovision ap-name "«MAC_Addr»"

Here is an example of the first two rows:

provision-ap copy-provisioning-params ap-name 9c:1c:12:c5:a6:c8
provision-ap installation default
provision-ap no external-antenna
provision-ap server-name aruba-master
provision-ap ap-group PollockLocal2-PollockHalls
provision-ap ap-name UP.Beaver-AP003
provision-ap no syslocation
provision-ap no remote-ap
provision-ap no fqln
provision-ap reprovision ap-name 9c:1c:12:c5:a6:c8


provision-ap copy-provisioning-params ap-name 9c:1c:12:c5:a0:78
provision-ap installation default
provision-ap no external-antenna
provision-ap server-name aruba-master
provision-ap ap-group PollockLocal2-PollockHalls
provision-ap ap-name UP.Beaver-AP004
provision-ap no syslocation
provision-ap no remote-ap
provision-ap no fqln
provision-ap reprovision ap-name 9c:1c:12:c5:a0:78

I would need the script to create an output like the ones above for each row in the table. Some of these tables can easily be a couple hundred rows.

Thank you both again very much for your help!

I am not sure the attached is what you want, but have a look.

Data in your table is output to a new Word document in the format you describe.

If a row in your table does not contain a valid MAC address in the Notes column, that row is skipped in the output document.

The number at the end of line 6 is increased by one for each block of text. If a row is skipped in your table, a number is NOT skipped in the output file. That can be changed if required.

If the format is wrong, please say what you need.

Attached Files

Sample AP Inventory.zip (53.5 KB, 18 views)

On reviewing your table, I have realised that you want the AP Name and AP Group taken from columns 2 and 3. (At least, I think you do.)

The VBA macros in the attached document do just that.

Incidentally, there was a programming error in the error handler in the previous versions I posted: I put Err.No instead of Error.Number. I have corrected that in the attached version.

Are the macros now doing what you want?

Attached Files

Sample AP Inventory.zip (52.9 KB, 24 views)