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>
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top