R help - Jun 2009 - [OT] VBA to save excel as csv

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/

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 <hadley@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]]

Hi Hadley,
in VBA you can do sth. like

Sub save_all_csv()
 oldName = ThisWorkbook.FullName
 curpath = ThisWorkbook.Path
 
  For Each ws In ActiveWorkbook.Worksheets
    ws.SaveAs Filename:=curpath & "\" & ws.Name &
".csv",
FileFormat:=xlCSV, CreateBackup:=False
 Next
 'bring old file back
 Workbooks.Open Filename:=oldName
 ThisWorkbook.Close savechanges:=False
End Sub

My Excel (2000) has no SaveCopyAs - method for other file formats than 
xls, so I'm using .SaveAs, which saves the file and replaces the current 
worksheet with the saved one - thats why the code for bringing back the 
starting file
hth.


Hadley Wickham schrieb:
> 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
>
>   
-- 
Eik Vettorazzi
Institut f?r Medizinische Biometrie und Epidemiologie
Universit?tsklinikum Hamburg-Eppendorf

Martinistr. 52
20246 Hamburg

T ++49/40/7410-58243
F ++49/40/7410-57790