How to create unicode HTM files using VBA?


New Member
Apr 12, 2008
Hi everyone,

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

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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Richard Schollar

MrExcel MVP
Apr 19, 2005
Hi & Welcome to the Board!

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

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

Latest member

This Week's Hot Topics