Importing Files into Excel using VBA

[Vexed]

Help I am trying to import vasrious files into Excel. I hae a Macro to
define the Column Breaks However sometimes it does not work. Some of the
Files seem to work correctly however others seem to have a Mind of their own.
Is there a Way to Clear all Columns breaks before I set them. Macro:


Sub sImportFile()
'
' sImportFile Macro
' Macro Created On 5/18/2007
'

'
Const SAVESTR As String = "X"
Dim myRange As Range
Dim cell As Range
Dim delRange As Range

Underscore = "_"
sRawDirName = "C:\Temp Data Files\Raw Data\"
sReconfigDirName = "C:\Temp Data Files\Reconfigured Data\"
sFileData = "LEXALL.OUT"
sSheetData = "LEXALL"
sSheetName = Cmr + Underscore + sSheetData
sFileName = Cmr + Underscore + sFileData

Application.ScreenUpdating = False
Application.DisplayAlerts = False

ChDir "C:\Temp Data Files\Raw Data"
Workbooks.OpenText Filename:=sFileData, Origin:=437, StartRow:=1,
DataType:=xlFixedWidth, _
OtherChar:=":", FieldInfo:=Array(Array(0, 2), Array(8, 2), Array(13,
2), Array(17, 2), _
Array(26, 2), Array(34, 9), Array(45, 2), Array(50, 2), Array(53,
2), Array(73, 9), _
Array(76, 2), Array(86, 2), Array(96, 2), Array(101, 2), Array(111,
2), Array(121, 2), _
Array(126, 2), Array(136, 2), Array(146, 2)),
TrailingMinusNumbers:=True
Columns("F:F").Select
ChDir "C:\Temp Data Files"
ActiveWorkbook.SaveAs Filename:= _
"C:\Temp Data Files\Reconfigured Data\9005 Exten Report.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
CreateBackup:=False
ActiveWindow.WindowState = xlMinimized
End Sub


Any and all Assistance will be appreciated

 

[Joel]

The files you are having problems with probably have TABs in them. You code
is a little confusing

DataType:=xlFixedWidth, _
OtherChar:=":", FieldInfo:=Array(Array(0, 2), Array(8, 2), Array(13,

You are specifying fixedWidth yet yo are using OtherChar:=":" which is a
parmeter if you are using a delimited Option. Try this

1) Make the file .TXT for testing purposes.
2) Then manually input the file using data - Import External Data - Import
data. then try different options with the wizrd until you get the right
combination of options.

When I write a macro using fixed width I don't include the "FieldInfo"
parametr which will allow Excel to determine the fix width automatically.
Usually I get it to work using a combination of delimied options. if all
else fails I read the text file line by line with code and do my own
splitting.