Importing Fixed Width File Macro

Hello everyone,

I'm trying to create a macro in which I import a FIXED width
file. The macro below works fine, but I have to import files
with various record sizes. So I might have 20 arrays sometimes
and sometimes 10 arrays. Is there a way to prompt me and
ask how many arrays there are in the input file? Let me know
if I should elaborate more on the problem. Any help would be
appreciated.

Thanks,
Himansu
--


Dim CellMatrixFile As String
Dim DeptMatrixFile As String

Dim SaveAsFile As String
Application.DisplayAlerts = False
Set OldSpreadSheet = ActiveWindow.ActiveSheet

CellMatrixFile = Application.GetOpenFilename("Matrix Files,*.matrix")

If CellMatrixFile <> "False" Then
Workbooks.OpenText Filename:=CellMatrixFile, _
Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=
_
Array(Array(0, 2), Array(10, 1), Array(20, 1), Array(30, 1),
Array(40, 1) _
, Array(50, 1), Array(60, 1), Array(70, 1), Array(80, 1), Array(90,
1) _
, Array(100, 1), Array(110, 1), Array(120, 1), Array(130, 1),
Array(140, 1) _
, Array(150, 1), Array(160, 1), Array(170, 1), Array(180, 1),
Array(190, 1) _
, Array(200, 1), Array(210, 1), Array(220, 1), Array(230, 1),
Array(240, 1))
Else
Exit Sub
End If

If the column widths are the same regardless of the number of files, just
use the one that handles the most columns for all.

--
Regards,
Tom Ogilvy


"Himansu" <himansu114@hotmail.com> wrote in message
news:ergeIHpyFHA.3588@tk2msftngp13.phx.gbl...
>
> Hello everyone,
>
> I'm trying to create a macro in which I import a FIXED width
> file. The macro below works fine, but I have to import files
> with various record sizes. So I might have 20 arrays sometimes
> and sometimes 10 arrays. Is there a way to prompt me and
> ask how many arrays there are in the input file? Let me know
> if I should elaborate more on the problem. Any help would be
> appreciated.
>
> Thanks,
> Himansu
> --
>
>
> Dim CellMatrixFile As String
> Dim DeptMatrixFile As String
>
> Dim SaveAsFile As String
> Application.DisplayAlerts = False
> Set OldSpreadSheet = ActiveWindow.ActiveSheet
>
> CellMatrixFile = Application.GetOpenFilename("Matrix Files,*.matrix")
>
> If CellMatrixFile <> "False" Then
> Workbooks.OpenText Filename:=CellMatrixFile, _
> Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=
> _
> Array(Array(0, 2), Array(10, 1), Array(20, 1), Array(30, 1),
> Array(40, 1) _
> , Array(50, 1), Array(60, 1), Array(70, 1), Array(80, 1),
Array(90,
> 1) _
> , Array(100, 1), Array(110, 1), Array(120, 1), Array(130, 1),
> Array(140, 1) _
> , Array(150, 1), Array(160, 1), Array(170, 1), Array(180, 1),
> Array(190, 1) _
> , Array(200, 1), Array(210, 1), Array(220, 1), Array(230, 1),
> Array(240, 1))
> Else
> Exit Sub
> End If
>
>