TO convert Excel entire workbook in csv format

[pritee]

Hi

I have excel workbbok with around 20 sheet. I want to convert it in csv format so that i will get all sheet in single csv file.

[patel]

Sub SheetsToCSV()
Application.DisplayAlerts = False
dpath = "C:\test\" ' <<< to be changed
For Each ws In Worksheets
ws.Copy
ActiveWorkbook.SaveAs Filename:=dpath & ActiveSheet.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close
Next ws
Application.DisplayAlerts = True
End Sub

[Kenneth Hobson]

I guess the most efficient way would be to export the data using ADO.

If you export each sheet as patel explained, you can use this method providing you did not add a header/fieldnames row.
[SOLVED] Import multiple files from same directory (http://www.excelforum.com/excel-programming-vba-macros/939533-import-multiple-files-from-same-directory.html?p=3326646)


'http://spreadsheetpage.com/index.php/tip/getting_a_list_of_file_names_using_vba/
Sub MakeCSVMasters()
Dim p As String, x() As Variant, i As Integer

p = ThisWorkbook.Path & "\?-?????? *.csv"
x() = GetFileList(p)
Select Case IsArray(x)
Case True 'files found
'MsgBox UBound(x), , "Count of Found Files"
'Sheets("Sheet1").Range("A:A").Clear
'For i = LBound(x) To UBound(x)
' Sheets("Sheet1").Cells(i, 1).Value = x(i)
'Next i
MakingCSVMasters ThisWorkbook.Path, x()
Case False 'no files found
MsgBox "No matching files"
End Select
End Sub

Sub MakingCSVMasters(pPath As String, a() As Variant)
Dim x() As Variant, y() As Variant, xv As Variant, yv As Variant
Dim z() As Variant, zv As Variant, i As Integer, s As String

x() = UniqueArray(a)
'MsgBox Join(x, vbLf)

ReDim y(LBound(x) To UBound(x))
i = LBound(x) - 1
For Each xv In x()
'MsgBox Join(Filter(a(), xv, True), " ")
'Add Parent Path, pPath, and embed quotes around full file name
'and bulid string for COPY
s = vbNullString
i = i + 1
For Each zv In Filter(a(), xv, True)
'+ sign is the COPY concatenation operator
s = s & "+" & """" & pPath & "\" & zv & """"
Next zv
y(i) = Right(s, Len(s) - 1) 'Trim first "+" character
'MsgBox Join(y(), vbLf)
Next xv

For i = LBound(y) To UBound(y)
s = "cmd /c Copy " & y(i) & " " & """" _
& pPath & "\Master_" & Left(x(i), 8) & ".csv" & """"
'Debug.Print s
Shell s, vbHide
Next i
End Sub

Function UniqueArray(inArray() As Variant) As Variant
Dim it As Variant, sn() As Variant, c00 As Variant
With CreateObject("scripting.dictionary")
For Each it In inArray()
c00 = .Item(Left(CStr(it), 8))
Next
sn = .Keys ' the array .keys contains all unique keys
'MsgBox Join(sn, vbLf) ' you can join the array into a string
End With
UniqueArray = sn
End Function


Function GetFileList(FileSpec As String) As Variant
' Returns an array of filenames that match FileSpec
' If no matching files are found, it returns False

Dim FileArray() As Variant
Dim FileCount As Integer
Dim FileName As String

On Error GoTo NoFilesFound

FileCount = 0
FileName = Dir(FileSpec)
If FileName = "" Then GoTo NoFilesFound

' Loop until no more matching files are found
Do While FileName <> ""
FileCount = FileCount + 1
ReDim Preserve FileArray(1 To FileCount)
FileArray(FileCount) = FileName
FileName = Dir()
Loop
GetFileList = FileArray
Exit Function

' Error handler
NoFilesFound:
GetFileList = False
End Function

[patel]

I guess the most efficient way would be to export the data using ADO.
If you export each sheet as patel explained, you can use this method providing you did not add a header/fieldnames row.

can you explain better what you mean ?

[snb]

This is sufficient:


Sub M_snb()
For Each sh In Sheets
sn = sh.UsedRange
For j = 1 To UBound(sn)
c00 = c00 & vbCr & Join(Application.Index(sn, j, 0), ",")
Next
Next

CreateObject("scripting.filesystemobject").createtextfile("G:\OF\summary.csv").write Mid(c00, 2)
End Sub

[Kenneth Hobson]

Patel, the op said:

...get all sheet in single csv file.
Either "...export all sheets into a single csv file" or "...export each sheet into separate csv files." was the intent. I read it as the former. You can see the referenced thread for more info about that users need for one master csv file.

[patel]

@Kenneth
Sorry, I misread the initial post

# snb
For j = 1 To UBound(sn) ???????????

[Kenneth Hobson]

Patel, maybe you read it right and I read it wrong. Sometimes I read well and sometimes not. Only pritee knows for sure, or your hair dresser....

[snb]

For j = 1 To UBound(sn)[/B] ???????????

Did you test the macro?
If yes, what is your question (beside the question marks) ?

[patel]

yes I did, run-time error 13 (excel 2010)

[snb]

Then you have some empty sheets ?
In my workbook it works flawlessly (Excel 2010)

[patel]

My apologies, you are right, I had some empty sheets