R
roblo
I am really new at this vbs stuff and using XML in excel and have come
up with a partial solution looking at the net and have come this far.
But still not there.
I have been successful in creating a script to map .XSD schema to Excel
and then import the data from a file into excel through a script. But
my goal was to be able to change the data and export it back to XML.
The script I was able to make created Column Lists in my excel doc and
that can not be exported. How do I map my .XSD into excel without it
being in Column List fomat.
The below script is dynamic and being created outside of any
conventional editor and will change based on what fields I want to
bring into excel. But the goal is the same, export it from my
database to excel using xml, then change it in excel and create an xml
document using the save as option of excel and take tha final xml doc
back to my database. All this is started outside of a MS environment.
Dim xapp
Dim workbook
Dim actColumn
Dim worksheet
Dim strXpath
Dim objMap
Dim strXMLPath
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
AbsolutePath = fso.GetAbsolutePathName(path)
strXMLPath = AbsolutePath & "\BOBBY.xml"
actColumn = 0
' Create a new Excel workbook
set xapp = WScript.CreateObject("Excel.Application")
xapp.Visible = True
set workbook = xapp.Workbooks.Add
set worksheet = workbook.Worksheets("sheet1")
' Add XML Data map
set objMap = workbook.XmlMaps.Add(AbsolutePath &
"\BOBBY.xsd","PowerShift")
'Map elements into spread sheet
strXPath = "/PowerShift/C1A/Customer_number"
Set xp = worksheet.Range("A1:A2").Offset(1,actColumn).XPath
xp.SetValue workbook.XmlMaps(1), strXPath
actColumn = actColumn + 1
'Map elements into spread sheet
strXPath = "/PowerShift/C1A/Customer_type"
Set xp = worksheet.Range("A1:A2").Offset(1,actColumn).XPath
xp.SetValue workbook.XmlMaps(1), strXPath
actColumn = actColumn + 1
'Map elements into spread sheet
strXPath = "/PowerShift/C1A/Xref"
Set xp = worksheet.Range("A1:A2").Offset(1,actColumn).XPath
xp.SetValue workbook.XmlMaps(1), strXPath
actColumn = actColumn + 1
'Map elements into spread sheet
strXPath = "/PowerShift/C1A/Freight_terms"
Set xp = worksheet.Range("A1:A2").Offset(1,actColumn).XPath
xp.SetValue workbook.XmlMaps(1), strXPath
actColumn = actColumn + 1
'Import XML file into mapped elements
objMap.Import strXMLPath
'Save workbook as:'
workbook.SaveAs(AbsolutePath & "\BOBBY.xls")
up with a partial solution looking at the net and have come this far.
But still not there.
I have been successful in creating a script to map .XSD schema to Excel
and then import the data from a file into excel through a script. But
my goal was to be able to change the data and export it back to XML.
The script I was able to make created Column Lists in my excel doc and
that can not be exported. How do I map my .XSD into excel without it
being in Column List fomat.
The below script is dynamic and being created outside of any
conventional editor and will change based on what fields I want to
bring into excel. But the goal is the same, export it from my
database to excel using xml, then change it in excel and create an xml
document using the save as option of excel and take tha final xml doc
back to my database. All this is started outside of a MS environment.
Dim xapp
Dim workbook
Dim actColumn
Dim worksheet
Dim strXpath
Dim objMap
Dim strXMLPath
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
AbsolutePath = fso.GetAbsolutePathName(path)
strXMLPath = AbsolutePath & "\BOBBY.xml"
actColumn = 0
' Create a new Excel workbook
set xapp = WScript.CreateObject("Excel.Application")
xapp.Visible = True
set workbook = xapp.Workbooks.Add
set worksheet = workbook.Worksheets("sheet1")
' Add XML Data map
set objMap = workbook.XmlMaps.Add(AbsolutePath &
"\BOBBY.xsd","PowerShift")
'Map elements into spread sheet
strXPath = "/PowerShift/C1A/Customer_number"
Set xp = worksheet.Range("A1:A2").Offset(1,actColumn).XPath
xp.SetValue workbook.XmlMaps(1), strXPath
actColumn = actColumn + 1
'Map elements into spread sheet
strXPath = "/PowerShift/C1A/Customer_type"
Set xp = worksheet.Range("A1:A2").Offset(1,actColumn).XPath
xp.SetValue workbook.XmlMaps(1), strXPath
actColumn = actColumn + 1
'Map elements into spread sheet
strXPath = "/PowerShift/C1A/Xref"
Set xp = worksheet.Range("A1:A2").Offset(1,actColumn).XPath
xp.SetValue workbook.XmlMaps(1), strXPath
actColumn = actColumn + 1
'Map elements into spread sheet
strXPath = "/PowerShift/C1A/Freight_terms"
Set xp = worksheet.Range("A1:A2").Offset(1,actColumn).XPath
xp.SetValue workbook.XmlMaps(1), strXPath
actColumn = actColumn + 1
'Import XML file into mapped elements
objMap.Import strXMLPath
'Save workbook as:'
workbook.SaveAs(AbsolutePath & "\BOBBY.xls")