How to create unicode HTM files using VBA?

mikoamberg

New Member
Joined
Apr 12, 2008
Messages
2
Hi everyone,


I am new to this forum so I would like to thank you for considering my threat.

<o:p></o:p>
I have a problem with creating a VBA code for a macro fitting my requirements. I would like the macro to create an .htm file based on cell values in an opened Excell spreadsheet. For the content of the file I would like the macro to use the data in cells J2 and J3 and I would like to name the file according to cell J1. Actually I would like to create multiple htm files, but I guess (I hope) the loop will be the minor problem. Now, I have a very nice code, which does all these things – however since my htm files will include Slavic characters they are displayed badly in the resulting file. Here the code (I am not a VBA expert, so I took this code from a nice page and transformed it, probably i left some unnecessary data):<o:p></o:p>
<o:p> </o:p>
Sub MakeHTM_Basic()<o:p></o:p>
' Defining a list of variables used in this program<o:p></o:p>
Dim PageName As String<o:p></o:p>
<o:p> </o:p>
codepart1 = Range("j2").Value<o:p></o:p>
codepart2 = Range("j3").Value<o:p></o:p>
fName = Range("j1").Value<o:p></o:p>
PageName = "C:\" & fName & ".htm" 'location and name of saved file<o:p></o:p>
MyPageTitle = Range("A1").Value<o:p></o:p>
<o:p> </o:p>
Open PageName For Output As #1<o:p></o:p>
<o:p> </o:p>
Print #1, codepart1<o:p></o:p>
Print #1, codepart2<o:p></o:p>
Close #1<o:p></o:p>
<o:p> </o:p>
End Sub


You can see the problem and check the result when you place the following text into cell J2:


ą


and the following text to cell J3:


Ś


(The value in J1 can be: test)
<o:p></o:p>
<o:p> </o:p>
So what I would like is to make the macro save the resulting htm file in unicode and not in ansii as it obviousy does. I fought fiercly with it, replaced my notpad for other text editors but nothing helped yet. I am not sure if notepad is the source of the problem or if it is the VBA editor. Has anyone an idea how to make this work? This would help me a lot.<o:p></o:p>
<o:p> </o:p>
Thank you very much and have a good day!<o:p></o:p>
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi & Welcome to the Board!

Using the Scripting Runtime library you can create a unicode text file and write to it like:

Code:
Sub test()
'set reference to Microsoft Scripting Runtime lib
Dim strFile As String, strRange1 As String, strRange2 As String
Dim fso As FileSystemObject
Dim txtStrm As TextStream
strFile = "Y:\Work\MyFile.htm"  'amend as appropriate
strRange1 = Range("A1").Value
strRange2 = Range("A2").Value
Set fso = New FileSystemObject
Set txtStrm = fso.CreateTextFile(strFile, Overwrite:=True, Unicode:=True)
With txtStrm
    .WriteLine strRange1
    .WriteLine strRange2
    .Close
End With
End Sub
You must set a reference within the VBE via Tools>References to the Microsoft Scripting Runtime lib.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,156
Messages
5,466,983
Members
406,513
Latest member
t0ny84

This Week's Hot Topics

Top