Save as csv file format

[sindhuja]

hi,

i have a file with multiple sheets. i want to save all the sheet as a seperate file in csv format using the coding.

semicolan seperated values should be output.

-sindhuja

[Aussiebear]

ActiveWorkbook.SaveAs Filename:= "c:\MyFile.csv", _ <--- change to suit path
 FileFormat:=xlCSV, CreateBackup:=False

[sindhuja]

Hi,

Thanks for the coding !

I need to save each sheet in a file as a separate csv file with the file name same as sheet name.

-Sindhuja

[Kenneth Hobs]

A csv file is a comma-separated value file, not semi-colon.

You can change the txt file extension to csv but that does not make it a csv file. It is delimited though so import methods will work.

Be sure to add the reference as explained in the comment.

Sub ExportSheets()
  Dim ws As Worksheet, exportPath As String, s As String
  exportPath = ThisWorkbook.Path & "\"
  For Each ws In Worksheets
   With ws
      .UsedRange.Copy
      s = Replace(getClipboard, vbTab, ";")
      StrToTXTFile exportPath & .Name & ".txt", s
   End With
  Next ws
  Application.CutCopyMode = False
End Sub

Sub StrToTXTFile(filePath As String, str As String)
  Dim hFile As Integer
  hFile = FreeFile
  Open filePath For Output As #hFile
  If str <> "" Then Print #hFile, str
Close hFile
End Sub

Function getClipboard() As String
'Add, Tools > References > Reference: Microsoft Forms 2.0 Object
    Dim MyData As DataObject
On Error Resume Next
    Set MyData = New DataObject
    MyData.GetFromClipboard
    getClipboard = MyData.GetText
End Function

[shrivallabha]

Hi,

Thanks for the coding !

I need to save each sheet in a file as a separate csv file with the file name same as sheet name.

-Sindhuja

Put the code in a loop with a bit of logic and it should give you the result.

Public Sub SaveAllShtCSV()
Dim wbThis As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wbThis = ThisWorkbook
For i = 1 To wbThis.Sheets.Count
    wbThis.Sheets(i).Copy
    With ActiveWorkbook
        .SaveAs Filename:="c:\" & wbThis.Sheets(i).Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False 'Change path to suit
        .Close
    End With
Next i
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

[Kenneth Hobs]

Chip Pearson's routine can be used if you like. Use one of the two methods shown here to iterate through your sheets. http://www.cpearson.com/excel/ImpText.aspx

Shrivallabha's method needs Application.International(xlColumnSeparator) changed to semi-colon to meet your goal. I know how to change some other Local settings but not that one. If I knew which one that was modified in the registry, I could write code to change it.

[snb]

There you go:

HKEY_CURRENT_USER\Control Panel\International\sList


to read / write:

Sub snb()
  msgbox  CreateObject("wscript.shell").regread("HKEY_CURRENT_USER\Control Panel\International\sList")
  CreateObject("Wscript.shell").regwrite "HKEY_CURRENT_USER\Control Panel\International\sList", ";"
end Sub

[Kenneth Hobs]

Thanks for that sng. I had changed that one with an API routine but it did not change the delimiter character in the SaveAs routine.

My routine and Chips's works in the background so they are fast.

[snb]

@KH : the alias is snb

to keep it simple; independent of international settings:

sub snb()
sn=sheets(1).usedrange
for j= 1 to ubound(sn)
c01=c01 & vbcrlf & join(application.index(sn,j),Application.International(5))
next
createobject("scripting.filesystemobject").createtextfile("G:\OF\sheet.csv").write mid(c01,3)
end sub

[Aussiebear]

I don't know...... sng sort of suits you

[Kenneth Hobs]

Sorry about the typo snb. I don't always get it right the first time. An unlimited Edit option would be good for me. At least I get some time to Edit a post and make use of that, alot. My fingers don't always do what my brain tells it to, especially when typing in the dark. Guess I need to get one of those lighted keyboards as the desk light is a bit hard on my eyes.

I will test the sList trick on xp today. That is the method that many have posted in the past but it did not work for my Vista64.

The sList trick is best if it worked, if you want Excel to later open the "csv" file properly.

[snb]

@KH

Search in regedit for "Control Panel' or 'International'

I think it's best not to change the registry at all (see my last suggestion).

[Kenneth Hobs]

Yes, I well know how to modify the registry manually, through API commands, some Excel commands, through BAT file methods, Shell() methods, and registry edit methods as you posted.

If the registry is not changed (if the method works at all), then opening the semi-colon file in Excel will put all of the data into column A. But as I said earlier, you can change the file extension to csv but that does not make it a csv file.

I will try your method later. Obviously, background methods such as yours, mine, and Chip's work faster than foreground methods.

[Kenneth Hobs]

Hopefully sindhuja, has found a solution that meets her? goal.

Modifying the sList value worked for XP in the sense that Excel opended the semi-colon delimited file properly. Of course I had to close and reopen Excel after making the registry change. Doing that did not create the semi-colon delimited file using shrivallabha's method.

Here is an example where I commented Chip's routine and added snb's routine.

Sub snb()
  Dim ws As Worksheet, exportPath As String
  Dim sn As Variant, j As Long, c01 As String
exportPath = ThisWorkbook.Path & "\"
  For Each ws In Worksheets
    With ws
    ' http://www.cpearson.com/excel/ImpText.aspx
    ' ExportToTextFile ThisWorkbook.Path & "" & .Name & ".txt" _
        , ";", False, False
sn = .UsedRange
      c01 = ""
      For j = 1 To UBound(sn)
        'c01 = c01 & vbCrLf & Join(Application.Index(sn, j), _
          Application.International(xlListSeparator))
        c01 = c01 & vbCrLf & Join(Application.Index(sn, j), ";")
      Next j
      CreateObject("scripting.filesystemobject").createtextfile( _
        exportPath & ws.Name & ".csv").write Mid(c01, 3)
End With
  Next ws
End Sub

[shrivallabha]

Now I am struggling to understand what was Sindhuja's aim at first. As I see from the flag, OP is from India. And here, the convention and the delimiter is comma "," and not semicolon ";".

And OP posted [after Aussiebear's post] that the the code should loop through all sheets so I gave a looping syntax.

Its all haze for me now.

[Kenneth Hobs]

The OP wanted a semi-colon delimited file. I am not sure what is confusing about that. IF the OP never comes back, it is a mute point.

[shrivallabha]

The OP wanted a semi-colon delimited file. I am not sure what is confusing about that. IF the OP never comes back, it is a mute point.

See post#3 [which is OP's reply to Aussiebear's post]:

Hi,

Thanks for the coding !

I need to save each sheet in a file as a separate csv file with the file name same as sheet name.

-Sindhuja

OP did not say that Aussiebear's method did not work. Instead OP says as quoted above so I posted a code which looped through. Does this clarify?

[Kenneth Hobs]

i have a file with multiple sheets. i want to save all the sheet as a seperate file in csv format using the coding.

Clear.

semicolan seperated values should be output.

Clear.

[Aussiebear]

is this an alternative?
http://www.dreamincode.net/forums/to...on-separation/

[Kenneth Hobs]

That uses vb.net so no. It creates a comma separated file using the first method posted. That is slow. It then reads it back and converts commas to semi-colons and then writes back to the file. That is inefficient. It can cause problems too as data may contain commas.

I ran the 4 routines using this: http://www.vbaexpress.com/forum/showthread.php?t=30477

The routines were Aussiebear/shrivallabha, mine, Chip, and snb. I created two sheets. A1=Number, B1=Letter, A2=0, B2=Sheet1, A3=Row(), B3=B2 & "_" & A3. A3:B3 were copied down to row 100. I copied sheet1 to sheet2 and changed B2=Sheet2. This makes column B values progressively larger.

Run 1 in order (seconds): 0.452, 0.031, 0.047, failed at:

c01 = c01 & vbCrLf & Join(Application.Index(sn, j), ";")

Run 2: 0.780, 0.031, 0.063, failed


Shorted the formula in column B to make the string values much smaller. B3=$B$2 & row() and copy down to row 100.

Run 1: 0.421, 0.031, 0.047, 0.078

Run 2: 0.327. 0.031. 0.109, 0.031

Private Declare Function GetTickCount Lib "kernel32.dll" () As Long
 
Public Sub Test()
    Dim lStart As Long
    Dim lEnd As Long
    lStart = GetTickCount
'Your procedure call here:
    'SaveAllShtCSV
    'ExportSheets
    snb
lEnd = GetTickCount
    MsgBox Format$((lEnd - lStart) / 1000&, "0.000 ""Seconds"""), vbInformation, "Time Elapsed"
End Sub

Sub snb()
    Dim ws As Worksheet, exportPath As String
    Dim sn As Variant, j As Long, c01 As String
exportPath = ThisWorkbook.Path & "\"
    For Each ws In Worksheets
        With ws
              'http://www.cpearson.com/excel/ImpText.aspx
              'ExportToTextFile ThisWorkbook.Path & "\" & .Name & ".txt" _
            , ";", False, False
sn = .UsedRange
            c01 = ""
            For j = 1 To UBound(sn)
                 'c01 = c01 & vbCrLf & Join(Application.Index(sn, j), _
                Application.International(xlListSeparator))
                c01 = c01 & vbCrLf & Join(Application.Index(sn, j), ";")
            Next j
            CreateObject("scripting.filesystemobject").createtextfile( _
            exportPath & ws.Name & ".csv").Write Mid(c01, 3)
End With
    Next ws
End Sub