Macro for importing a fixed width text file into the activeworkbook
 

I can set up a macro to import a text file just fine, but how can I
write it so that it imports into the active workbook? Pearson's has an
example of importing to an active workbook, but thats limited to
delimited files. How can I modify this to work for a fixed width? any
help is greatly appreciated!

Macro for importing a fixed width text file into the activeworkbook
 

Are you're fixed width columns going to all be the exact same
with(meaning number of characters)?

Sandy

Koveras wrote:
I can set up a macro to import a text file just fine, but how can I
write it so that it imports into the active workbook? Pearson's has an
example of importing to an active workbook, but thats limited to
delimited files. How can I modify this to work for a fixed width? any
help is greatly appreciated!

Macro for importing a fixed width text file into the activeworkbook
 

I meant width...if they are then try playing around with this, it's
pretty rough but you'll get the jist of what needs to be done.

Sub ImportFixedWidth(ByVal FileLoc As String, ByVal NumChar As Integer)
Dim x As Integer, TheLine As String
Dim mRow As Integer, mCol As Integer
Open FileLoc For Input Access Read As #1
While Not EOF(1)
Line Input #1, TheLine
mRow = ActiveCell.Row
mCol = ActiveCell.Column
For x = 1 To Len(TheLine) Step NumChar
Cells(mRow, mCol).Value = Mid(TheLine, x, NumChar)
mCol = mCol + 1
Next x
Wend
Close #1
End Sub

HTH

Sandy

Sandy wrote:
Are you're fixed width columns going to all be the exact same
with(meaning number of characters)?

Sandy

Koveras wrote:
I can set up a macro to import a text file just fine, but how can I
write it so that it imports into the active workbook? Pearson's has an
example of importing to an active workbook, but thats limited to
delimited files. How can I modify this to work for a fixed width? any
help is greatly appreciated!

Macro for importing a fixed width text file into the activeworkbook
 

Apologies, I should have been more specific. I run a monthly report
for inventory and the fields will vary from one another but will not
change from month to month. Here is an example of the fixed width file
i am importing:

Workbooks.OpenText Filename:= _
"C:\Documents and Settings\CAThomd\My Documents\Mth End Inv Sep
2006.txt", _
Origin:=-535, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array( _
Array(0, 2), Array(16, 1), Array(28, 1), Array(56, 1), Array(60,
1), Array(73, 1), Array(90 _
, 1), Array(103, 1), Array(120, 1), Array(133, 1), Array(150, 1),
Array(163, 1)), _
TrailingMinusNumbers:=True

How would I set this up? BTW thanks for the reply!


Sandy wrote:
I meant width...if they are then try playing around with this, it's
pretty rough but you'll get the jist of what needs to be done.

Sub ImportFixedWidth(ByVal FileLoc As String, ByVal NumChar As Integer)
Dim x As Integer, TheLine As String
Dim mRow As Integer, mCol As Integer
Open FileLoc For Input Access Read As #1
While Not EOF(1)
Line Input #1, TheLine
mRow = ActiveCell.Row
mCol = ActiveCell.Column
For x = 1 To Len(TheLine) Step NumChar
Cells(mRow, mCol).Value = Mid(TheLine, x, NumChar)
mCol = mCol + 1
Next x
Wend
Close #1
End Sub

HTH

Sandy

Sandy wrote:
Are you're fixed width columns going to all be the exact same
with(meaning number of characters)?

Sandy

Koveras wrote:
I can set up a macro to import a text file just fine, but how can I
write it so that it imports into the active workbook? Pearson's has an
example of importing to an active workbook, but thats limited to
delimited files. How can I modify this to work for a fixed width? any
help is greatly appreciated!

Macro for importing a fixed width text file into the activeworkbook
 

Here try working with this, it worked for me, but I'm sure you'll have
to do some changes here and there.

Sub FixedWidth(ByVal FileLoc As String, ByVal NumChar As Integer)
Dim x As Integer, TheLine As String
Dim mRow As Integer, mCol As Integer
Dim MyArray(1 To 12, 1 To 2) As Long
MyArray(1, 1) = 0
MyArray(1, 2) = 2
MyArray(2, 1) = 16
MyArray(2, 2) = 1
MyArray(3, 1) = 28
MyArray(3, 2) = 1
MyArray(4, 1) = 56
MyArray(4, 2) = 1
MyArray(5, 1) = 60
MyArray(5, 2) = 1
MyArray(6, 1) = 73
MyArray(6, 2) = 1
MyArray(7, 1) = 90
MyArray(7, 2) = 1
MyArray(8, 1) = 103
MyArray(8, 2) = 1
MyArray(9, 1) = 120
MyArray(9, 2) = 1
MyArray(10, 1) = 133
MyArray(10, 2) = 1
MyArray(11, 1) = 150
MyArray(11, 2) = 1
MyArray(12, 1) = 163
MyArray(12, 2) = 1
Open FileLoc For Input Access Read As #1
mStart = 1
Line Input #1, TheLine
mRow = ActiveCell.Row
mCol = ActiveCell.Column
For x = 1 To 12 Step 1
Cells(mRow, mCol).Value = Mid(TheLine, mStart, MyArray(x, 1))
mStart = mStart + MyArray(x, 1)
mRow = mRow + 1
If MyArray(x, 2) = 1 Then
Cells(mRow, mCol).NumberFormat = "General"
ElseIf MyArray(x, 2) = 2 Then
Cells(mRow, mCol).NumberFormat = "Date"
End If
Next x
Close #1
End Sub


Sandy

Koveras wrote:
Apologies, I should have been more specific. I run a monthly report
for inventory and the fields will vary from one another but will not
change from month to month. Here is an example of the fixed width file
i am importing:

Workbooks.OpenText Filename:= _
"C:\Documents and Settings\CAThomd\My Documents\Mth End Inv Sep
2006.txt", _
Origin:=-535, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array( _
Array(0, 2), Array(16, 1), Array(28, 1), Array(56, 1), Array(60,
1), Array(73, 1), Array(90 _
, 1), Array(103, 1), Array(120, 1), Array(133, 1), Array(150, 1),
Array(163, 1)), _
TrailingMinusNumbers:=True

How would I set this up? BTW thanks for the reply!


Sandy wrote:
I meant width...if they are then try playing around with this, it's
pretty rough but you'll get the jist of what needs to be done.

Sub ImportFixedWidth(ByVal FileLoc As String, ByVal NumChar As Integer)
Dim x As Integer, TheLine As String
Dim mRow As Integer, mCol As Integer
Open FileLoc For Input Access Read As #1
While Not EOF(1)
Line Input #1, TheLine
mRow = ActiveCell.Row
mCol = ActiveCell.Column
For x = 1 To Len(TheLine) Step NumChar
Cells(mRow, mCol).Value = Mid(TheLine, x, NumChar)
mCol = mCol + 1
Next x
Wend
Close #1
End Sub

HTH

Sandy

Sandy wrote:
Are you're fixed width columns going to all be the exact same
with(meaning number of characters)?

Sandy

Koveras wrote:
I can set up a macro to import a text file just fine, but how can I
write it so that it imports into the active workbook? Pearson's has an
example of importing to an active workbook, but thats limited to
delimited files. How can I modify this to work for a fixed width? any
help is greatly appreciated!

Macro for importing a fixed width text file into the activeworkbook
 

Wow I'm pretty new with VB. Let me play around with this and see how I
can work this in. Thanks a lot!

Sandy wrote:
Here try working with this, it worked for me, but I'm sure you'll have
to do some changes here and there.

Sub FixedWidth(ByVal FileLoc As String, ByVal NumChar As Integer)
Dim x As Integer, TheLine As String
Dim mRow As Integer, mCol As Integer
Dim MyArray(1 To 12, 1 To 2) As Long
MyArray(1, 1) = 0
MyArray(1, 2) = 2
MyArray(2, 1) = 16
MyArray(2, 2) = 1
MyArray(3, 1) = 28
MyArray(3, 2) = 1
MyArray(4, 1) = 56
MyArray(4, 2) = 1
MyArray(5, 1) = 60
MyArray(5, 2) = 1
MyArray(6, 1) = 73
MyArray(6, 2) = 1
MyArray(7, 1) = 90
MyArray(7, 2) = 1
MyArray(8, 1) = 103
MyArray(8, 2) = 1
MyArray(9, 1) = 120
MyArray(9, 2) = 1
MyArray(10, 1) = 133
MyArray(10, 2) = 1
MyArray(11, 1) = 150
MyArray(11, 2) = 1
MyArray(12, 1) = 163
MyArray(12, 2) = 1
Open FileLoc For Input Access Read As #1
mStart = 1
Line Input #1, TheLine
mRow = ActiveCell.Row
mCol = ActiveCell.Column
For x = 1 To 12 Step 1
Cells(mRow, mCol).Value = Mid(TheLine, mStart, MyArray(x, 1))
mStart = mStart + MyArray(x, 1)
mRow = mRow + 1
If MyArray(x, 2) = 1 Then
Cells(mRow, mCol).NumberFormat = "General"
ElseIf MyArray(x, 2) = 2 Then
Cells(mRow, mCol).NumberFormat = "Date"
End If
Next x
Close #1
End Sub


Sandy

Koveras wrote:
Apologies, I should have been more specific. I run a monthly report
for inventory and the fields will vary from one another but will not
change from month to month. Here is an example of the fixed width file
i am importing:

Workbooks.OpenText Filename:= _
"C:\Documents and Settings\CAThomd\My Documents\Mth End Inv Sep
2006.txt", _
Origin:=-535, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array( _
Array(0, 2), Array(16, 1), Array(28, 1), Array(56, 1), Array(60,
1), Array(73, 1), Array(90 _
, 1), Array(103, 1), Array(120, 1), Array(133, 1), Array(150, 1),
Array(163, 1)), _
TrailingMinusNumbers:=True

How would I set this up? BTW thanks for the reply!


Sandy wrote:
I meant width...if they are then try playing around with this, it's
pretty rough but you'll get the jist of what needs to be done.

Sub ImportFixedWidth(ByVal FileLoc As String, ByVal NumChar As Integer)
Dim x As Integer, TheLine As String
Dim mRow As Integer, mCol As Integer
Open FileLoc For Input Access Read As #1
While Not EOF(1)
Line Input #1, TheLine
mRow = ActiveCell.Row
mCol = ActiveCell.Column
For x = 1 To Len(TheLine) Step NumChar
Cells(mRow, mCol).Value = Mid(TheLine, x, NumChar)
mCol = mCol + 1
Next x
Wend
Close #1
End Sub

HTH

Sandy

Sandy wrote:
Are you're fixed width columns going to all be the exact same
with(meaning number of characters)?

Sandy

Koveras wrote:
I can set up a macro to import a text file just fine, but how can I
write it so that it imports into the active workbook? Pearson's has an
example of importing to an active workbook, but thats limited to
delimited files. How can I modify this to work for a fixed width? any
help is greatly appreciated!