to whom it may concern...
a vba code,
data in active sheet,
expected results in path - file "c:\tmp\abcd.csv"
Sub ActiveSheet_to_csv_utf8()
'Dec 14, 2014
Const sDelim As String = "," '<< select comma or semicolon
Dim ws As Worksheet
Set ws = ActiveSheet
Dim r As Long, c As Long, i As Long, j As Long
r = ws.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
c = ws.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Dim v() As Variant
ReDim v(1 To c)
Dim sFile As String
sFile = "c:\tmp\abcd.csv" '<< change
Dim obj As Object
Set obj = CreateObject("ADODB.Stream")
obj.Type = 2
obj.Charset = "utf-8"
obj.Open
For i = 1 To r
For j = 1 To c
v(j) = ws.Cells(i, j).Text ' << or .Value
Next
obj.WriteText Join(v, sDelim), 1
Next
obj.SaveToFile sFile, 2
Dim Npad As Variant
Npad = Shell("C:\WINDOWS\notepad.exe " & sFile, 1)
End Sub
note
csv file is open via notepad
check encoding:
select... File > Save As (you can see encoding: UTF-8 )
select... Cancel
close Notepad
xxxxxxxxxxxxxxxxxxxxxxx
[update Apr 21, 2016]
also,
convert active sheet to csv file
on desktop, in a folder named: yyyymmdd_hhmmss
csv file name: activesheet name
Sub ActiveSheet_to_csv_utf8_02()
'Apr 21, 2016
Const sDelim As String = "," '<< select comma or semicolon
Dim ws As Worksheet
Set ws = ActiveSheet
Dim sFile As String
sFile = "c:\users\Tasos\Desktop" & Format(Now, "yyyymmdd_hhmmss") '<< change path
MkDir sFile
sFile = sFile & "" & ws.Name & ".csv"
Dim r As Long, c As Long, i As Long, j As Long
r = ws.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
c = ws.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Dim v() As Variant
ReDim v(1 To c)
Dim obj As Object
Set obj = CreateObject("ADODB.Stream")
obj.Type = 2
obj.Charset = "utf-8"
obj.Open
For i = 1 To r
For j = 1 To c
v(j) = ws.Cells(i, j).Text ' << or .Value
Next
obj.WriteText Join(v, sDelim), 1
Next
obj.SaveToFile sFile, 2
Dim Npad As Variant
Npad = Shell("C:\WINDOWS\notepad.exe " & sFile, 1)
End Sub