Lines at end of output Text file

[swraman]

Hi,

Someone I know is making a VB script for Excel. The script allows him to add data to a spreadsheet, then go to the next line of te spreadsheet, add more data, and on and on.

The problem is that, when he outputs to a text file, the output file has 1 or 2 blank lines at the end, and the program this text file is going into doesn't like blank lines.

Is there any way to remove blank lines from the end of a file in vb once the file is created?

thanks

[Pino]

Moved to office development

[RobDog888]

Welcome to the Forums.

Instead of trying to remove them its better to figure out why and what is placing them there in the first place. Then you can fix that and the extra lines will be gone.

[swraman]

I found out why there is an extra line at the end...

YOu type the data you want to add into the form, then click Insert.

The insert button does this:
-Inserts the text in the appropriate cells, in one row.
-resizes all the rows to fit
-Changes the offset to the next row.

The problem is the last thing. It goes to the next line no matter what. These are the solutions I could think of:

1) Move the "offset to next row" command to the begining of the insert command. The problem with this is that then it skips teh first row, adding a blank row where it should go. I tried to make it check whether there is data in the first row, but I have never worked with VB (this being the first VB script ive ever seen) and coldn't get it to work.
Another way to execute this would be to create a variable set to 0, and as soon as you insert one row of data, make the variable set to 1. Then in the insert routine add a check to check the value of the variable, if it is 0 do nothing and if it is 1 add a new line. Again, I am nhot experienced in VB and couldn't get htis to work.

2) create a Integer variable in the insert routine that has the starting value of 0. Each time you add a row, add one to the *******. When You have the output to the text file, have it select the region starting in A1 (the document starts there) and ending in Jx (J is the letter that each colun ends on, it is a constant; x is the variable that you created in the "insert" routine. Again, I am not experienced enough to do this.

3) have the script remove the last blan lines from the already outputted text file.

I have the form script now, here it is:

Insert routine:

Private Sub cmdOK_Click()
Dim a As Range
Set a = Selection
a.Value = Me.cmbWeek.Text
a.Offset(0, 1).Value = Me.cmbHub.Text
a.Offset(0, 2).Value = Me.cmbCustomer.Text
a.Offset(0, 3).Value = Me.cmbItem.Text
a.Offset(0, 4).Value = Me.cmbCost.Text
a.Offset(0, 5).Value = Me.cmbCurrency.Text
a.Offset(0, 6).Value = Me.cmbVendor.Text
a.Offset(0, 7).Value = Me.txtInvoice.Text
a.Offset(0, 8).Value = Me.txtQty.Text
a.Offset(0, 9).Value = Me.txtAmt.Text
a.Columns.AutoFit
a.Offset(0, 1).Columns.AutoFit
a.Offset(0, 2).Columns.AutoFit
a.Offset(0, 3).Columns.AutoFit
a.Offset(0, 4).Columns.AutoFit
a.Offset(0, 5).Columns.AutoFit
a.Offset(0, 6).Columns.AutoFit
a.Offset(0, 7).Columns.AutoFit
a.Offset(0, 8).Columns.AutoFit
a.Offset(0, 9).Columns.AutoFit
a.Offset(1, 0).Select

the bold part is the part I think is causing the problem.

Save file as text file code:

Private Sub CommandButton2_Click()
Selection.CurrentRegion.Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Selection.Columns.AutoFit
    Range("A1").Select
        Selection.CurrentRegion.Select
        ActiveWorkbook.ActiveSheet.SaveAs Filename:="C:\Book2.txt", _
        FileFormat:=xlText, CreateBackup:=False
        Range("A1").Select

Here is the file, so you can see what it does and what Im talking about. Just select any data and imput it (you must start on A1 in Excel), then click save. Open C:\Book2.txt to see the problem Im talking about (the blank lines). Im sure all of you already knew all that last paragraph, haha.

http://www.swraman.com/stuff/Input_example2.xls

Sorry...I heva never used VB...someone just asked me for help with this. I usually code PHP/use photoshop.

Thanks

[salvelinus]

You could do a Trim in the text file.

[swraman]

how would that be done?

Thanks

[westconn1]

f1 = FreeFile
Open "C:\book2.txt" For Input As f1
    pos = EOF(f1)
    strfi = Input(LOF(f1), #f1)
    Close f1
    arrfi = Split(strfi, vbNewLine)
    For i = UBound(arrfi) To 1 Step -1
        If Not arrfi(i) = "" Then Exit For
    Next
    ReDim Preserve arrfi(i)
    strfi = Join(arrfi, vbNewLine)
Open "c:\book2.txt" For Output As f1
    Print #f1, strfi;
    Close f1

this will remove all blank lines at the end of your text file
if they are not empty they will still be there, even if you can't see any characters, maybe there are nulls or something

pete