I have several delimited files (.xl) in the same directory and would
like to open all of them. I would like to save each file in the same
name except as an Excel file. I have macros that open all files in a
directory, save all open workbooks and open delimited files given a
name (in an array in the macro). Any suggestions would be
appreciated!

Below is what I currently have to open the files, notice how each file
name must be specified in the array. I basically want to get rid of
this necessity.

Sub Open1()
Dim varr As Variant
Dim wkbk1 As Workbook
Dim wkbk As Workbook
Dim i As Long
Dim sh1 As Workbook
Dim sName As String

varr = Array("12 15 03 01 NIST610.xl", _
"12 15 03 02 NIST610.xl", _
"12 15 03 03 NIST610.xl", _
"12 15 03 04 MACS1.xl")

Set wkbk = ActiveWorkbook
For i = LBound(varr) To UBound(varr)
Workbooks.OpenText Filename:=tName, _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13,
1), Array(14, 1), Array(15 _
, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1),
Array(20, 1), Array(21, 1), _
Array(22, 1), Array(23, 1), Array(24, 1))

Set wkbk1 = ActiveWorkbook

Next
End Sub

I'll also include my code to open all files within a directory. Can I
modify this to open all delimited files and automatically go through
the delimited-to-excel file wizard?

Sub OpenAll()
Dim sName As String
sName = Dir("D:\Key West\New Data\*.xls")
Do While sName < ""
Workbooks.Open Filename:=sName
sName = Dir()
Loop
End Sub

Thank you!

Ryan


---
Message posted from http://www.ExcelForum.com/