Excel VBA-open & save all delimited files  
Author Message
jovian





PostPosted: Thu Feb 12 15:51:50 CST 2004 Top

Excel Programming >> Excel VBA-open & save all delimited files

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.hide-link.com/

Excel93  
 
 
Tom





PostPosted: Thu Feb 12 15:51:50 CST 2004 Top

Excel Programming >> Excel VBA-open & save all delimited files 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




> 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/
>