Hi,

I have a weekly task that involves copying data in Excel, editing it in Word and pasting and saving it on Notepad. Basically I want a macro that will be controlled in Excel. I used recorded macro only so I don't have much code to post anyway.

Some of you may ask why don't I ditch the Word and do all the editing and saving in Notepad. Sadly our system is very sensitive that I have already tried copying the data from Excel to Notepad then editing and saving it there and/or editing the file in Excel first then pasting and saving it in Notepad. Those two didn't work unfortunately so I have no option but to use the old process.

Attached is my sample data:
SAMPLE 1.xls

Desktop Procedure
Excel:
  • Filter out Column J (AA) with non-blanks

  • Copy entire Column J data


Word:
  • Paste to Word as unformatted text

  • Change font to Courier New (don't ask me why, this has been in the process for so long)

  • Replaced all "|" with "^s" (non-breaking space)

  • Copy entire data


Notepad:
  • Paste the data

  • Delete the first line (the heading on the Excel: "AA", "BB" or "CC"), making the first line blank

  • Pop-up window to prompt the user to typed in his/her ID NAME

  • If the user typed the wrong ID NAME the whole application will be aborted or ask to retype the ID NAME again

  • Save as "C:\Documents and Settings\*ID NAME*\Desktop\DAT\*Folder date created*\"P2*Heading on the excel it was copied ("AA", "BB" or "CC")*.DAT" file in a specific folder


  • Loop the whole procedure until next column is blank

  • Close Word and Notepad when done