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