Results 1 to 1 of 1
  1. #1
    Join Date
    Sep 2009
    Posts
    11

    Unanswered: How do you stop putting an extra line in a text file when exporting from Excel?

    I am analysing routes and I have an excel file that imports a data from a number of dbf files (one file for each route) it adds headings, descriptions and separators between each route. when all the route data is entered I then export it to a text file that an old dos based programme imports manipulates based on census data.

    The data being exported is only ever 1 column wide but may vary in length from 10 rows to upwards of 1,000 depending on the length of the route and the number of routes being analysed.

    The old dos based programme won't work if there are any spaces in the text file, but every time I export the data there is an extra line at the end of the text file. I used to have two lines but after searching through many forums I found adding a ";" at the end stopped a hard return. I added this to the code and got rid of 1 hard return but still have one that has to be manually deleted.

    I have stepped through all the macros and when the code copies the data it does not pick up any extra rows, so the problem has to be either in the clipboard or the pasting.

    I have also searched forums looking for code to go to the end of the text file and back space to get rid of the hard return that way but cannot find anything that works.

    the data being exported looks like this which is all in one column. This format is the only one that the dos programme will read.

    2
    1
    800 Suburb 1 to suburb 2
    983,BP,BP
    1040,BP,BP
    1042,BP,BP
    1043,BP,BP
    ***,***,***
    2
    905 somewhere to somewhere
    878,BP,BP
    1045,BP,BP
    1048,BP,BP
    ***,***,***

    I think I have identified how the extra line gets added to the file. If you change the print the clipboard to write the clipboard, this is how the text file looks:

    "3
    1
    123
    1004,BP,BP
    1005,BP,BP
    1040,BP,BP
    ***,***,***
    2
    456
    983,BP,BP
    985,BP,BP
    986,BP,BP
    2113,BP,BP
    2117,BP,BP
    2118,BP,BP
    2121,BP,BP
    ***,***,***
    3
    789
    1854,BP,BP
    1856,BP,BP
    2179,BP,BP
    ***,***,***
    ",

    but if you use print the clipboard you do not get the opening " and the closing ", (which appears on the extra line). To check if the "'s were put in by prior to running the "WriteToFile" sub or after, I manually entered data and even copied a blank cell. Both created the " before and a "' after when using the "Write #1, theclipboard;" line. Therefore the problem must be in the WriteToFile Sub.

    Could it be in the "theclipboard = CreateObject("htmlfile").ParentWindow.ClipboardDat a.GetData("Text")" line, the text that says "CreateObject("htmlfile")?

    I tried to change htmlfile to txtfile but that just caused the macro to crash. Is there some other object that could be created there which would not put in the extra bits and pieces?

    Apart from the fact you have to use different bits of software to perform each function, if I can fix this bug the whole process from data collection to final analysis will be automated and so less error prone.



    Here is the code that adds an unwanted line. Can anyone help?



    Sub SelectColumn()
    Dim UpBound As range
    Dim LowBound As range
    Dim Z As TextRange2




    'Copies data to clipboard to insert into text file

    Sheets("Sheet2").Select
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    range("A1").Select
    If ActiveCell.Row > 1 Then
    If IsEmpty(ActiveCell.Offset(-1, 0)) Then
    Set UpBound = ActiveCell
    Else
    Set UpBound = ActiveCell.End(xlUp)
    End If
    Else
    Set UpBound = ActiveCell
    End If

    If ActiveCell.Row < Rows.Count Then
    If IsEmpty(ActiveCell.Offset(1, 0)) Then
    Set LowBound = ActiveCell

    Else
    Set LowBound = ActiveCell.End(xlDown)
    End If

    range(UpBound, LowBound).Copy
    Else
    Set LowBound = ActiveCell
    End If

    range(UpBound, LowBound).Select

    Set UpBound = Nothing
    Set LowBound = Nothing

    Call EntrOutputDetails
    End Sub

    Sub EntrOutputDetails()
    Application.ScreenUpdating = False
    Dim UserName As String
    Dim FirstSpace As Integer
    Do Until UserName <> ""
    UserName = InputBox("Enter File Name:", "File Name")
    Loop
    FirstSpace = InStr(UserName, " ")
    If FirstSpace <> 0 Then
    UserName = Left(UserName, FirstSpace - 1)
    End If
    Sheets("Sheet1").Select
    range("Z1") = UserName

    Call writetofile


    End Sub


    Sub writetofile()

    Application.ScreenUpdating = False
    Dim NameA As String
    Dim theclipboard
    NameA = Worksheets("Sheet1").range("Z1")
    theclipboard = CreateObject("htmlfile").ParentWindow.ClipboardDat a.GetData("text")



    Open "C:\Corridor Analysis\" & NameA & ".txt" For Output As #1
    Print #1, theclipboard;

    Close #1

    'Sheets("Sheet2").Select
    range("A1:A65000").Value = ""

    'Sheets("Sheet1").Select
    'range("Z1") = ""
    ' range("S2500").Value = ""
    'range("G5").Select

    End Sub
    Last edited by jbro; 08-14-11 at 23:34. Reason: I have found the problem (I think), no closer to solution

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •