Hi,
Try this macro:
Sub SaveAllCsv()
Dim xl As New Excel.Application
Dim wk As New Excel.Workbook
Dim s As Excel.Worksheet
Dim fso As New FileSystemObject ' You need set the Microsoft Scripting
Runtime in References
Dim a As File
Dim f As Folder
Dim pathDir, pathDirOut As String
' Set dir of the files
pathDir = "C:/xls/"
' Set out dir
pathDirOut = "C:/xls/out/"
Set f = fso.GetFolder(pathDir)
For Each a In f.Files
Set wk = xl.Workbooks.Open(a)
xl.DisplayAlerts = False
' Look for all sheets in the workbook
For Each s In wk.Sheets
' If is there values in the sheet, save as...
If xl.WorksheetFunction.CountA(s.Range("A1:A65536")) > 0 Then
s.SaveAs pathDirOut & s.Name & Replace(a.Name, ".xls", ".csv"),
FileFormat:=xlCSV
End If
Next
wk.Close
xl.DisplayAlerts = True
xl.Quit
Set xl = Nothing
Set wk = Nothing
DoEvents
Next
End Sub
On Mon, Jun 15, 2009 at 1:19 PM, Hadley Wickham <had...@rice.edu> wrote:
> Hi all,
>
> This is a little off-topic, but it is on the general topic of getting
> data in R. I'm looking for a excel macro / vba script that will
> export all spreadsheets in a directory (with one file per tab) into
> csv. Does anyone have anything like this?
>
> Thanks,
>
> Hadley
>
> --
> http://had.co.nz/
>
> ______________________________________________
> R-help@r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
--
Henrique Dallazuanna
Curitiba-Paraná-Brasil
25° 25' 40" S 49° 16' 22" O
[[alternative HTML version deleted]]
______________________________________________
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
