Save the entire sheet as UTF8

[schnide]

Hi, I hope that the title was explanatory enough

In short, I have a sheet in Excel which I want to save as a UTF8 format text file. I previously thought it couldn't be done but I found the below and it definitely works in saving a text string as one:

 'Function saves cText in file, and returns 1 if successful, 0 if not
Public Function writeOut(cText As String, file As String) As Integer 
    On Error Goto errHandler 
    Dim fsT, tFilePath As String 
     
    tFilePath = file + ".txt" 
     
     'Create Stream object
    Set fsT = CreateObject("ADODB.Stream") 
     
     'Specify stream type – we want To save text/string data.
    fsT.Type = 2 
     
     'Specify charset For the source text data.
    fsT.Charset = "utf-8" 
     
     'Open the stream And write binary data To the object
    fsT.Open 
    fsT.writetext cText 
     
     'Save binary data To disk
    fsT.SaveToFile tFilePath, 2 
     
    Goto finish 
     
errHandler: 
    MsgBox (Err.Description) 
    writeOut = 0 
    Exit Function 
     
finish: 
    writeOut = 1 
End Function 

What I need to do is adapt the references to cText to save an entire named Excel worksheet in UTF8 format, instead of that single string of text. I would like to think this is quite straight forward, but my initial attempts haven't been successful.

Thank you greatly, as always, in advance.

[schnide]

I have also posted this thread at Excel Forum:

http://www.excelforum.com/excel-prog...t-as-utf8.html

..but as yet, no-one has been able to help with this one.

With thanks,

schnide

[schnide]

After a few days looking around, I seem to have been able to solve this myself despite never even having heard of what ADO Streams are before:

 
 
 'Function saves cText in file, and returns 1 if successful, 0 if not
 
Public Function writeOut2(cText As String, file As String) As Integer 
    On Error Goto errHandler 
    Dim fsT, tFilePath, tOpenPath As String 
     
    [B]tOpenPath = "C:\" & cText & ".txt"[/B] 
    tFilePath = "C:\" & file & ".txt" 
     
     'Create Stream object
    Set fsT = CreateObject("ADODB.Stream") 
     
     'Specify stream type – we want To save text/string data.
    fsT.Type = 2 
     
     'Specify charset For the source text data.
    fsT.Charset = "utf-8" 
     
     'Open the stream And write binary data To the object
    fsT.Open 
    [B]fsT.LoadFromFile tOpenPath[/B] 
     
     'Save binary data To disk
    fsT.SaveToFile tFilePath, 2 
     
    Goto finish 
     
errHandler: 
    MsgBox (Err.Description) 
    writeOut2 = 0 
    Exit Function 
     
finish: 
    writeOut2 = 1 
End Function 

Note the new function which, instead of inserting a new line of text into the stream capability, opens a whole file into it. The stream is then saved as a UTF8 file.

[schnide]

I've now tidied up and simplified from the original code I adapted. Please see below:

Sub SaveAsUTF8() 
     
    Dim fsT, tFileToOpen, tFileToSave As String 
     
    tFileToOpen = InputBox("Enter the name and location of the file to convert" & vbCrLf & "With full path and filename ie. C:\MyFolder\ConvertMe.Txt") 
    tFileToSave = InputBox("Enter the name and location of the file to save" & vbCrLf & "With full path and filename ie. C:\MyFolder\SavedAsUTF8.Txt") 
     
    tFileToOpenPath = tFileToOpen 
    tFileToSavePath = tFileToSave 
     
Set fsT = CreateObject("ADODB.Stream"): 'Create Stream object
fsT.Type = 2: 'Specify stream type – we want To save text/string data.
fsT.Charset = "utf-8": 'Specify charset For the source text data.
     
fsT.Open: 'Open the stream
fsT.LoadFromFile tFileToOpenPath: 'And write the file to the object stream
     
fsT.SaveToFile tFileToSavePath, 2: 'Save the data to the named path
     
End Sub 

I have performed a hash check on a file that I would convert using Notepad and a file I converted using this method. For reference, I used this method in Excel 2007. The hashes are exactly the same. I hope this helps someone!

[fakelogin]

doesn't work it seems.

[schnide]

You'll have to be more specific about that, because for me, it works perfectly.

[Dave Taylor]

I've now tidied up and simplified from the original code I adapted. Please see below:

Sub SaveAsUTF8() 
     
    Dim fsT, tFileToOpen, tFileToSave As String 
     
    tFileToOpen = InputBox("Enter the name and location of the file to convert" & vbCrLf & "With full path and filename ie. C:\MyFolder\ConvertMe.Txt") 
    tFileToSave = InputBox("Enter the name and location of the file to save" & vbCrLf & "With full path and filename ie. C:\MyFolder\SavedAsUTF8.Txt") 
     
    tFileToOpenPath = tFileToOpen 
    tFileToSavePath = tFileToSave 
     
Set fsT = CreateObject("ADODB.Stream"): 'Create Stream object
fsT.Type = 2: 'Specify stream type – we want To save text/string data.
fsT.Charset = "utf-8": 'Specify charset For the source text data.
     
fsT.Open: 'Open the stream
fsT.LoadFromFile tFileToOpenPath: 'And write the file to the object stream
     
fsT.SaveToFile tFileToSavePath, 2: 'Save the data to the named path
     
End Sub 

I have performed a hash check on a file that I would convert using Notepad and a file I converted using this method. For reference, I used this method in Excel 2007. The hashes are exactly the same. I hope this helps someone!

I want to save the contents of a sheet as a text file but with UTF8 coding, this thread is titled what I am looking for but the code is looking to convert a file.

how do I use this script to export a sheet as UTF8?

Re Dave