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
|