.rtf (Text) importing into Excel

  • Thread starter Thread starter sosteffo
  • Start date Start date
S

sosteffo

This could save me so much time..........

I run report several time a day, they are produced in .rtf format,
normally import these manually into excel and set the 'Fixed width' s
the data is in the right columns, this then needs manually editing t
remove headings etc that are repeated at the top of each page.

this report is very similar report which i found posted in the forum,
not very used to VB but would appreciate any help.

http://68.230.14.172:81/Monthly Billing 103103.tx
 
Assuming your file was the only text file stored in the following folder:-

C:\4ImportFile\

then the following routine will clean that file up as you would want. It also
puts the region code against each record so you can dump it into a Pivot table
if you want and then play around with it:-



Sub ImportData()

Workbooks.OpenText Filename:= _
"C:\4ImportFile\" & "*" & ".txt", _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array( _
Array(0, 1), Array(4, 1), Array(16, 1), Array(49, 3), Array(61, 3),
Array(72, 3), Array(84, _
1), Array(98, 1), Array(112, 1), Array(127, 1), Array(141, 1),
Array(156, 1), Array(171, 1) _
, Array(185, 1), Array(199, 1), Array(213, 1))
Columns("B:F").EntireColumn.AutoFit

With ActiveSheet
lrow = .Cells(Rows.Count, "B").End(xlUp).Row
Set Rng = .Range(Cells(1, "B"), Cells(lrow, "B"))
End With

For Each cel In Rng
If Len(cel) = 12 And Mid(cel.Value, 3, 1) = "-" And _
Mid(cel.Value, 10, 1) = "-" Then
cel.Offset(0, -1).Value = Right(cel.Value, 2)
Else: cel.Offset(0, -1).Value = ""
End If
Next cel

For Each cel In Rng
If cel.Value = "Job" Then
cel.Offset(-1, -1).Value = "Header1"
cel.Offset(0, -1).Value = "Header2"
Exit For
End If
Next cel

On Error Resume Next
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Ken Wright said:
What version of Excel are you using?
Click to expand...
 
OK, let's try again without the damn wordwrap :-)


Sub ImportData()

Workbooks.OpenText Filename:= _
"C:\4ImportFile\" & "*" & ".txt", _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(4, 1), Array(16, 1) _
, Array(49, 3), Array(61, 3), Array(72, 3), Array(84, _
1), Array(98, 1), Array(112, 1), Array(127, 1), _
Array(141, 1), Array(156, 1), Array(171, 1) _
, Array(185, 1), Array(199, 1), Array(213, 1))
Columns("B:F").EntireColumn.AutoFit

With ActiveSheet
lrow = .Cells(Rows.Count, "B").End(xlUp).Row
Set Rng = .Range(Cells(1, "B"), Cells(lrow, "B"))
End With

For Each cel In Rng
If Len(cel) = 12 And Mid(cel.Value, 3, 1) = "-" And _
Mid(cel.Value, 10, 1) = "-" Then
cel.Offset(0, -1).Value = Right(cel.Value, 2)
Else: cel.Offset(0, -1).Value = ""
End If
Next cel

For Each cel In Rng
If cel.Value = "Job" Then
cel.Offset(-1, -1).Value = "Header1"
cel.Offset(0, -1).Value = "Header2"
Exit For
End If
Next cel

On Error Resume Next
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub
 
thanks, that works great.

Just 1 last thing, is there a way to specific the file name, i.e i kno
the folder the file is located in, could i have a form etc that prompt
for the file name (which would be a spool number from the report
 
Back
Top