Help Creating XML File from Spreadsheet

  • Thread starter Thread starter Vmusic
  • Start date Start date
V

Vmusic

Hi,
I did some research on the net on how to create an xml file from a
spreadsheet. I think I followed the steps

1) Create an xsd (schema) file that defines or is the design for the
relationships and format of the output data

2) Import the xsd (schema) into your excel file - it should appear in
the XML source area (little window on the right)

3) 'Map' the elements in the schema to the columns in your spreadsheet

- THEN - 'in theory' you should be able to export your spreadsheet as
an xml file.....

HOWEVER .... all I get is an xml file with the column headings..... NO
DATA from the spreadsheet rows.

Any thoughts or ideas? I have 'pasted in' the output xml file below.
My spreadsheet has 7 columns as you can see from the output xml. It's
NOT some overly complicated schema.

Thanks
Alex

- - - - - - - - - - - - - - - - - - - - - - -
- - - - - - -
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<AccountEntry xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<SiteName>Site</SiteName>
<SiteDescription-Use>Site Description /Use</SiteDescription-Use>
<LoginID>Login</LoginID>
<Pwd>Pwd</Pwd>
<AccountInfo>Account</AccountInfo>
<WebsiteURL>website</WebsiteURL>
<DateLastModified>DateModified</DateLastModified>
</AccountEntry>
 
Hello,

Recently I built some code (below) to build multiple *.xml files based
on an Excel (column A)list of names. The code below MIGHT give you some
ideas?

The save part <.xml", FileFormat:=xlTextPrinter> is important because it
dosen't put quotations around text and will save the rows as setup.

I setup the file (2 sheets) as follows:-
1st Sheet named: "List"
Column A: Row 1 Heading, Rows 2,3,4... - Names of xml files I want to
create.
Column B: Row 1 Heading, Row 2 the folder to save files into (example:
C:\Test)

2nd Sheet named "Export"
Column A: Contains xml code you want to export.
Put your code here (in row not columns)

I don't like the way Excel imports XML files but that didn't matter to
me because I wasn't importing anything - Just creating/exporting to
multiple *.xml files.

I hope this helps?
Kind Regards,
Charlie.B

Sub Build_XML()
Dim rCount As Integer
Dim sFolder As String
Dim File_name As String
Dim NewWb As Workbook

Application.ScreenUpdating = False
rCount = 2

sFolder = ActiveSheet.Range("Save_Path")
'create folder to save files
On Error Resume Next
MkDir (sFolder)
On Error GoTo 0

Do While Cells(rCount, 1) <> ""
File_name = Sheets("List").Cells(rCount, 1)
Application.DisplayAlerts = False
Sheets("Export").Copy
Set NewWb = ActiveWorkbook

Range("A2").Select
ActiveCell.Formula = "<wsp_xml_root ShortDescription=" & """" &
(File_name) & """" & " LongDescription=" & """" & """" & " />"
NewWb.SaveAs Filename:=(sFolder) & "\" & (File_name) & ".xml",_
FileFormat:=xlTextPrinter
NewWb.Close
Set NewWb = Nothing

rCount = rCount + 1
ActiveWorkbook.Names.Add Name:="File_Name",
RefersTo:=Sheets("List").Cells(rCount, 1)

Loop
Application.ScreenUpdating = True
End Sub
 
Back
Top