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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

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,095,183
Messages
5,442,868
Members
405,206
Latest member
warviksam

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top