Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing a Value from many Excel Files | Excel Discussion (Misc queries) | |||
Importing Files into Excel | Excel Discussion (Misc queries) | |||
Importing CSV files in Excel | Excel Programming | |||
importing files into excel | Excel Discussion (Misc queries) | |||
Excel VBA-Importing txt files with VBA | Excel Programming |