UTF-8 Format

[Daviadio]

Hi

I need to export selected cells as an UTF-8 format compatible file.

I am ok if it were just a test file - but have no idea how to ensure it is UTF-8 compatible.

Any leads/pointers or assistance appreciated. I am happy to post once the solution once I have it working

Cheers

 

[yky]

Try just saving as txt file. It works for me.

 

[Domenic]

The following macro is based on a solution offered by Vladimir in the following thread...

http://www.mrexcel.com/forum/excel-questions/707325-export-excel-text-file-utf16-encoded.html

Try...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit
[/COLOR]
[COLOR=darkblue]Sub[/COLOR] SaveSelectionAsUTF8()

    [COLOR=darkblue]Dim[/COLOR] oStream             [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sDestFolder         [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sFilename           [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] vData               [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] aCols()             [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] aRows()             [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sText               [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] RowCount            [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] ColCount            [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] r                   [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] c                   [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]If[/COLOR] TypeName(Selection) <> "Range" [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    
    [COLOR=darkblue]Const[/COLOR] sListSep [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR] = vbTab [COLOR=green]'or ","[/COLOR]
    [COLOR=darkblue]Const[/COLOR] sLineSep [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR] = vbCrLf
    
    sDestFolder = "C:\Users\Domenic\Desktop\" [COLOR=#008000]'change the path to the source folder accordingly[/COLOR]
    [COLOR=darkblue]If[/COLOR] Right(sDestFolder, 1) <> "" [COLOR=darkblue]Then[/COLOR] sDestFolder = sDestFolder & "\"
    
    sFilename = "sample.txt" [COLOR=#008000]'change the file name accordingly[/COLOR]
    
    vData = Selection.Value
    
    RowCount = [COLOR=darkblue]UBound[/COLOR](vData, 1)
    ColCount = [COLOR=darkblue]UBound[/COLOR](vData, 2)
    
    [COLOR=darkblue]ReDim[/COLOR] aRows(1 [COLOR=darkblue]To[/COLOR] RowCount)
    [COLOR=darkblue]ReDim[/COLOR] aCols(1 [COLOR=darkblue]To[/COLOR] ColCount)
    
    [COLOR=darkblue]For[/COLOR] r = 1 [COLOR=darkblue]To[/COLOR] RowCount
        [COLOR=darkblue]For[/COLOR] c = 1 [COLOR=darkblue]To[/COLOR] ColCount
            aCols(c) = vData(r, c)
        [COLOR=darkblue]Next[/COLOR] c
        aRows(r) = Join(aCols, sListSep)
    [COLOR=darkblue]Next[/COLOR] r
        
    sText = Join(aRows, sLineSep)
    
    [COLOR=darkblue]Set[/COLOR] oStream = CreateObject("ADODB.Stream")
    
    [COLOR=darkblue]With[/COLOR] oStream
        .Charset = "UTF-8"
        .Mode = 3 [COLOR=green]'adModeReadWrite[/COLOR]
        .Type = 2 [COLOR=green]'adTypeText[/COLOR]
        .Open
        .WriteText sText
        .SaveToFile sDestFolder & sFilename, 2 [COLOR=green]'1=adSaveCreateNotExist, 2=adSaveCreateOverWrite[/COLOR]
        .Close
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!