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

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

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.

--
Regards,
Tom Ogilvy


"waveracerr " wrote in message
...
I have a macro that opens all delimited files and SHOULD save each file
as an excel file. The new name should be the same as the delimited
file without the ".xl" in the end. In the code below the macro will
open each file but will try to save the file as False.xls, if I accept
this name the code will run through each file in the directory and open
& save each delimited file. This would be great but with each save the
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 Sub


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