Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Save the entire sheet as UTF8

  1. #1
    Join Date
    11th May 2007
    Posts
    25

    Save the entire sheet as UTF8

    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:

    VB:
     '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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    11th May 2007
    Posts
    25

    Re: I have code to save a string as UTF8 - I need to adapt to save entire SHEET as UT

    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    11th May 2007
    Posts
    25

    Re: Save the entire sheet as UTF8

    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:

    VB:
     
     
     '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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    11th May 2007
    Posts
    25

    Re: Save the entire sheet as UTF8

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

    VB:
    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!

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    23rd February 2011
    Posts
    27

    Re: Save the entire sheet as UTF8

    doesn't work it seems.
    Last edited by cytop; November 18th, 2011 at 21:08. Reason: Removed quoted post

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    11th May 2007
    Posts
    25

    Re: Save the entire sheet as UTF8

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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    9th October 2004
    Posts
    36

    Re: Save the entire sheet as UTF8

    Quote Originally Posted by schnide View Post
    I've now tidied up and simplified from the original code I adapted. Please see below:

    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 2
    Last Post: April 30th, 2011, 22:46
  2. Replies: 3
    Last Post: November 4th, 2010, 19:23
  3. Copy entire row to other sheet based on condition
    By junction42 in forum EXCEL HELP
    Replies: 2
    Last Post: October 24th, 2010, 10:28
  4. Entire Sheet Into A Variable?
    By yoreciv in forum EXCEL HELP
    Replies: 9
    Last Post: February 21st, 2007, 07:43
  5. Print entire workbook by sheet
    By teresa.harrison in forum EXCEL HELP
    Replies: 1
    Last Post: June 6th, 2006, 21:48

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno