MS Word VBA "^M" problem 2

[cosmoh2o]

I am building a MS Word template that has some macros that will create a file and export it to our server (Unix). Everything works fine except when we try to import the newly created comma-separated text file (created and exported by the Word macros) into our database we get an error because attached to the newly created file (called "default.csv&quot are little "^M"s at the end of everyline. My question is this: how can I get rid of the "^M"s before the file is exported to the server?

Thank you to anyone that can help. I am a newbie when it comes to MS Word macros and only know a little VBA.

 

[dougcranston]

point,

Your seeing a Carriage Return (with presumably no Line Feed.)

Details are:
Char Dec Hex Octal HTML Description / Notes
^M 13 0x0d 0015 ^M CR carriage return [\r]
Source:

http://www.idautomation.com/ascii-table.html

A Linefeed is:
Char Dec Hex Octal HTML Description / Notes
^J 10 0x0a 0012 ^J LF line feed [\n]

Assumptions here:
------------------
My guess is your getting your information from a UNIX based system.

Since your at this site, your using MS OS of some sort.

If Unix, depending on your FTP software should/may have an option to create the file with the layout MS looks for as opposed to what UNIX needs.

Depending on the size of the file, try opening a "COPY" of the input file in NOTEPAD and saving it. Or some other text editor. That may convert from a CR to a CR/LF. In the way distant past in a former life we saw similar problems with files d'loaded from IBM mainframes. There we ended up creating or d'loading a command-line filter to do it in a streaming fashion.

Hope this helps.
DougCranston

 

[dougcranston]

cosmoh2o,

Sorry, but I am real tired and mistyped your alias.

DougCranston

 

[cosmoh2o]

Thanks for the reply. That is useful information. However, I was wondering if there was a way to remove the ^M from the file using VBA before it is exported to the UNIX system. The program is written for some users at our warehouse and are not very technically proficient. If you have any ideas, please let me know. Once again, thank you for your help.

 

[cosmoh2o]

Sorry, I should specify more. I would like to remove only the last ^M that is printed to the file before it is exported to our UNIX system. The reason is, when the database tries to import the file the ^M by itself causes a problem (throws an error).

for example:
"200304100856","04/10/04 UPS","45632","0.00","Y"^M
^M

The last ^M is what causes the problem. Thanks to anyone who can solve this problem.

 

[dougcranston]

cosmoh2o,

If it is TRULY an embedded ^M as opposed to the caret char and the capital M, then this should work:

Function NoCR(oldStr As String)
newStr = ""
For i = 1 To Len(oldStr)
If Asc(Mid(oldStr, i, 1)) <> 10 Then
newStr = newStr & Mid(oldStr, i, 1)
End If
Next
NoCR = newStr
End Function

Hope this helps.
DougCranston

 

[dougcranston]

Cosmoh2o,

Just saw your latest. Have to defer to someone else more adept with VB than I am.

Sorry can't be of any more help.
DougCranston

 

[cosmoh2o]

What about using the Replacement object? For example:

With ActiveDocument.Content.Find
.Text = &quot;^M&quot;
.Replacement.Text = &quot;&quot;
.Forward = True
.Wrap = wdFindContinue
.Execute Replace := wdReplaceAll
End With

However, I believe that the above code may replace ALL ^M's when I only want the last one removed. I am inexperienced with VBA (usually program in PERL or Java). If anyone has ideas, please help. Thanks for all your help dougcranston.

 

[TonyJollans]

Hi cosmoh2o,

If you just want to work with the last character in the document you should be able to select it with ..

ActiveDocument.Characters(ActiveDocument.Characters.Count).Select

You can then use

Selection.Find etc.

to change it, or

Selection.Delete

to delete it, or whatever you want.

You might have to experiment a little as I found I had to select the character before last to make it work with my random text (I guess the last is a paragraph mark which you may not have).

Enjoy,
Tony

 

[cosmoh2o]

Thank you TonyJollans. I will give it a try.

 

[cosmoh2o]

TonyJollans,

Could you please supply some sample code to perform the removing of the last ^M? I know it is probably trivial but like I said I am a MS Word VBA newbie. Thanks again for all your help.

 

[TonyJollans]

cosmoh2o,

I just reread some of the thread and I'm not sure whether you actually want the last character of the document or not. Suggest you do what you want in Word and record a macro while you do it.

Tools > Macro > Record New Macro, just press Enter to take the defaults

Now do what you want, which is something like
<Ctrl><End> (to go to the end of the document)
<Ctrl><H> (to bring up the Replace Window)
Enter Find and Replace texts
If &quot;More&quot; is shown on a button, press it, if &quot;Less&quot; is shown, you're OK.
Select &quot;Up&quot; in the Search combobox
Press &quot;Find Next&quot; to get to the last occurrence
Press &quot;Replace&quot; to replace it.
Press &quot;Close&quot; to close the window

Now stop recording - press the saquare on the Stop Recording Toolbar or do Tools > Macros > Stop Recording

Now go and look at the code. I just did it and got the following:

Sub Macro7()
'
' Macro7 Macro
' Macro recorded 11/04/03 by Tony Jollans
'
    Selection.EndKey Unit:=wdStory
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = "b"
        .Replacement.Text = "zz"
        .Forward = False
        .Wrap = wdFindAsk
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute
    With Selection
        If .Find.Forward = True Then
            .Collapse Direction:=wdCollapseStart
        Else
            .Collapse Direction:=wdCollapseEnd
        End If
        .Find.Execute Replace:=wdReplaceOne
        If .Find.Forward = True Then
            .Collapse Direction:=wdCollapseEnd
        Else
            .Collapse Direction:=wdCollapseStart
        End If
        .Find.Execute
    End With
End Sub

I used my own characters which I had in my text - you will need to replace them with yours. I did notice that ^M was not considered valid by my Word 2000, so I'm not exactly sure what you'll to do with that.

Enjoy,
Tony

 

[cosmoh2o]

Thanks to everybody for the help. It worked GREAT! You guys really know your stuff.

 

[dougcranston]

cosmoh2o

Just a suggestion. If you liked the work that Tony did and it helped you, you might want to consider awarding him a star &quot;*&quot; if it did assist you. It appeared to be thorough, and to the point.. imho ;-)

Just a thought. Particularly due to the time elapsed between his work and your response.

For whats its worth.
DougCranston

 

[cosmoh2o]

dougcranston,

You are right. You both should get a star. I appologize for not responding sooner...my job keeps me so busy

 

[dougcranston]

cosmoh2o

Appreciate the star, but Tony's the one that provided you a detailed example.

In any event. Thanks.
DougCranston

 

[TonyJollans]

Hi cosmoh2o,

Glad it worked for you and thanks for coming back and telling us so - so often we don't even know if answers are read! And thanks for the star, too.

Enjoy,
Tony