PDA

View Full Version : Editing in Word and saving in Notepad while controlling everything in Excel



pearlplekhim
06-26-2011, 06:27 PM
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:
6191

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