Re: $$Excel-Macros$$ Re: Saving an Excel sheet as a UTF8 .txt and .csv - have code, need modifications!
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:
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!
On Jun 14, 4:29 pm, ashish koul 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
> 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:
>
> >
>
> > '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.
>
> > On Jun 13, 11:43 am, Major Bandwith 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:
>
> > >
>
> > > '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
>
> > >
>
> > > ..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 Tri
Re: $$Excel-Macros$$ Re: Saving an Excel sheet as a UTF8 .txt and .csv - have code, need modifications!
do you want to save your workbook in text and csv format.check links below
in text
http://www.compshack.com/visual-basic/save-file-using-visual-basic-excel-macro
http://www.vbaexpress.com/kb/getarticle.php?kb_id=805
in csv
http://blogs.technet.com/b/heyscriptingguy/archive/2005/03/22/how-can-i-save-a-single-excel-worksheet-to-a-csv-file.aspx
http://www.ozgrid.com/forum/showthread.php?t=92436&page=1
On Tue, Jun 14, 2011 at 5:19 PM, Major Bandwith 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:
>
>
>
> '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.
>
>
> On Jun 13, 11:43 am, Major Bandwith 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:
> >
> >
> >
> > '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
> >
> >
> >
> > ..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 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
>
--
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com
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
$$Excel-Macros$$ Re: Saving an Excel sheet as a UTF8 .txt and .csv - have code, need modifications!
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
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.
On Jun 13, 11:43 am, Major Bandwith 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:
>
>
>
> '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
>
>
>
> ..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 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
