Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Save the entire sheet as UTF8

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

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

  • #2
    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

    Comment


    • #3
      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:

      Code:
      '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
      
      tOpenPath = "C:\" & cText & ".txt"
      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
      fsT.LoadFromFile tOpenPath
      
      '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.

      Comment


      • #4
        Re: Save the entire sheet as UTF8

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

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

        Comment


        • #5
          Re: Save the entire sheet as UTF8

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

          Comment


          • #6
            Re: Save the entire sheet as UTF8

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

            Comment


            • #7
              Re: Save the entire sheet as UTF8

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

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

              Comment

              Trending

              Collapse

              There are no results that meet this criteria.

              Working...
              X