Editing and saving in Notepad while controlling macro in Excel

[pearlple_khim]

Hi,

I'm new here and honestly not an expert on this. But I need help...

I need a macro for my weekly task. I have already crated a vba syntax up to MS Word. Unfortunately, I forgot to save it at Notepad, which results to an error whenever I load the file into our system. Therefore, I need an entire syntax from Step 5 to 7. BTW I'd like to control it at Excel.

1. Copy entire column at MS Excel
2. Paste as unformatted text at MS Word
3. Change font to "Courier New"
4. Replaced all "|" letters with "^s"
Note: "^s" represents "space" I think
5. Copy all from MS Word to Notepad
6. Delete the first line in Notepad
7. Save as "P2.DAT" file

Hope you can help me since it takes me about more than an hour weekly just to run this task.

Thanks,
pearlple

 

[Blade Hunter]

Out of curiousity, why are you changing the font if you are copying to Notepad?

Why not stream out to a text file direct from Excel? You could do the substitute "|" for "^s" on the way out.

 

[Ruddles]

Also, do you still need the Word file or do you just want it in Notepad format?

And if you want us to modify your code, you're going to have to show it to us otherwise the variables we use aren't going to have the same names as yours.

 

[Jon von der Heyden]

Hello

Why not give Richard Schollars swanky WriteText and MakeText functions a go.

There is little amendment required. All you need to do is replace "|" with chr(32) and save it as a DAT rather than CSV.

 

[pearlple_khim]

Hi,

I have tried directly copying the data from Excel to notepad then replaced "|" with "space". Unfortunately, our system still cannot read it and I don’t have any option but to replaced it in Word. If you have any idea how does "^s" is different with "space" then I guess it could do the trick.

Here is my sample data:

Desktop Procedure
Excel:

• Filter out Column J (AA) with non-blanks
• Copy entire Column J data

Word:

• Paste to Word as unformatted text
• Replaced all "|" with "^s"
• 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
• 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

I know it's too much but I really need help and most of my colleagues is not a VBA user at all.

 

[pearlple_khim]

I also tried replacing the "|" with with "non-breaking space" in the Excel formula. But still the system cannot read it. I have no other option but to edit it in Word first then save it in Notepad. Help! I only use recorded macros from Excel and Word.