Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Save the entire sheet as UTF8

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

    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.

    Excel Video Tutorials / Excel Dashboards Reports


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

    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
    27

    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.

    Excel Video Tutorials / Excel Dashboards Reports


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

    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!

    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
    27

    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:

    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

    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