UTF-8.0 encoding

**bob808a** · 06-13-2012, 07:42 AM

OK, this is a fun one...

When I try to export my spread sheet data to xml, windows/excel saves it as the default file format of notepad (ANSI) but I want it in UTF-8, if I open the exported xml in notepad then hit ctrl+s to save it then it saves it as UTF-8 and is fine. The issue is that I need to update this xml everyday (as do others within the business).

How do I force UTF-8 first time around?

I have already edited the registry to make new notepad files UTF-8 by default (but this only seems to happen to new files created by right-clicking and going new>text file, not by going new, from within notepad...). I have also changed the excel "web options" to be UTF-8 and still have the same issue...

Cheers

**romperstomper** · 06-13-2012, 08:30 AM

Did you uncheck the 'always save web pages in the default encoding' option and if so, did it remain turned off?

**bob808a** · 06-14-2012, 03:18 AM

Yes I have, and yes it is.

I have been experimenting and when I open it with Notepad++, I can see that the encrption is now UTF-8(without BOM) and I have the same issue (explained below). If I open the file with notepad and hit ctrl+s, then open it again with Notepad++ I see that the encryption is now UTF-8 ..(true UTF-8 with BOM) and my issue is gone, but as I said other users need to be able to export from the excel file...

What I am trying to do and why this becomes an issue: I have created a flash app using AS2.0 and it is working fine, but when I use the XML that it exported from excel (the one without BOM) then the German characters get scrambled. When I save it in notepad (adding the BOM) the characters display just fine.

At the moment I am exporting via a linked xsd schema file I wrote myself. I don't know VBA at all, but do you know if it would be possible to fix the BOM issue with VBA? If so where can I find a simplish guide?

I thought that perhaps if I create an empty template XML that is saved as UTF-8 (WITH BOM), with one feild that I can ignore in my app, then use VBA to open it add my data then save it some where else (so that my template remains). And hopefully it will keep the BOM from the template... Do you know if this could work? I don't want to waste too much time figuring out how to write one in VBA if it won't work.

Regards,

Bob

**romperstomper** · 06-14-2012, 03:29 AM

To be honest, I don't know, as this is not something I have ever had occasion to do. What you might be able to do easily would be to export as you are currently, then use VBA (probably using an ADODB.Stream) to read the exported file and save as UTF-8 with BOM. You can certainly write UTF-8 that way so I'm hoping a conversion would be as simple.

**bob808a** · 06-14-2012, 07:39 AM

I couldn't get that to work, but I have managed to build a vba macro to copy a blank template then append the data into the copied template, meaning the the orignal UTF-8 coding is kept, this is working well except for one thing... I can't get the xml to format quite right.... do you know where I can find an example of how to write a function to create an xml?

The following format if possible..??

Format:
<code>
<head>
<data>
<field1>data</field1>
<field2>data</field2>
<field3>data</field3>
</data>
<data>
<field1>data</field1>
<field2>data</field2>
<field3>data</field3>
</data>
</head>
</code>

**romperstomper** · 06-14-2012, 07:44 AM

Not really my area but this might get you started: http://www.freevbcode.com/ShowCode.asp?ID=1919

**bob808a** · 06-14-2012, 08:01 AM

Thanks you, I'm sure that will help! Cheers

UTF-8.0 encoding

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

UTF-8.0 encoding

Re: UTF-8.0 encoding

Re: UTF-8.0 encoding

Re: UTF-8.0 encoding

Re: UTF-8.0 encoding

Re: UTF-8.0 encoding

Re: UTF-8.0 encoding

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions