View Single Post
 
Old July 3rd, 2007, 04:51 AM
Shasur Shasur is offline
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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