PDA

View Full Version : Open text file and delete lines.



andysuth
08-15-2007, 05:36 AM
I'm looking for a bit of code to open text files and delete about four or five different types of lines.

These are trail files for ProE and I want to get rid of mouse movements automatically and comments that aren't needed:

e.g. Comments:
!%CIDatum planes will be displayed.
(always start with a '!')

e.g. Zooms:
~ Wheel `main_dlg_cur` `proe_win` \
9 809 831 458752 0 1529 947 1920 1200 107781
('~ Wheel' starts two lines)

I've tried similar to the replace command:



Sub filemoder()
Dim VFF As Long, trailwpath As String, trailoutput As String, trailbody As String, trailbodyO As String, vIStep As String
trailwpath = "C:\Path\trailfile.txt"
trailoutput = "C:\Path\trailfile2.txt"

VFF = FreeFile
Open trailwpath For Binary As #VFF
trailbody = Space$(LOF(VFF))
Get #VFF, , trailbody
Close #VFF
trailbodyO = Replace(trailbody, "!", "")
Open trailoutput For Output As #VFF
Print #VFF, trailbodyO
Close #VFF

End Sub




But with poor results. Mainly because it just takes out the "!" comment denoter. So the file then falls over

I was thinking maybe read the file line by line and then only write to next file string if it didn't read the "!" or the "~ wheel", but I don't know how to do that.

Any ideas?

-AS

Bob Phillips
08-15-2007, 05:55 AM
Dim iLastRow As Long

Workbooks.OpenText Filename:=vNIStep , _
Origin:=xlMSDOS, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
With ActiveWorkbook.Worksheets(1)

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For i = iLastRow To 1 Step -1

If Left$(.Cells(i, "A").Value, 1) = "~" Then

.Rows(i).Resize(2).Delete
ElseIf Left$(.Cells(i, "A").Value, 1) = "!" Then

.Rows(i).Delete
End If
Next i

End With

ActiveWorkbook.Save
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True

andysuth
08-15-2007, 05:57 AM
If Left$(.Cells(i, "A").Value, 1) = "~" Then

.Rows(i).Resize(2).Delete
ElseIf Left$(.Cells(i, "A").Value, 1) = "!" Then

.Rows(i).Delete
End If

Clever, so I load the file into Excel and use the native formatting commands.

Love it.

-AS

andysuth
08-15-2007, 06:20 AM
I've tried the code, works great, but there were other commands with the start ~ (in fact, most commands!), so I've modified as below.

Unfortunately it's crashing out and I can't catch where, I'll keep you posted on this, but not sure it's a go idea simply because of ProE.

I suspect it trips because of the LButton Arm / Disarm command, so I'll try later, but thanks for the help, and I've posted the code below for any other ProE saddo's out there who want to rationalise the trail files.

-AS




Sub Hunter()
Dim VFF As Long, trailwpath As String, trailoutput As String, trailbody As String, trailbodyO As String, vIStep As String
Dim iLastRow As Long


trailwpath = "C:\ProE_files\iD2M\trailfile.txt"


Workbooks.OpenText Filename:=trailwpath, _
Origin:=xlMSDOS, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
With ActiveWorkbook.Worksheets(1)

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For i = iLastRow To 1 Step -1

If Left$(.Cells(i, "A").Value, 7) = "~ Wheel" Then
.Rows(i).Resize(2).Delete
ElseIf Left$(.Cells(i, "A").Value, 6) = "~ LBut" Then
.Rows(i).Resize(2).Delete
ElseIf Left$(.Cells(i, "A").Value, 1) = "!" Then
.Rows(i).Delete
ElseIf Left$(.Cells(i, "A").Value, 1) = "<" Then
.Rows(i).Delete
End If
Next i

End With

ActiveCell.Range("a1").Select
ActiveCell.Rows.Insert
ActiveCell.Value = "!trail file version No. 1350"

ActiveWorkbook.Save
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True

End Sub

Bob Phillips
08-15-2007, 06:27 AM
Andy,

what do you mean by the arm/disarm button? If it is just cases that need to be catered for thst should be simple.