Macro to copy file, replace a specific row of data and save as new file...

[enajh2]

Hello,

I am trying to create a macro that will modify a specific line within a .prodxml file (for use with Vismockup for viewing CAD data) and then save the resulting code as a new file.

I have been unable to perform this action within Excel and export the file because the file I am trying to edit has more than 65000 rows of data.

I would like the macro to...

1) Look in directory C:\Temp\ for "1104D_HIGH_LEVEL_EBOM_HAZELL.plmxml"

2) Open this file and search for the row that begins with text
ProductRevisionView id="id10" and replace it with the contents of a specific cell in my spreadsheet i.e. NewCode = Sheets("XML").Cells(6, 2) which will take the form "
ProductRevisionView id="id10" name="1104D_TOP_LEVEL_EBOM_HAZELL/A (view)" instanceRefs="id17 id2748 id4550 id5715 id6727 id8034"

The row to be replaced will always be the same i.e. I am using the file as a template, but the row contains 500+ id numbers within the instanceRefs argument. ProductRevisionView id="id10" only appears on one row.

3) Save the modified file as "ListNumber".prodxml (in C:\Temp) where ListNumber = Sheets("Front end WIP").Cells(6, 2)
Hopefully someone with the knowhow to do this can write the basic structure and I can finesse it afterwards.

Many thanks in advance,

Alan Hazell

 

[pbornemeier]

I would download the TextPad editor (http://www.textpad.com/) and use its macro capability to do the replacement. It is quite capable of handling very large text files.