Thank you for your help but quite simply, no! It's saving a file as
UTF8 which is a specific encoding format, supporting more characters
than other formats. Excel does not natively support UTF8 despite it
being widely used. This code will allow you to do that, which I've
now tidied up and simplified from the original code I adapted. Please
see below:
<code starts>
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
<code ends>
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!
On Jun 14, 4:29 pm, ashish koul <koul.ash...@gmail.com> wrote:
> do you want to save your workbook in text and csv format.check links below
>
> in
> texthttp://www.compshack.com/visual-basic/save-file-using-visual-basic-ex...http://www.vbaexpress.com/kb/getarticle.php?kb_id=805
>
> in csvhttp://blogs.technet.com/b/heyscriptingguy/archive/2005/03/22/how-can...
>
> http://www.ozgrid.com/forum/showthread.php?t=92436&page=1
>
> On Tue, Jun 14, 2011 at 5:19 PM, Major Bandwith
> <majorbandw...@gmail.com>wrote:
>
>
>
>
>
>
>
>
>
> > 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 starts>
>
> > '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
>
> > <code ends>
>
> > 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.
>
> > On Jun 13, 11:43 am, Major Bandwith <majorbandw...@gmail.com> wrote:
> > > Hi all,
>
> > > I have some Excel sheets that I want to save as a UTF8 file in .txt
> > > and .csv formats, using a VBA macro. I've found the following code:
>
> > > <code starts>
>
> > > '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
>
> > > <code ends>
>
> > > ..and that saves a single line of text as UTF8 format very well to
> > > a .txt format file. However, I want to save an entire sheet.
>
> > > So firstly, can it be done using this method? And secondly, would
> > > there be any problems using this to save it as a .csv file?
> > > Ultimately, I won't be launching this as a function but as a macro.
>
> > > Thank you very much in advance,
>
> > > MB
>
> > --
>
> > ----------------------------------------------------------------------------------
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to excel-macros@googlegroups.com
>
> > <><><><><><><><><><><><><><><><><><><><><><>
> > Like our page on facebook , Just follow below link
> >http://www.facebook.com/discussexcel
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *akoul*.*blogspot*.com <http://akoul.blogspot.com/>http://akoul.posterous.com/
> *akoul*.wordpress.com <http://akoul.wordpress.com/>
> My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
>
> P Before printing, think about the environment.
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
