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