VBA Copy and paste column data in multiple rows for multiple conditions

Question

Hi All,

I have worksheet where I have a below 2 main sheets :-

1. Code list : having example list of 1 codes which is required to copy for all the different IP's
2. IP list  : for different criteria.

currently I am taking example of 2 example :-

1. MW : First I go to code list and there in cell A4 I found MW codes which I needs to repeat as per given list in IP list in current case MW IP list is from A3 to last row :- 

Output :- I bold highlighted figure only be change according to number of IP's mentioned in IP list sheet and paste all the codes generated for all the IP's in MW sheet from range A13.

crt.Screen.Send "telnet 172.22.197.73" & chr(13)

crt.Screen.WaitForString "Password: "

crt.Screen.Send "rajender" & chr(13)

crt.Screen.Send "enable" & chr(13)

crt.Screen.WaitForString "Password: "

crt.Screen.Send "rajender" & chr(13)

crt.Screen.Send "show mac-address-table dynamic" & chr(13)

crt.Screen.Send "exit" & chr(13)

crt.Screen.WaitForString "[u0039853@sam " & chr(126) & "]$"

crt.Screen.Send "telnet 172.22.96.254 " & chr(13)

crt.Screen.WaitForString "Password: "

crt.Screen.Send "rajender" & chr(13)

crt.Screen.Send "enable" & chr(13)

crt.Screen.WaitForString "Password: "

crt.Screen.Send "rajender" & chr(13)

crt.Screen.Send "show mac-address-table dynamic" & chr(13)

crt.Screen.Send "exit" & chr(13)

crt.Screen.WaitForString "[u0039853@sam " & chr(126) & "]$"

and so on.............

2. Now another one I need the solution is for MPA because here multiple changes required :-

Example : In code list under MPA Dynamic : codes are mentioned which I needs to repeat for all the list which is mentioned in IP list under MPA dynamic list.

crt.Screen.Send "ssh L1PE-ANT-11" & chr(13)

crt.Screen.WaitForString "u0039853@l1pe-ant-11's password: "

crt.Screen.Send "singhnegi" & chr(13)

crt.Screen.WaitForString "<L1PE-ANT-11>"

crt.Screen.Send "scre 0 temp" & chr(13)

crt.Screen.Send "dis mac-address dynamic verbose" & chr(13)

crt.Screen.WaitForString "<L1PE-ANT-11>"

crt.Screen.WaitForString "< L1PE-ANT-11 >"

crt.Screen.Send "quit" & chr(13)

crt.Screen.WaitForString "[u0039853@sam " & chr(126) & "]$"

crt.Screen.Send "ssh L1PE-BOR-12" & chr(13)

crt.Screen.WaitForString "u0039853@l1pe-ant-11's password: "

crt.Screen.Send "singhnegi" & chr(13)

crt.Screen.WaitForString "<L1PE-BOR-12>"

crt.Screen.Send "scre 0 temp" & chr(13)

crt.Screen.Send "dis mac-address dynamic verbose" & chr(13)

crt.Screen.WaitForString "<L1PE-BOR-12>"

crt.Screen.WaitForString "< L1PE-BOR-12 >"

crt.Screen.Send "quit" & chr(13)

crt.Screen.WaitForString "[u0039853@sam " & chr(126) & "]$"

and so on................ and paste in sheet MPA dynamic.

Below is the link to sample file

https://onedrive.live.com/redir?resid=393CD98CEE2A349F%21128

Thanks in advance for your valuable assistance.

Regards,

Rajender

Answer 1

Ok, I'll work from _TEST3 - I've actually got the code in it now to generate the text files.  What 'file extension' do you want on them:  .txt  .vbs  .dat or something else?

And I'll change where all of the erasing of old data is at in the code and put it ahead of things that rebuild the code.  So it would erase all old and then rebuild, and only those sheets with IPs listed would be rebuilt.

_TEST4 should do all of that - other than having a file output that ends with .txt.

If you want to change that - everything in the code that controls most of what it does is defined as Const values at the start of the code module.  The last entry in that section is

Const fileTypeIndicator = ".txt"

just change the txt to whatever you want it to be if it is something else.

Here is link to the new file

http://www.mediafire.com/download/r3vcxokdige9stx/Cartography_Report_Code_Generator_TEST4.xlsb

Answer 2

[Edit] - actually you might start at the bottom - I think you'll prefer _TEST3 over _TEST and _TEST2

Download this file and try it out.  You can look at the various sheets in it now to see what the output looks like.

It rebuilds the sheets beginning at row 14 on each one - anything in rows 1 through 13 on them is left as is.  It also adds "End Sub" at the bottom of it all.

I've added a 'General Use Notes' sheet with some comments to help you.  I also left the old [Code] sheet in it, but renamed it to [Code OLD] and the new multi-column format [Code] sheet is what is actually used to generate the script on the other sheets.  You will notice I shaded the tabs on [Code] and [IP List] red as a reminder that you cannot change the name of those two sheets without changing the code also.

Verify that it works properly before committing it to production work.  I have digitally signed the code, so you can verify its source.  If you or anyone else alters the code, then the digital signature is removed.  Not saying you cannot alter it, just saying that initially you have a 'guarantee' of the source of the code.

http://www.mediafire.com/download/59oqx8deeoc188i/Cartography_Report_Code_Generator_TEST.xlsb

This copy of the file (same code is used) shows that you do not have to leave empty columns on the [Code] sheet - so which ever format for those entries that you are most comfortable with is fine.  The 'telnet' entries only have 1 entry that uses 3 columns (the very first one), while the ssh entries have several.

http://www.mediafire.com/download/8mgnrl77qhnaeeb/Cartography_Report_Code_Generator_TEST2.xlsb

Now - the heck of it is that I've actually just now thought of how we could have done the whole thing with your original structure! I'll work up a version that works that way also and post it in a day or so.  Imagine where you have

some text 123.12.55.253 more text

entries for a script code line what if you wrote those like this

some text IP HERE more text

and for the ssh entries where the lowercase stuff is needed

some text LCASE IP HERE more text

or we could use something like *IP* and *LIP* to keep the typing to a minimum, while still keeping those identifiers unique enough to be detected in the processing.

OK - here is that version, and I'm betting that this is the one you will prefer to use, much simpler for you and the [Code] sheet entries are in formats more familiar to you.

http://www.mediafire.com/download/vgksw1weya88aov/Cartography_Report_Code_Generator_TEST3.xlsb

Answer 3

QUESTION:  In your sample workbook, for the two ssh entries, you have exactly the same thing repeated in columns H and I:

crt.Screen.WaitForString "<L1PE-ANT-11>"

Does that actually need to be in the final result twice in a row?

Answer 4

Hi Latham,

Thanks a lot for your valuable suggestion:-

I format I attached in the excel file I roughly created , we can modify the same. but the output should be the same as below because after generating this list of codes I need to put in another tool in order to extract some data :-

for MW :-

    crt.Screen.Send "telnet 172.22.197.73" & chr(13)

    crt.Screen.WaitForString "Password: "

    crt.Screen.Send "ericsson" & chr(13)    

    crt.Screen.Send "enable" & chr(13)

    crt.Screen.WaitForString "Password: "

    crt.Screen.Send "ericsson" & chr(13)    

    crt.Screen.Send "show mac-address-table dynamic" & chr(13)

    crt.Screen.Send "show running-config 24" & chr(13)    

    crt.Screen.Send "exit" & chr(13)

    crt.Screen.WaitForString "[u0039853@sam " & chr(126) & "]$"

and so on for all the rest of IP's

Similar for MPA :-

    crt.Screen.Send "ssh L1PE-ANT-11" & chr(13)

    crt.Screen.WaitForString "u0039853@l1pe-ant-11's password: "

    crt.Screen.Send "Mobistar2001" & chr(13)

    crt.Screen.WaitForString "<L1PE-ANT-11>"

    crt.Screen.Send "scre 0 temp" & chr(13)

    crt.Screen.Send "dis mac-address dynamic verbose" & chr(13)

    crt.Screen.WaitForString "<L1PE-ANT-11>"

    crt.Screen.Send "dis interface description" & chr(13)

    crt.Screen.WaitForString "<L1PE-ANT-11>"

    crt.Screen.Send "quit" & chr(13)

    crt.Screen.WaitForString "[u0039853@sam " & chr(126) & "]$"

and so on for rest of IP's ....

Thanks in advance for the assistance :-)

Regards,

Rajender

Answer 5

I think I understand what you are asking for.  I am wondering if in a final product if we might change the contents of the [Code] sheet?  I think it would improve performance and help make certain that the 'created' code for the other sheets is correct.

This change would involve the current columns that have information to be replaced, as columns B and for ssh type entries, columns C, H and I.

What we would do is break each of those single columns up into 3 columns:  first column would contain the code up to and including the beginning of the information to be replaced, the second column would have some keyword(s) such as USE IP INFO to let the code know that that information needs to be inserted at this point, then the 3rd new column would hold the remaining portion of that code line entry.  For these extra columns that are not used (as in C, H and I for non-ssh entries) you would just leave them empty.

There might be another change to make to help things along.  Right now each column from B on out represents a single line of code to be generated.  But if we split them up as I suggest above, then we are probably going to need to provide a "start of new line" marker of some type at the first column of these potential multi-column entries, and for each of the others.  But maybe not - might be able to distinguish this information based on whether the entry in column B contains "ssh ..." or if it contains "telnet ..." It's a work in progress you know.  But definitely breaking up the columns like I mentioned above would be of great help.