Input Box selection and file open

[Rick Rothstein \(MVP - VB\)]

Works like a charm!

Great!

I can think of several files I can use this in. Never ends for you does
it....
any way you know of that I can start the import at row 45?

When you say "import at row 45", you mean start placing the text imported
from the file at row 45, right? You can control which line the print out
starts on by making a small change on the 6th line up from the bottom of the
Import_PTC subroutine. This is the line as it appeared in my code (where the
imported data was outputted to Row 1)...

Cells(X + 1, Z + 1 - LBound(FieldWidths)).Value = Entry

and here is that same line changed to start outputting the data on Row 45
instead...

Cells(X + 45, Z + 1 - LBound(FieldWidths)).Value = Entry

Notice, all I changed is the value being added to the X loop index value in
the first argument.

Rick

 

[cottage6]

Hi Rick,
Actually in the Text Import Wizard, you can choose which row to start the
import with from the .txt file. The text file has 44 rows of total garbage
at the top that I don't need (header rows, the report name, etc). We have a
lot of old programs here. I can talk to the programmer about getting rid of
these because I think he can do that right in his job. Thanks again!

 

[Rick Rothstein \(MVP - VB\)]

The replacement Import_PTC subroutine below will skip over the first 44
lines of your file and begin outputting the 45th line onward starting at Row
1.

Rick

Sub Import_PTC()
Dim X As Long
Dim Z As Long
Dim P As Long
Dim FileNum As Long
Dim JunkCount As Long
Dim Entry As Variant
Dim FieldWidths As Variant
Dim TotalFile As String
Dim Records() As String
FileNum = FreeFile
FieldWidths = Array(7, 26, 12, 5, 9, 10, 10, 15, 10, 15, 12)
Open Path & ListBox1.Text For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
Records = Split(TotalFile, vbCrLf)
With ActiveSheet
For X = 45 To UBound(Records)
P = 1
For Z = LBound(FieldWidths) To UBound(FieldWidths)
Entry = Mid$(Records(X), P, FieldWidths(Z))
If Z - LBound(FieldWidths) = 2 Then
Entry = "'" & Entry
End If
Cells(X - 44, Z + 1 - LBound(FieldWidths)).Value = Entry
P = P + FieldWidths(Z)
Next
Next
End With
End Sub

 

[Rick Rothstein \(MVP - VB\)]

Here is a possibly more general solution... if the "junk" in the front of
the file **never** can start with 7 digits followed by a space, then you can
use the following replacement Import_PTC subroutine to read the file (it
will skip over any number of lines of "junk" in the front of the file, not
just 45 of them).

Rick

Sub Import_PTC()
Dim X As Long
Dim Z As Long
Dim P As Long
Dim FileNum As Long
Dim JunkCount As Long
Dim Entry As Variant
Dim FieldWidths As Variant
Dim TotalFile As String
Dim Records() As String
FileNum = FreeFile
FieldWidths = Array(7, 26, 12, 5, 9, 10, 10, 15, 10, 15, 12)
Open Path & ListBox1.Text For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
Records = Split(TotalFile, vbCrLf)
With ActiveSheet
For X = 0 To UBound(Records)
If Records(X) Like "####### *" Then
P = 1
For Z = LBound(FieldWidths) To UBound(FieldWidths)
Entry = Mid$(Records(X), P, FieldWidths(Z))
If Z - LBound(FieldWidths) = 2 Then
Entry = "'" & Entry
End If
Cells(X + 1 - JunkCount, Z + 1 - LBound(FieldWidths)).Value =
Entry
P = P + FieldWidths(Z)
Next
Else
JunkCount = JunkCount + 1
End If
Next
End With
End Sub

 

[cottage6]

Hey Rick, thanks for both solutions. I've tried both of them and they work
great. I'm sure I'll use both methods in the future, and it always helps to
get several solutions. That's the only way I ever learn. Again, thanks for
all your help!

 

[Rick Rothstein \(MVP - VB\)]

My pleasure... I'm glad this all worked out for you.

Remember, by the way, that the "general" solution requires the "junk" at the
beginning of the file to **never** have a line start with 7 digits followed
by a space. If you are guaranteed that, then the general solution will work
even if the number of lines of "junk" should ever change in the future.

Rick