I have a excel file with 30 worksheets (naming 1, 2, 3, ..., 29, 30)
Can I change each worksheet into one CSV file by marco?

*Sometimes the worksheet name may be random but still inside 1-30 range

Thanks

You cannot convert all sheets into one Excel directly

You convert each sheet into CSV using the following

i1 = 1
For Each SHT In Sheets
    SHT.Activate
    ActiveWorkbook.SaveAs "C:\Temp" & i1 & ".csv", xlCSV
    i1 = i1 + 1
Next SHT


Then use the following code to collate all to a single file

Sub Append_Text_Files()

Dim oFS As FileSystemObject
Dim oFS1 As FileSystemObject

Dim oTS As TextStream
Dim oTS1 As TextStream

Dim vTemp

Set oFS = New FileSystemObject
Set oFS1 = New FileSystemObject


For i1 = 1 To 30

    Set oTS = oFS.OpenTextFile("c:\Sheet" & i1 & ".txt", ForReading)
    vTemp = oTS.ReadAll

    Set oTS1 = oFS.OpenTextFile("c:\Documents and Settings\160831\Temp.txt", ForAppending, True)
    oTS1.Write (vTemp)

Next i1

End Sub

You need to add the microsoft scripting runtime reference to use the above code

Good luck
Shasur

http://www.dotnetdud.blogspot.com

Thanks Shasur,

I try to open the csv file in notepad and i saw the spacing become comma, can i remove the comma?

Thanks again

You can retain those spaces. That shouldn't be a problem



http://www.dotnetdud.blogspot.com

Thanks shasur.

All the empty cell in excel become comma by the marco
and I want to eliminate the comma in A1:o10 in everysheet.
the other remain unchange