PDA

View Full Version : save as csv file format



sindhuja
06-30-2012, 11:48 PM
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
07-01-2012, 12:51 AM
ActiveWorkbook.SaveAs Filename:= "c:\MyFile.csv", _ <--- change to suit path
FileFormat:=xlCSV, CreateBackup:=False

sindhuja
07-01-2012, 07:25 AM
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
07-01-2012, 09:03 AM
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
07-01-2012, 09:15 AM
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
07-01-2012, 12:23 PM
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
07-01-2012, 02:58 PM
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
07-01-2012, 04:22 PM
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
07-02-2012, 12:04 AM
@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
07-02-2012, 01:00 AM
I don't know...... sng sort of suits you

Kenneth Hobs
07-02-2012, 05:17 AM
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
07-02-2012, 05:40 AM
@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
07-02-2012, 05:52 AM
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
07-02-2012, 06:54 AM
Hopefully sindhuja (http://www.vbaexpress.com/forum/member.php?u=12503), 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 (http://www.vbaexpress.com/forum/member.php?u=27076)'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
07-02-2012, 08:08 AM
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
07-02-2012, 08:33 AM
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
07-02-2012, 08:51 AM
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
07-02-2012, 09:33 AM
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
07-02-2012, 03:51 PM
is this an alternative?
http://www.dreamincode.net/forums/topic/238774-converting-excel-to-csv-semicolon-separation/

Kenneth Hobs
07-02-2012, 07:16 PM
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 (http://www.vbaexpress.com/forum/member.php?u=27076), 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

snb
07-03-2012, 12:56 AM
@KH

the method 'saveas' has several arguments:

filetype:

if you use xlcsv, xlCSVMSDOS or xlCSVWindows the fieldseparator is comma.
if you use xlTextMSDOS or xlTextWindows, the fieldseparator is Tab

code page:
I tried the codepages 437 and 850, but that didn't result in another result than if omitted

Local: (since Office 2007)
if you set this argument to True:
if you use xlcsv, xlCSVMSDOS or xlCSVWindows the fieldseparator is semicolumn.
if you use xlTextMSDOS or xlTextWindows, the fieldseparator is Tab

In my case the international fieldseparator is the semicoumn. So local=true forces the program to use this international setting.

I suppose that if your international setting is comma, setting the argument local to true won't have any visual effect. In that case you have to adapt the international setting first, before running the code and using the argument local=true.


This is the code I used

Codepage

ThisWorkbook.SaveAs "G:\OF\snb_001b.csv", xlCSV, , , , , , , , 437
ThisWorkbook.SaveAs "G:\OF\snb_002b.csv", xlCSVMSDOS, , , , , , , , 437
ThisWorkbook.SaveAs "G:\OF\snb_003b.csv", xlCSVWindows, , , , , , , , 437
ThisWorkbook.SaveAs "G:\OF\snb_004b.csv", xlTextMSDOS, , , , , , , , 437
ThisWorkbook.SaveAs "G:\OF\snb_005b.csv", xlTextWindows, , , , , , , , 437
result
' csv separator comma
' text separator tab

Local= True

ThisWorkbook.SaveAs "G:\OF\snb_001a.csv", xlCSV, , , , , , , , , , True
ThisWorkbook.SaveAs "G:\OF\snb_002a.csv", xlCSVMSDOS, , , , , , , , , , True
ThisWorkbook.SaveAs "G:\OF\snb_003a.csv", xlCSVWindows, , , , , , , , , , True
ThisWorkbook.SaveAs "G:\OF\snb_004a.csv", xlTextMSDOS, , , , , , , , , , True
ThisWorkbook.SaveAs "G:\OF\snb_005a.csv", xlTextWindows, , , , , , , , , , True

Result
' csv semicolumn as separator
' text separator tab

Local= default (False)

ThisWorkbook.SaveAs "G:\OF\snb_001.csv", xlCSV
ThisWorkbook.SaveAs "G:\OF\snb_002.csv", xlCSVMSDOS
ThisWorkbook.SaveAs "G:\OF\snb_003.csv", xlCSVWindows
ThisWorkbook.SaveAs "G:\OF\snb_004.csv", xlTextMSDOS
ThisWorkbook.SaveAs "G:\OF\snb_005.csv", xlTextWindows

Result
' CSV separator comma
' Text separator Tab

From the helpfile on argument 'Local'

Local
' True saves files against the language of Microsoft Excel (including control panel settings).
' False (default) saves files against the language of Visual Basic for Applications (VBA)
' (which is typically US English unless the VBA project where Workbooks.Open is run from is an old internationalized XL5/95 VBA project).

Kenneth Hobs
07-03-2012, 07:00 AM
Thanks for that snb. I had thought about the Local parameter option but had not used it. Of course if you do change the sList value, you must restart Excel for the Local parameter to use it. I guess that is why Microsoft did create a write method for it.

While this is the slowest method, it is probably one of the more reliable methods.

I did not make a separate routine to set sList but you can see from the code how to do it via an API method or a wScript method.

Declare Function SetLocaleInfo _
Lib "kernel32" Alias "SetLocaleInfoA" ( _
ByVal Locale As Long, _
ByVal LCType As Long, _
ByVal lpLCData As String) As Boolean

Declare Function GetLocaleInfo _
Lib "kernel32" Alias "GetLocaleInfoA" ( _
ByVal Locale As Long, _
ByVal LCType As Long, _
ByVal lpLCData As String, _
ByVal cchData As Long) As Long

Const LOCALE_USER_DEFAULT = &H400
Const LOCALE_SLIST = &HC ' list item separator
Const LOCALE_SDECIMAL = &HE ' decimal separator
Const LOCALE_STHOUSAND = &HF 'thousand separator
Sub t()
Range("A7").Value = GetInfo(LOCALE_SLIST)
SetLocalSetting LOCALE_SLIST, ";"
Range("A8").Value = GetInfo(LOCALE_SLIST)
End Sub

Public Function GetInfo(ByVal lInfo As Long) As String
Dim Buffer As String, Ret As String
Buffer = String$(256, 0)
Ret = GetLocaleInfo(LOCALE_USER_DEFAULT, lInfo, Buffer, Len(Buffer))
If Ret > 0 Then
GetInfo = Left$(Buffer, Ret - 1)
Else
GetInfo = ""
End If
End Function

' http://www.vbaexpress.com/forum/showthread.php?t=42769
Public Sub SaveAllShtCSV()
Dim wbThis As Workbook, i As Integer
Dim colDelimiter As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False
colDelimiter = Application.International(xlColumnSeparator)
'colDelimiter=getinfo(LOCALE_SLIST)
'colDelimiter=CreateObject("Wscript.shell").regread("HKEY_CURRENT_USER\Control Panel\International\sList")

SetLocalSetting LOCALE_SLIST, ";"
'CreateObject("Wscript.shell").regwrite "HKEY_CURRENT_USER\Control Panel\International\sList", ";"

On Error GoTo EndSub

Set wbThis = ThisWorkbook
For i = 1 To wbThis.Sheets.Count
wbThis.Sheets(i).Copy
With ActiveWorkbook
.SaveAs FileName:=ThisWorkbook.Path & "\" & wbThis.Sheets(i).Name & ".csv", _
FileFormat:=xlCSV, CreateBackup:=False, Local:=True 'Change path to suit
.Close
End With
Next i

EndSub:
Application.DisplayAlerts = True
Application.ScreenUpdating = True
SetLocalSetting LOCALE_SLIST, colDelimiter
End Sub

sindhuja
07-03-2012, 09:05 PM
Thank you everyone for the coding and i tried the above and below is the error message.

SetLocalSetting - compiler error sub or function not defined.

Also pls explain the coding as it is at high level which helps to understand better.

-Sindhuja

snb
07-04-2012, 12:05 AM
You can find all explanation using the F1 key on your keybord in the VBEditor.

Kenneth Hobs
07-04-2012, 02:13 AM
Sorry, I had SaveLocalSetting in another Module. Notice that API declarations and constants are at the top of a Module.

Declare Function GetUserDefaultLCID% Lib "kernel32" ()

Function SetLocalSetting(LC_CONST As Long, Setting As String) As Boolean
Call SetLocaleInfo(GetUserDefaultLCID(), LC_CONST, Setting)
End Function
As snb said, in the VBE, press F1 in or near a command word for help. Of course F2 lets you browse and see even more. If you debug by pressing F8 at each line, hover mouse over set variables to see results or enable the Locals Window in the View menu and then debug by F8.

snb
07-04-2012, 04:12 AM
@KH

Only out of curiosity: why Set wbThis ?, because

For Each sh In ThisWorkbook.Sheets
sh.Copy
With ActiveWorkbook
.SaveAs sh.Parent.Path & "\" & sh.Name & ".csv", xlCSV, , , , , , , , , True
.Close False
End With
Next

secondly:
where do you get the API information from

shrivallabha
07-04-2012, 06:09 AM
@KH

Only out of curiosity: why Set wbThis ?, because

For Each sh In ThisWorkbook.Sheets
sh.Copy
With ActiveWorkbook
.SaveAs sh.Parent.Path & "\" & sh.Name & ".csv", xlCSV, , , , , , , , , True
.Close False
End With
Next
secondly:
where do you get the API information from
@snb
Ken's first part is from my post so I think I should answer it. Its quirk rather than requirement. So it can be skipped completely if you don't want / feel redundant.

2nd part I will leave to KH.

snb
07-04-2012, 06:33 AM
@shri

Thanks :)

Kenneth Hobs
07-04-2012, 07:24 AM
What, give away my secrets and make people think less of me?

I use three sources when I am trying to find an API method.

1. http://allapi.mentalis.org/agnet/apiguide.shtml
2. wind32api.txt - http://support.microsoft.com/kb/178020
3. Search Engines

Another tool that I like to use sometimes is a Window spy. I use it when I need to find or set focus for a SendKeys() sort of solution though I try to avoid SendKeys() when I can. This tool also generates some API routines. The API spy is 2nd from the bottom. http://patorjk.com/blog/software/

If you prefer books, look for books by Dan Appleman. Most of his books are older but the concepts still work for 32bit computers and usually for 64bit. 64bit API routines in the DLL's won't be covered in his books. http://www.amazon.com/s/ref=nb_sb_noss?url=search-alias%3Dstripbooks&field-keywords=API+Applemans

shrivallabha
07-04-2012, 07:39 AM
There's some good reference if you've this book:
http://www.amazon.com/Vba-Developers-Handbook-Ken-Getz/dp/0782119514

snb
07-04-2012, 08:06 AM
@KH

You were the last person to think about regarding to having secrets.
Sometimes I have the feeling you even reveal your thoughts (....)
But my admiration won't get any blow by revealing these links.

@shri

I will dive into that too.

sindhuja
07-06-2012, 09:09 AM
Hello..

Am getting the error message in the below line.. pls help

Declare Function GetUserDefaultLCID% Lib "kernel32.dll" ()

-Sindhuja

Kenneth Hobs
07-06-2012, 01:32 PM
Did you put the DLL functions at the top of the Module?

sindhuja
07-09-2012, 04:21 AM
sorry.. am not aware of DLL. Pls let me know how to do the same

Kenneth Hobs
07-09-2012, 05:18 AM
If you are not aware of the kernel32.dll then where did you get:
Declare Function GetUserDefaultLCID% Lib "kernel32.dll" ()

The Declare Function's reference a zDLL and in this case, kernel32.dll. Those go at the top of a Module as do Constants.

It is a minor point though. You can use the DLL routines to get and set the registry entry but that can be done manually. Once done, close Excel and then Open. IF the DLL's are that much trouble, just use snb's code to modify the registry if you are not comfortable modifying the registry manually or you don't use my method.

sindhuja
08-07-2012, 07:02 AM
Hi Snb,

Still am breaking my head.. am not getting the result as expected.



Sub save()
Local= True
ThisWorkbook.SaveAs "C:\Users\sindhu\Desktop\snb_001a.csv", xlCSV, , , , , , , , , , True
ThisWorkbook.SaveAs "C:\Users\sindhu\Desktop\snb_002a.csv", xlCSVMSDOS, , , , , , , , , , True
ThisWorkbook.SaveAs "C:\Users\sindhu\Desktop\snb_003a.csv", xlCSVWindows, , , , , , , , , , True
ThisWorkbook.SaveAs "C:\Users\sindhu\Desktop\snb_004a.csv", xlTextMSDOS, , , , , , , , , , True
ThisWorkbook.SaveAs "C:\Users\sindhu\Desktop\snb_005a.csv", xlTextWindows, , , , , , , , , , True
End Sub



am getting the error message - syntax error on Local= True

Pls assist

snb
08-09-2012, 03:27 AM
Remove it from your code (I never entered it as part of VBA code)