title: excel to xml save output in UTF-8
body: <p>I trying to create a VBA macro that converts an excel sheet to xml. The problem is that the excel data contains special characters. I searched here in the forum for some help and guess the ADOB.stream-solution is the one that could solve my problem. But I'm not able to integrate this into my VBA code. So far I got the following code:</p>
  
  <pre><code>Public Sub ExcelToXML()
  
  On Error GoTo ErrorHandler
  
  
  Dim colIndex As Integer
  Dim rwIndex As Integer
  Dim asCols() As String
  Dim oWorkSheet As Worksheet
  Dim sName As String
  Dim lCols As Long, lRows As Long
  Dim iFileNum As Integer
  
  
  Set oWorkSheet = ThisWorkbook.Worksheets(1)
  sName = oWorkSheet.Name
  lCols = oWorkSheet.Columns.Count
  lRows = oWorkSheet.Rows.Count
  
  Set objstream = CreateObject("ADODB.Stream")
  
  objstream.Charset = "utf-8"
  objstream.Mode = 3
  objstream.Type = 2
  objstream.Open
  
  ReDim asCols(lCols) As String
  
  iFileNum = FreeFile
  Open "C:\temp\test5.xml" For Output As #iFileNum
  
  Worksheets("Sheet1").Range("A1:Z1").Replace _
  What:=" ", Replacement:="_", _
  SearchOrder:=xlByColumns, MatchCase:=True
  
  For i = 0 To lCols - 1
  'Assumes no blank column names
  If Trim(Cells(1, i + 1).Value) = "" Then Exit For
  asCols(i) = Cells(1, i + 1).Value
  Next i
  
  If i = 0 Then GoTo ErrorHandler
  lCols = i
  
  objstream.WriteText "&lt;?xml version=""1.0"" encoding=""UTF-8""?&gt;"
  objstream.WriteText "&lt;" &amp; "DATA" &amp; "&gt;"
  For i = 2 To lRows
  If Trim(Cells(i, 1).Value) = "" Then Exit For
  obj.stream.WriteText "&lt;" &amp; "PROC" &amp; "&gt;"
  
  For j = 1 To lCols
  
      If Trim(Cells(i, j).Value) &lt;&gt; "" Then
         objstream.WriteText "  &lt;" &amp; asCols(j - 1) &amp; "&gt;"
         objstream.WriteText Trim(Cells(i, j).Value)
         objstream.WriteText "&lt;/" &amp; asCols(j - 1) &amp; "&gt;"
         DoEvents 'OPTIONAL
      End If
  Next j
  objstream.WriteText " &lt;/" &amp; "PROC" &amp; "&gt;"
  Next i
  
  objstream.WriteText "&lt;/" &amp; "DATA" &amp; "&gt;"
  
  ErrorHandler:
  If iFileNum &gt; 0 Then Close #iFileNum
  
  objstream.SaveToFile "C:\temp\test6.xml", 2
  objstream.Close
  Set AdoS = Nothing
  
  
  Exit Sub
  
  End Sub
  </code></pre>
  
  <p>The output file is unfortunately empty. Thank you in advance for any assistance you can give me!</p>