saving multiple files as csv

[chouston]

Hi all, I did a search but couldn't find anything to help me...

I have about 75 excel files that need to be saved in csv format (so they can be sent to a printer). Is this possible/practical with vba? I can see how to do one sheet with the macro by fileformat:=xlcsv, but am at a loss trying to apply this to all 75 files. If this is possible it will save me soooooo much time.

Any help is greatly appreciated!

[klb]

Place your macro in your personal.xls. Create a button on one of your toolbars and attach the macro to it.

You will still have to open each file and run the macro but it should save a little time.

[zzydhf]

create a workbook named file_with_names.xls
create a sheet named save_as_info
in column A put the file names of the files you want to open start in a1.
copy this code into a module in this workbook:

Sub count_loop()
Dim counter As Integer 'declare variable
Dim fname As String
Dim fname1 As String
Dim csvfname As String

counter = 1 'initialize variable
Sheets("save_as_info").Select
Range("A1").Select '1st cell with file name

Do Until ActiveCell = "" 'or whatever your criteria is
fname1 = Cells(counter, 1) 'selects cell with file name
'this is set for column A
fname = "d:data" & fname1 'file location
csvfname = "d:data" & counter & ".csv" 'set the csv filename
Workbooks.Open Filename:=fname 'open the xls file

ActiveWorkbook.SaveAs Filename:=csvfname, FileFormat:=xlCSV, CreateBackup:=False
'save as csv

ActiveWorkbook.Close 'close csv


Windows("file_with_names.xls").Activate 'select workbook with file info
Sheets("save_as_info").Select 'select sheet with file info


ActiveCell.Offset(1, 0).Range("A1").Select 'This moves down the column
counter = counter + 1
Loop


End Sub


this uses d:data as the directory to open from and save to. you will need to change it as appropriate. it also just names the csv with a counter (1.csv, 2.csv,...)

hope this helps.

[zzydhf]

ps do NOT use the double backslashes (\) for the directories. does anyone know why it does this?