Solved Encoding error in XML file generated in acess VBA

T

Tea

Member
Local time
Today, 13:42
Joined
Oct 30, 2020
Messages
51
Hi all,

I have an issue regarding encoding in a XML file. I'm using czech letters like "Č, á, í"... inside a body tag of an XML and the error seems to occur whenever there are letters with acute or circumflex. I'm actually printing some text content into an xml file.

Snippet of the code

Code: 
Dim xmlContent As String
Dim save As String

xmlContent = "<?xml version=""1.0"" encoding=""UTF-8""?>" & vbCrLf & _
"<Main>" & vbCrLf & _
"<ID>" & Me.ID & "</ID>" & vbCrLf & _
"<Date>" & Now() & "</Date>" & vbCrLf & _
"<Name>" & "ěščřžýáíé" & "</Name>" & vbCrLf & _
"</Main>"

save = "C:...\test.xml"

Open save For Output As #1
Print #1, xmlContent
Close #1

When i open this file in some browser, it tells me there is some encoding error at line 5 and show only the ID and date, like a normal string, the name is not included. When I open the file in my notepad, everything seems fine.

Do I need to encode the whole content of the file or the file itself? What to do so it can read the 5th line also and show as a tag in browser?

Thank you
 
What happens if you change the encoding?
xmlContent = "<?xml version=""1.0"" encoding=""windows-1250""?>" & vbCrLf & _
 
  • Like
Reactions: Tea
bastanu said:
What happens if you change the encoding?
xmlContent = "<?xml version=""1.0"" encoding=""windows-1250""?>" & vbCrLf & _
Click to expand...
That is working.
But I have questions: why does UTF-8 not work?
Also the format of the generated file is in ANSI Western European 1252, is it possible to convert the file to utf-8 instead? Instead of changing the encoding in the file content? Is it possible to change the encoding in Acess settings or convert the whole file?
 
Tea said:
But I have questions: why does UTF-8 not work?
Also the format of the generated file is in ANSI Western European 1252, is it possible to convert the file to utf-8 instead? Instead of changing the encoding in the file content? Is it possible to change the encoding in Acess settings or convert the whole file?
Click to expand...
You answered the first question yourself. If you put a "This is a pear" label on an apple, it still is an apple. That's what you did by writing the string "UTF-8" into your windows-1252 encoded file.

AFAIK, you cannot use UTF-8 encoding with the Open/Print statements.
The easiest alternative is probably the ADODB.Stream object, which supports many different encodings, including UTF-8.
 
sonic8 said:
You answered the first question yourself. If you put a "This is a pear" label on an apple, it still is an apple. That's what you did by writing the string "UTF-8" into your windows-1252 encoded file.

AFAIK, you cannot use UTF-8 encoding with the Open/Print statements.
The easiest alternative is probably the ADODB.Stream object, which supports many different encodings, including UTF-8.
Click to expand...
Have never used ADODB.Stream. How would it look like?
 
Tea said:
Is the ADO library on every users computer? What if I want to share my database with someone, that can't change or add library references? Will it still work?
Click to expand...
I think, you can safely assume that the ADO library is available on every computer where there either is Access or its runtime installed.
If you use Late Binding (CreateObject()), it will work independently of the references.
 
  • Like
Reactions: Tea
This would be the early binding if I understand correctly, because I'm declaring a specific object variable.

Code: 
 Dim streamObject As ADODB.Stream

Set streamObject = New ADODB.Stream

With streamObject
    .Charset = "utf-8"
    .Mode = adModeReadWrite
    .Type = adTypeText
    .Open
    .WriteText Data:=strContent
    .Position = 3
End With

streamObject.SaveToFile save, adSaveCreateOverWrite
streamObject.Flush
streamObject.Close
Set streamObject = Nothing

Would the Late Binding look like this?

Code: 
 Dim streamObject As Object

Set streamObject = CreateObject("ADODB.Stream")

With streamObject
    .Charset = "utf-8"
    .Mode = adModeReadWrite
    .Type = adTypeText
    .Open
    .WriteText Data:=strContent
    .Position = 3
End With

streamObject.SaveToFile save, adSaveCreateOverWrite
streamObject.Flush
streamObject.Close
Set streamObject = Nothing

sonic8 said:
I think, you can safely assume that the ADO library is available on every computer where there either is Access or its runtime installed.
If you use Late Binding (CreateObject()), it will work independently of the references.
Click to expand...
I've looked and couldn't find any information regarding the availability of the ADO library reference on computers. Would you know where to look up this information for more details?
Thank you so much
 
Tea said:
This would be the early binding if I understand correctly, because I'm declaring a specific object variable.

Code: 
 Dim streamObject As ADODB.Stream

Set streamObject = New ADODB.Stream

With streamObject
    .Charset = "utf-8"
    .Mode = adModeReadWrite
    .Type = adTypeText
    .Open
    .WriteText Data:=strContent
    .Position = 3
End With

streamObject.SaveToFile save, adSaveCreateOverWrite
streamObject.Flush
streamObject.Close
Set streamObject = Nothing

Would the Late Binding look like this?

Code: 
 Dim streamObject As Object

Set streamObject = CreateObject("ADODB.Stream")

With streamObject
    .Charset = "utf-8"
    .Mode = adModeReadWrite
    .Type = adTypeText
    .Open
    .WriteText Data:=strContent
    .Position = 3
End With

streamObject.SaveToFile save, adSaveCreateOverWrite
streamObject.Flush
streamObject.Close
Set streamObject = Nothing


I've looked and couldn't find any information regarding the availability of the ADO library reference on computers. Would you know where to look up this information for more details?
Thank you so much
Click to expand...
Look in the References in the VBE:

1643840265528.png
 
Your code regarding Early/Late Binding looks fine to me.

Tea said:
I've looked and couldn't find any information regarding the availability of the ADO library reference on computers. Would you know where to look up this information for more details?
Click to expand...
I don't know any source confirming this with certainty but I believe ADO ins included with Windows since Windows Vista at the latest.
If it isn't, I think it must be installed with Office.
 
  • Like
Reactions: Tea
GPGeorge said:
Look in the References in the VBE:

View attachment 97998
Click to expand...
Does it matter which version of library am I referencing to? What's the difference?
sonic8 said:
Your code regarding Early/Late Binding looks fine to me.


I don't know any source confirming this with certainty but I believe ADO ins included with Windows since Windows Vista at the latest.
If it isn't, I think it must be installed with Office.
Click to expand...
Thank you for the infomation.
 
Tea said:
Does it matter which version of library am I referencing to? What's the difference?
Click to expand...
That's the point of using late binding - it won't matter which version is installed on the users' machine.
 
theDBguy said:
it won't matter which version is installed on the users' machine.
Click to expand...
I think that was clarified already. The current question is, how to be sure that any suitable version of the library is installed on the users computer.
 
  • Like
Reactions: Tea
sonic8 said:
I think that was clarified already. The current question is, how to be sure that any suitable version of the library is installed on the users computer.
Click to expand...
That's right. I can't seem to find any information regarding this, if it's a part of a MS package or office in that matter.
 
sonic8 said:
Thank you, George! I was looking for this info from an official Microsoft source for quite some time.
Click to expand...
Sometimes I wear my Sherlock Holmes hat to inspire me on internet searches. Occasionally it even helps. ;)
 
You must log in or register to reply here.

Similar threads

K
Solved Reading ANSI encoded files
Replies
15
Views
731
KitaYama
K
T
Can someone please assist with the VBA code Below - Error Message 3075
Replies
16
Views
378
CJ_London
C
M
VBA output report to PDF and email
Replies
18
Views
1,293
Pat Hartman
P
P
Loop gets limited
2
Replies
25
Views
1,384
Pat Hartman
P
S
The text file specification <name> does not exist. You cannot import, export, or link using the specification. (Error 3625)
Replies
12
Views
262
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom