Removing the last empty line (Return) in a text file (CSV format)

[Jac Tremblay]

Hi
I work from Excel X
I create text files in CSV format. These files are to be loaded in tables on a central mainframe computer
Some of the code I use is
' ************************
Application.DisplayAlerts = Fals
ActiveWorkbook.SaveAs Filename:=strPath &
strTableName & ".DAT",
FileFormat:=xlCSV, CreateBackup:=False, Local:=Tru
Application.DisplayAlerts = Tru
' ************************
When I open one of these files with Notepad, there is an extra empty line at the end that must be removed manually before the mainframe loading operation because the process aborts. To remove this extra line, one must place the cursor at the end of the file, press Backspace and save and close the file. Then everything is ready for the mainframe loading operation
I thought I could automate this process with some code but nothing seems to work. I tried many solutions
I tried to open the file with Open ... For Input and then transfer the lines in a new file (after truncating the last character). Here is some code I used
' *********************************************************************
Sub TestWithOpen(
' Jac Tremblay 2004-04-1
Dim strPathDat As Strin
Dim strNameDat As Strin
Dim strNameDat2 As Strin

Dim strLineText As Strin
Dim intI As Intege
Dim intJ As Intege
Dim strLine() As Strin

strPathDat = "C:\DaProd\
strNameDat = "SCAT0107.DAT
strNameDat2 = "SCAT0199.DAT

Open strPathDat & strNameDat For Input As #
Open strPathDat & strNameDat2 For Output As #
intI =

Do While Not EOF(1
Line Input #1, strLineTex
intI = intI +
ReDim Preserve strLine(intI
strLine(intI) = strLineTex
Loo

For intJ = 1 To int
strLineText = strLine(intJ
If intJ = intI The
strLineText = Mid(strLineText, 1,
Len(strLineText) - 1
End I
Print #2, strLineTex
Next int

Close #
Close #

End Su
' *********************************************************************
This does not work because the last line is stripped of a good character. It is not the extra Return that is removed
I then tried to use Word automation. Here is how I did it
' *********************************************************************
Sub TestWithWord1(
' Jac Tremblay 2004-04-1
' (Try to) delete the last line (Return) in a text file

Dim oWord As Word.Applicatio
Dim oDoc As Word.Documen
Dim booWordRunning As Boolea

Dim strPathDat As Strin
Dim strNameDat As Strin

strPathDat = "C:\DaProd\
strNameDat = "SCAT0107.DAT

booWordRunning = IsWordRunning(
If booWordRunning The
Set oWord = GetObject(, "Word.Application"
Els
Set oWord = CreateObject("Word.Application"
End I

oWord.Visible = Tru
Set oDoc = oWord.Documents.Open(strPathDat & strNameDat
oDoc.Activat
With oDoc.ActiveWindo
.Selection.EndKey Unit:=wdStor
.Selection.TypeBackspac
' I tried..
' .Selection.Delete Unit:=wdCharacter, Count:=
End Wit
oDoc.Close SaveChanges:=Tru

If Not booWordRunning Then oWord.Qui
Set oDoc = Nothin
Set oWord = Nothin
End Su
' *********************************************************************
It does not work either, Word always adds an extra paragraph mark at the end of the file (as well as at the end of each paragraph)
I tried another thing with Word. Here is how it goes
' *********************************************************************
Sub TestWithWord2(
' Jac Tremblay 2004-04-1
' (Try to) delete the last line (Return) in a text file

Dim oWord As Word.Applicatio
Dim oDoc As Word.Documen
Dim booWordRunning As Boolea

Dim strPathDat As Strin
Dim strNameDat As Strin
Dim intNbParag As Intege
Dim intI As Intege
Dim strParagText As Strin
Dim intNbCar As Intege
Dim intNbCarReturn As Intege
Dim rngRange As Rang
Dim strReturn As String

strPathDat = "C:\DaProd\"
strNameDat = "SCAT0107.DAT"

booWordRunning = IsWordRunning()
If booWordRunning Then
Set oWord = GetObject(, "Word.Application")
Else
Set oWord = CreateObject("Word.Application")
End If

oWord.Visible = True
Set oDoc = oWord.Documents.Open(strPathDat & strNameDat)
oDoc.Activate
intNbParag = oDoc.Paragraphs.Count
oDoc.Paragraphs(intNbParag).Range.Select
strParagText = oDoc.Paragraphs(intNbParag).Range.Text
strReturn = Right(strParagText, 1)
If strReturn = vbCr Or strReturn = vbLf Or strReturn = vbCrLf Then
strParagText = Mid(strParagText, 1, Len(strParagText) - 1)
oDoc.ActiveWindow.Selection.TypeText Text:=strParagText
oDoc.Close SaveChanges:=True

' I tried...
' oDoc.ActiveWindow.Selection.Collapse Direction:=wdCollapseEnd
' oDoc.ActiveWindow.Selection.MoveRight Unit:=wdCharacter, _
' Count:=1, Extend:=wdExtend
' Selection.MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend
' Selection.Delete Unit:=wdCharacter, Count:=1

End If

If Not booWordRunning Then oWord.Quit
Set oDoc = Nothing
Set oWord = Nothing
End Sub
' **********************************************************************
It does not work either.
I thought that I might try to save the file with an extra parameter but cannot find any that will do the job.
What else can I do?
I think that this question is for an Excel MVP.
Thanks to anyone that can help.

 

[Bernie Deitrick]

Jac,

Try writing the file from the worksheet rather than using the SaveAs. There
are plenty of code samples in the newsgroups, or try Chip's page at

http://www.cpearson.com/excel/imptext.htm

Look for the "Exporting To Text Files" section.

HTH,
Bernie
MS Excel MVP

Hi.
I work from Excel XP
I create text files in CSV format. These files are to be loaded in tables

on a central mainframe computer.

Some of the code I use is:
' *************************
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=strPath & _
strTableName & ".DAT", _
FileFormat:=xlCSV, CreateBackup:=False, Local:=True
Application.DisplayAlerts = True
' *************************
When I open one of these files with Notepad, there is an extra empty line

at the end that must be removed manually before the mainframe loading
operation because the process aborts. To remove this extra line, one must
place the cursor at the end of the file, press Backspace and save and close
the file. Then everything is ready for the mainframe loading operation.

I thought I could automate this process with some code but nothing seems

to work. I tried many solutions.

I tried to open the file with Open ... For Input and then transfer the

lines in a new file (after truncating the last character). Here is some code
I used:

' **********************************************************************
Sub TestWithOpen()
' Jac Tremblay 2004-04-12
Dim strPathDat As String
Dim strNameDat As String
Dim strNameDat2 As String

Dim strLineText As String
Dim intI As Integer
Dim intJ As Integer
Dim strLine() As String

strPathDat = "C:\DaProd\"
strNameDat = "SCAT0107.DAT"
strNameDat2 = "SCAT0199.DAT"

Open strPathDat & strNameDat For Input As #1
Open strPathDat & strNameDat2 For Output As #2
intI = 0

Do While Not EOF(1)
Line Input #1, strLineText
intI = intI + 1
ReDim Preserve strLine(intI)
strLine(intI) = strLineText
Loop

For intJ = 1 To intI
strLineText = strLine(intJ)
If intJ = intI Then
strLineText = Mid(strLineText, 1, _
Len(strLineText) - 1)
End If
Print #2, strLineText
Next intJ

Close #1
Close #2

End Sub
' **********************************************************************
This does not work because the last line is stripped of a good character.

It is not the extra Return that is removed.

I then tried to use Word automation. Here is how I did it:
' **********************************************************************
Sub TestWithWord1()
' Jac Tremblay 2004-04-12
' (Try to) delete the last line (Return) in a text file.

Dim oWord As Word.Application
Dim oDoc As Word.Document
Dim booWordRunning As Boolean

Dim strPathDat As String
Dim strNameDat As String

strPathDat = "C:\DaProd\"
strNameDat = "SCAT0107.DAT"

booWordRunning = IsWordRunning()
If booWordRunning Then
Set oWord = GetObject(, "Word.Application")
Else
Set oWord = CreateObject("Word.Application")
End If

oWord.Visible = True
Set oDoc = oWord.Documents.Open(strPathDat & strNameDat)
oDoc.Activate
With oDoc.ActiveWindow
.Selection.EndKey Unit:=wdStory
.Selection.TypeBackspace
' I tried...
' .Selection.Delete Unit:=wdCharacter, Count:=1
End With
oDoc.Close SaveChanges:=True

If Not booWordRunning Then oWord.Quit
Set oDoc = Nothing
Set oWord = Nothing
End Sub
' **********************************************************************
It does not work either, Word always adds an extra paragraph mark at the

end of the file (as well as at the end of each paragraph).

I tried another thing with Word. Here is how it goes:
' **********************************************************************
Sub TestWithWord2()
' Jac Tremblay 2004-04-12
' (Try to) delete the last line (Return) in a text file.

Dim oWord As Word.Application
Dim oDoc As Word.Document
Dim booWordRunning As Boolean

Dim strPathDat As String
Dim strNameDat As String
Dim intNbParag As Integer
Dim intI As Integer
Dim strParagText As String
Dim intNbCar As Integer
Dim intNbCarReturn As Integer
Dim rngRange As Range
Dim strReturn As String

strPathDat = "C:\DaProd\"
strNameDat = "SCAT0107.DAT"

booWordRunning = IsWordRunning()
If booWordRunning Then
Set oWord = GetObject(, "Word.Application")
Else
Set oWord = CreateObject("Word.Application")
End If

oWord.Visible = True
Set oDoc = oWord.Documents.Open(strPathDat & strNameDat)
oDoc.Activate
intNbParag = oDoc.Paragraphs.Count
oDoc.Paragraphs(intNbParag).Range.Select
strParagText = oDoc.Paragraphs(intNbParag).Range.Text
strReturn = Right(strParagText, 1)
If strReturn = vbCr Or strReturn = vbLf Or strReturn = vbCrLf Then
strParagText = Mid(strParagText, 1, Len(strParagText) - 1)
oDoc.ActiveWindow.Selection.TypeText Text:=strParagText
oDoc.Close SaveChanges:=True

' I tried...
' oDoc.ActiveWindow.Selection.Collapse Direction:=wdCollapseEnd
' oDoc.ActiveWindow.Selection.MoveRight Unit:=wdCharacter, _
' Count:=1, Extend:=wdExtend
' Selection.MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend
' Selection.Delete Unit:=wdCharacter, Count:=1

End If

If Not booWordRunning Then oWord.Quit
Set oDoc = Nothing
Set oWord = Nothing
End Sub
' **********************************************************************
It does not work either.
I thought that I might try to save the file with an extra parameter but

cannot find any that will do the job.

 

[Jac Tremblay]

This is a question for a MVP (Excel or Word).

 

[Bernie Deitrick]

Jac,

Did you read my reply - or see my signature line? ;-)

Bernie
MS Excel MVP

 

[kkknie]

In your first code example, change from:

For intJ = 1 To intI
strLineText = strLine(intJ)
If intJ = intI Then
strLineText = Mid(strLineText, 1, _
Len(strLineText) - 1)
End If
Print #2, strLineText
Next intJ

To:

For intJ = 1 To intI
strLineText = strLine(intJ)
If intJ <> intI Then
Print #2, strLineText
Else
Print #2, strLineText;
End If
Next intJ

Putting a semicolon at the end of a Print statment doesn't add a lin
feed.

 

[Jac Tremblay]

Hi Bernie

Thank you very much for your comment. It is an interesting solution but I will use K,s option which is simpler in my case

By the way, I had not read your response when I wrote my 2nd message calling for an MVP. I am very proud of being able to work with nice and professional people like you. Thank you again

Jac Tremblay

 

[Jac Tremblay]

Hi kkknie

Your solution is the one that best fits with my particular problem. My project includes over 20 pages of VBA code and I can't afford to change too much of it

A simple semicolon is all I need

Thank you very much.