Editing and saving in Notepad while controlling macro in Excel

P

pearlple_khim

New Member
Joined
May 30, 2011
Messages
5
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
 
Last edited:

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
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.
 
Upvote 0
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.
 
Upvote 0
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:

samplez.jpg


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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Opening a non TXT with Notepad in excel
Replies
8
Views
590
LS2021
L
K
  • Question
Excel Macro to Save to .pdf not working
Replies
9
Views
301
kpost
K
N
  • Question
Excel Crashes when I Save Any File
Replies
4
Views
207
nolass
N
C
  • Question
Change from Save As Excel file to Save As PDF
Replies
1
Views
533
bobsan42
bobsan42
B
  • Solved
Barcode Scan result is errant in MS Excel, good data in Notepad/G Sheets
Replies
2
Views
2K
bmoreskimore
B

Forum statistics

Threads
1,215,064
Messages
6,122,936
Members
449,094
Latest member
teemeren

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top