VBA to open all xls file and...

  • Thread starter Thread starter David P
  • Start date Start date
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 :
 
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)
 
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)
 
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
 
You may have been hit by line wrap:

With ActiveWorkbook
.SaveAs Left(.FullName, Len(.FullName) - 4),
FileFormat:=xlTextMac
.Close savechanges:=False
End With

try adding a line continuation character (space underscore). (that .saveas is
actually one logical line):

With ActiveWorkbook
.SaveAs Left(.FullName, Len(.FullName) - 4), _
FileFormat:=xlTextMac
.Close savechanges:=False
End With
 
Thanks Dave for the heads up regards line wrap.

The code works fine... but

as each file is saved to the folder the routine then tries to open each
resaved text file!

Can we limit the opened file to just ".xls" files.

Cheers
 
How about adding a check to see if the filename ends with .xls?

Option Explicit

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"
sFolder = "c:\my documents\excel\test"
If sFolder <> "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If LCase(file.Name) Like "*.xls" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
.SaveAs Left(.FullName, Len(.FullName) - 4), _
FileFormat:=xlTextMac
.Close savechanges:=False
End With
End If
Next file

End If ' sFolder <> ""

End Sub
 
or even if it an Excel file

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"
'sFolder = "c:\my documents\excel\test"
If sFolder <> "" Then
Set Folder = FSO.GetFolder(sFolder)

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

End If ' sFolder <> ""

End Sub

Guess you'r not ready for sub-directories yet<vbg>

--

HTH

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