On 19 Oct, 10:56, keiji kounoike <"kounoike A | T ma.Pikara.ne.jp"
wrote:
One way, but not so fast.
If your workbook's name is Book1.xls, this macro would create a file
named Book1.csv in the same folder with Book1.xls

Sub Worbook2Csv()
Dim Csvname As String, Pdir As String
Dim TmpWB As Workbook, AcWB As Workbook
Dim Fname() As String
Dim SelSh As Sheets, Wsh As Worksheet
Dim filenum
Dim i As Long

Pdir = ActiveWorkbook.path
Csvname = ActiveWorkbook.Name
Csvname = Left(Csvname, InStr(Csvname, ".") - 1)
ChDir Pdir
Set AcWB = ActiveWorkbook
Set SelSh = AcWB.Worksheets

SelSh.Copy
Set TmpWB = ActiveWorkbook

Application.DisplayAlerts = False
Application.ScreenUpdating = False

ReDim Fname(TmpWB.Worksheets.Count - 1)

For Each Wsh In TmpWB.Worksheets
* * *Wsh.Select
* * *TmpWB.SaveAs Filename:=Csvname & "Tmp" & CStr(i), _
* * * * *FileFormat:=xlCSV, CreateBackup:=False
* * *Fname(i) = TmpWB.FullName
* * *i = i + 1
Next
TmpWB.Close

Open Fname(0) For Append As #1
For i = 1 To UBound(Fname)
* * *filenum = FreeFile
* * *Open Fname(i) For Input As #filenum
* * *Do While Not EOF(filenum)
* * * * *Line Input #filenum, tmp
* * * * *Print #1, tmp
* * *Loop
* * *Close #filenum
* * *Kill Fname(i)
Next
Close #1

Name Fname(0) As Replace(Fname(0), "Tmp0", "")

Keiji

cyew wrote:
Hi

I am new to VBA.

I have data on a number of sheets and I would like to write VBA codes
to loop through each sheet and write out the data in one CSV file.

eg

Sheet1
1 *one
2 *two
3 *three

Sheet2
4 *four
5 *five
6 *sixe

I would like my output CSV file to be:
1,one
2,two
3,three
4,four
5,five
6,sive

Thanks
Chen



Thanks All for your help.

Chen