Excel VBA-How to open several delmited files & SaveAs Excel files

[waveracerr]

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

 

[Tom Ogilvy]

You can combine them to achieve this.

Sub OpenAll()
Dim varr As Variant
Dim wkbk1 As Workbook
Dim wkbk As Workbook
Dim i As Long
Dim sh1 As Workbook
Dim sName As String
Dim sPath as string
Dim ub as Long
Redim Varr(1 to 1)
ub = 1
sPath = "D:\Key West\New Data\"
sName = Dir(spath & "*.xl")
Do While sName <> ""
Redim Preserve varr(1 to ub)
varr(ub) = sName
ub = ub + 1
sName = Dir()
Loop

Set wkbk = ActiveWorkbook
For i = LBound(varr) To UBound(varr)
tName = sPath & varr(i)
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

' any processing

wkbk1.SaveAs Filename = left(wkbk1.name, len(wkbk1.name)-4 & ".xls", _
Fileformat:=xlWorkbookNormal
wkbk1.Close SaveChanges:=False

Next
End Sub

 

[waveracerr]

Thanks for the help, I still cannot get the macro to save the workbook.
Excel gives me an error, "Run-time error '13': Type mismatch".
included a missing ")" after the number "4" in the code and then fin
Excel telling me the file "False.xls" already exists and do I wish t
save over it... I click "No" and then find the following code i
highlighted in debug mode:

wkbk1.SaveAs Filename = Left(wkbk1.Name, Len(wkbk1.Name) - 4) & ".xls"
_
FileFormat:=xlWorkbookNormal

I wonder if the code under the above code has anything to do with thi
problem?

wkbk1.Close SaveChanges:=False

Thanks for any help!

Rya

 

[Tom Ogilvy]

Hard to tell what you did. For example, in your original code

Set wkbk = ActiveWorkbook
For i = LBound(varr) To UBound(varr)
Workbooks.OpenText Filename:=tName, _

you open a workbook tName, but you never set that variable so your array of
names it totally unused. Yet you say the code is working.

Yes, I did omit a closing paren:

wkbk1.SaveAs Filename = left(wkbk1.name, len(wkbk1.name)-4 & ".xls", _

should be

wkbk1.SaveAs Filename = left(wkbk1.name, len(wkbk1.name)-4) & ".xls", _

No, there is nothing wrong with the statement you cite - since it hasn't
been executed yet when your error occurs, that would be even more reason not
to look there.

for some reason, it is opening a workbook named False.xl I would assume.
Probably the product of some previously flawed code being run.

If you want to delete existing workbooks with duplicate names, you can use
the kill command

Or you can suppress the message with DiplayAlerts.