CSV export macro in UTF-8

[JimmyG]

Hi there!

Have been using this macro to export as a CSV. I need it modified to export in UTF-8
Can someone with more VB skills than me help please







Public Sub ExportToTextFile(FName As String, _
Sep As String, SelectionOnly As Boolean)

Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String


Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

If SelectionOnly = True Then
With Selection
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
Else
With ActiveSheet.UsedRange
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
End If

Open FName For Output Access Write As #FNum

For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = ""
Else
CellValue = Cells(RowNdx, ColNdx).Text
End If
WholeLine = WholeLine & CellValue & Sep
Next ColNdx
WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

End Sub
Public Sub DoTheExport()
Dim FName As Variant
Dim Sep As String

FName = Application.GetSaveAsFilename()
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Sep = InputBox("Enter a single delimiter character (e.g., comma or semi-colon)", _
"Export To Text File")

ExportToTextFile CStr(FName), Sep, _
MsgBox("Do You Want To Export The Entire Worksheet?", _
vbYesNo, "Export To Text File") = vbNo
End Sub

 

[xenou]

I think you could convert your text I/O to make use of the File System Object.
See Richard Schollars example here:
http://www.mrexcel.com/forum/showthread.php?t=338526

Basically, you'll

• create a FSO object,
• use it's createtextfile method to open up a textstream
• use the writeline method to write your lines
• close the textstream

To use the FSO object with intellisense, in the Visual basic editor goto Tools | References and find the box for Microsoft Scripting Runtime and check it.

Though I'm not 100% sure that you'll get UTF-8 encoding, as opposed to say Windows-1252 or what have you. Try it and see if it works I guess.

 

[xenou]

Actually, just testing this myself and FSO files saved in "unicode" appear to be UCS-2 Little Endian.

Not sure ... a last resort is just open the file with a text editor and save it with UTF-8 encoding. What's the problem with the Ascii text here?

Note:
Found several references to using an ADODB.Stream (I've read about this before - ADODB streams seem to have stronger unicode support). This site gives an example that you could use to "Convert" your file, after you've run your existing code:
http://www.robvanderwoude.com/vbstech_files_utf8.php

 

[xenou]

It would be interesting if you had more details about your problem with the encoding.

As far as I can tell, UTF-8 and Ascii text should be the same unless you are actually working with a foreign alphabet - i.e., both would be single byte storage and use the same values, at least as far as the first 127 characters are concerned.