Importing Files into Excel using VBA
 

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

Importing Files into Excel using VBA
 

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.

"Vexed" wrote:

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