Excel VBA-open & save all delimited files

W

waveracerr

I have a macro that opens all delimited files and SHOULD save each fil
as an excel file. The new name should be the same as the delimite
file without the ".xl" in the end. In the code below the macro wil
open each file but will try to save the file as False.xls, if I accep
this name the code will run through each file in the directory and ope
& save each delimited file. This would be great but with each save th
macro saves the files as False.xls and asks to overwrite the False.xls
Any help would be much appreciated!

Thanks,
Ryan

Sub OpenAllDelimited()
'
'Open all delimited files from raw data directory in D:\Key West
'
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\raw 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

' Save workbook with name

wkbk1.SaveAs Filename = Left(wkbk1.Name, Len(wkbk1.Name) - 4) & ".xls"
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False
_
CreateBackup:=False
wkbk1.Close SaveChanges:=False

Next
End Su
 
T

Tom Ogilvy

There is a missing character on this line:

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


Change it to

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

Between Filename and Left it should be Filename:=Left

with a colon equal sign.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top