Search Text file for cell value then replace regex match in text file from cell value 7 columns over from first match

[meirbabboon]

I am attempting to use a macro to open a text file and search for a match in column d then when the match is found look back in the text file for the MAC address and replace it with the cell value 7 columns over from first match. I will post examples of what I am trying to do.

Step 1. look at active sheet range D3:D13
Step 2. find hostname (range D3:D13 of excel file) in text file
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column D - Hostnames[/TD]
[TD]Column K - MAC Addresses[/TD]
[/TR]
[TR]
[TD]ST999WAP200[/TD]
[TD]74-E0-A9-0B-68-31[/TD]
[/TR]
[TR]
[TD]ST999WAP201[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ST999WAP100[/TD]
[TD]DC-72-E5-E5-09-0C[/TD]
[/TR]
[TR]
[TD]ST999WAP101[/TD]
[TD]A2-E9-87-03-3F-9E[/TD]
[/TR]
[TR]
[TD]ST999WAP102[/TD]
[TD]69-E1-76-FC-B7-4E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Step 3. Replace the generic MAC (AA-BB-CC-DD-EE-FF) in text file with the matching row (column 7) from hostname search

ap6522 AA-BB-CC-DD-EE-FF
use profile ap6522
use rf-domain st999
hostname st999wap200
ip default-gateway 1.1.1.1
interface vlan1
ip address 1.1.1.2/22


ap6522 AA-BB-CC-DD-EE-FF
use profile ap6522
use rf-domain st999
hostname st999wap100
ip default-gateway 1.1.1.1
interface vlan1
ip address 1.1.1.3/22

ap6522 AA-BB-CC-DD-EE-FF
use profile ap6522
use rf-domain st999
hostname st999wap101
ip default-gateway 1.1.1.1
interface vlan1
ip address 1.1.1.4/22

ap6522 AA-BB-CC-DD-EE-FF
use profile ap6522
use rf-domain st999
hostname st999wap102
ip default-gateway 1.1.1.1
interface vlan1
ip address 1.1.1.5/22

Step 4. Save Text file with replacements

Any assistance would be greatly appreciated. If this could be a button that I can add to the sheet that would be great.

 

[meirbabboon]

Bump