Use VBA to read Excel table output to a formatted XML file

Recently need to do a once and for all XML document generation, to the project internal dedicated, direct VBA convenient, only for the first time. Now learn to sell .... Take the time or the systematic study of this knowledge

Output to a UTF-8 encoded XML document. And the newline character is the \ n line break for UNIX.

1 SubWritetoxml ()2   3   DimFilePath as String4   DimClientID as String5   DimName as String6   DimLastcol as Long7   DimLastRow as Long8   9   DimFso asFileSystemObjectTen   SetFSO =NewFileSystemObject One    A   DimFst as Object -   SetFST =CreateObject("ADODB. Stream") -    the    -    -    -   DimStream asTextStream +    -Lastcol =ActiveSheet.UsedRange.Columns.Count +LastRow =ActiveSheet.UsedRange.Rows.Count A      at   'Create a textstream. -    -  'Set stream = fso. OpenTextFile ("D:\ClientConfig.xml", ForWriting, True) -    -Fst. Type =2 'Specify stream type-we want to save text/string data. -Fst. Charset ="Utf-8" 'specify charset for the source text data. inFst. Open'Open The stream and write binary data to the object -   to    +   'Stream.  WriteLine "<?xml version=" & Chr & "1.0" & Chr & "encoding=" & chr & "Utf-8" & CHR & "?>" -   'Stream. WriteLine "<config>" the   'Stream. WriteLine "<clients>" *   $Fst. WriteText"<?xml version="&Chr( the) &"1.0"&Chr( the) &"encoding="&Chr( the) &"Utf-8"&Chr( the) &"?>"&Chr(Ten)Panax NotoginsengFst. WriteText"<config>"&Chr(Ten) -Fst. WriteText"<clients>"&Chr(Ten) the    +Celldata ="" A    the    forRow =1  toLastRow +        -ClientID = Cells (Row,1). Value $Name = Cells (Row,2). Value $        -      'Stream. WriteLine "<client clientid=" & chr & ClientID & chr & "Name=" & CHR & name &A mp Chr & _ -      '"ip=" & Chr & chr & "Username=" & chr & "username" & chr & "password=" & CHR & "Password" & Chr & _ the      '"upload=" & chr & "Yes" & chr & "Cachedvalidtime=" & chr & "172800" & Chr (3 4) & ">" -       Wuyi       'Stream.  WriteLine "<grid savepath=" & Chr & "/data/lwfd/client/{clientid}/{type}/{yyyymmdd}" & Chr (34) & _ the       '"Filename=" & Chr & "{type}_{ccc}_{yyyymmddhh}_{fff}_{tt}.grib2" & chr & ">" & "</ Grid> " -        Wu       'Stream. WriteLine "</client>" -        AboutFst. WriteText"<client clientid="&Chr( the) & ClientID &Chr( the) &"name="&Chr( the) & Name &Chr( the) & _ $       "ip="&Chr( the) &Chr( the) &"username="&Chr( the) &"username"&Chr( the) &"password="&Chr( the) &"Password"&Chr( the) & _ -       "upload="&Chr( the) &"Yes"&Chr( the) &"cachedvalidtime="&Chr( the) &"172800"&Chr( the) &">"&Chr(Ten) -        -Fst. WriteText"<grid savepath="&Chr( the) &"/DATA/LWFD/CLIENT/{CLIENTID}/{TYPE}/{YYYYMMDD}"&Chr( the) & _ A       "filename="&Chr( the) &"{type}_{ccc}_{yyyymmddhh}_{fff}_{tt}.grib2"&Chr( the) &">"&"</grid>"&Chr(Ten) +        theFst. WriteText"</client>"&Chr(Ten) -    $   NextRow the    the    the  'Stream. WriteLine "</clients>" the  'Stream. WriteLine "</config>" -  'Stream. Close in  theFst. WriteText"</clients>"&Chr(Ten) theFst. WriteText"</config>"&Chr(Ten) About   theFst. SaveToFile"D:\ClientConfig.xml",2 'Save binary data to disk the   MsgBox("Job Done") the End Sub

View Code

References

Http://stackoverflow.com/questions/2524703/save-text-file-utf-8-encoded-with-vba

Http://stackoverflow.com/questions/31435662/vba-save-a-file-with-utf-8-without-bom

Http://stackoverflow.com/questions/4143524/can-i-export-excel-data-with-utf-8-without-bom

Http://www.tutorialspoint.com/vba/vba_text_files.htm

Use VBA to read Excel table output to a formatted XML file