VBA to open all xls file and...

D

David P

Hello all,

I'm just getting into VBA. I usually work with Macs and have som
scripting knowledge using Applescript. :eek:

I'm after a script to open all xls files in a folder and resave each a
file type 'text (Macintosh)' with the same file name as each that
opened.

I've had a fair try but am having trouble getting to grips with the VB
code :confused:

Would want to include all nested folders as well but want to addres
that separately so I can get my head round the code!

Thanks in advance :
 
B

Bob Phillips

Without sub-directories

Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "C:\myTest\myTest"
If sFolder <> "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
.SaveAs Left(.FullName, Len(.FullName) - 4),
FileFormat:=xlTextMac
.Close savechanges:=False
End With
Next file

End If ' sFolder <> ""

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

Post back when you want the sub-directories.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
D

David P

Bob,

Tried the code above but got a syntax error on the highlighted lin
below,

Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "C:\myTest\myTest"
If sFolder <> "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
**error here**.SaveAs Left(.FullName, Len(.FullName) - 4),
FileFormat:=xlTextMac **error here**.
.Close savechanges:=False
End With
Next file

End If ' sFolder <> ""

End Sub

Any ideas??

Thanks
 
Top