+ Reply to Thread
Results 1 to 1 of 1

Including special characters in Excel generated XML

Hybrid View

  1. #1
    Registered User
    Join Date
    04-20-2015
    Location
    Madrid
    MS-Off Ver
    2010
    Posts
    85

    Including special characters in Excel generated XML

    I have a problem with the following:

    Previously I got help from one of the Excelforum users in generating an XML based on a specific schema.
    However this solution had a fault; the XML being generated did not support special characters (such as "ä","ö","å" and even "&").

    Could somebody kindly help me with this issue?

    The Module for generating XML is below and an example excel file along with the target XML schema attached.

    Sub XMLFIle()
      Dim strXML As String
        'strXML = NavfGenerateXML(Selection, "IndividualAccounts")
        Dim HEADER As String
        Dim TAG_BEGIN  As String
        Dim TAG_END  As String
        Dim LC As Long
        Dim LR As Long
        Dim Btag As String
        Dim filenameinput As String
        Dim FPath As String, FName As String
    '========================================
    Dim Sht As Worksheet: Set Sht = ThisWorkbook.Sheets("Sheet1")
    FPath = "C:\XML Creation"
    FName = "XMLTest"
    filenameinput = FPath & "\" & FName & ".xml"
    
    
    HEADER = "<?xml version=""1.0"" encoding=""UTF-8"" ?>" & vbCrLf
    strXML = HEADER
     
        
       TAG_BEGIN = "<Products>"
       TAG_END = "</Products>"
    
        strXML = strXML & TAG_BEGIN
        
        With Sht
            'Finding Last Row
            LR = .Range("A" & .Rows.Count).End(xlUp).Row
            LC = .Cells(1, .Columns.Count).End(xlToLeft).Column
                    
            For i = 2 To LR
                strXML = strXML & vbCrLf & "<Product>"
                
                For j = 1 To LC
                    If .Cells(i, j).Value = "" Then
                        strXML = strXML & vbCrLf & "<" & .Cells(2, j).Value & "/>"
                    Else
                        strXML = strXML & vbCrLf & "<" & .Cells(1, j).Value & ">" & .Cells(i, j).Value & "</" & .Cells(1, j).Value & ">"
                    End If
                Next
                j = 1
                strXML = strXML & vbCrLf & "</Product>"
            Next
       End With
       strXML = strXML & TAG_END
    '=========================================
        sWriteFile strXML, filenameinput
        MsgBox ("Completed. XML Written to " & filenameinput)
    End Sub
    ' Function for writing plain string out a file
    Sub sWriteFile(strXML As String, strFullFileName As String)
    
        Dim intFileNum As String
        
        intFileNum = FreeFile
        Open strFullFileName For Output As #intFileNum
        Print #intFileNum, strXML
        Close #intFileNum
    End Sub
    Attached Images Attached Images
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 01-12-2014, 03:40 AM
  2. How to remove special characters in a excel
    By Latha Mani in forum Excel General
    Replies: 6
    Last Post: 01-10-2014, 11:33 AM
  3. Export multiple columns to single column text files including special characters
    By mike4uuu in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-09-2011, 04:54 AM
  4. [SOLVED] Excel 'Special' Characters in Expressions
    By DannyDont in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-31-2006, 09:10 PM
  5. [SOLVED] Putting special characters into Excel...
    By Alex in forum Excel General
    Replies: 2
    Last Post: 08-25-2005, 03:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1