Mapping an .XDS file and import XML into excel

[roblo]

I am trying to automate a process of exporting data from a database into an excel spreadsheet. Currently database application creates a .XML file and an associated .XSD mapping. I can manually do this by creating an excel workbook, apply the XML source mapping (XSD), drag the mapping to the workbook and then import the .XML data. Now I want to use VBScript.

I have been struggling to mimic these excel menu commands in VBScript. I have already succesfully imported an .XDS mapping (XML SOURCE) into excel and am able to map a single element from the map to the spreadsheet (B2). But I can't mimic the drag of the root xml source to the spreadsheet with vbs. When I do the single element as shown below, I don't get the column heading like a drag from the mapping would have done.

Also I can't get the xmlimport command right, and I get errors. "Wrong number of arguments or invalid property assignment"


set workbook = xapp.Workbooks.Add
set worksheet = workbook.Worksheets("sheet1")

workbook.XmlMaps.Add("c:\junk.xsd"),"Power"

Set xp = worksheet.Range("B2").XPath
xp.SetValue Workbook.XmlMaps(1), "/Power/TEST/Customer_number"

msgbox worksheet.Range("B2").XPath.Value

Set xmpCustomMap = workbook.XmlMaps(1)
'workbook.XmlImport ("Power").Import "C:\junk.xml", False

'workbook.SaveAs("C:\junk1.xls")

If there is another way to do this, that will work too.

 

[PHV]

Have you tried the macrorecorder in Excel to get a starting point analyzing the generated VBA code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[roblo]

I did and it didn't produce any code for mapping the elements to the workbook.

I did a drag and drop of the elements (XML source) to A1 of the workbook and no VB code was generated.