CODEWITHSUNDEEP
xmlvbaexcel
Month
Month

Reputation: 303

Storing xml within excel workbook

I have some data defined in .xml file. I need to create an Excel workbook that provides some functions based on information contained in that file. The XML data must be placed within that Excel file --- i.e., it cannot be put into a separate file (so afterwards you can keep .xlsx file without .xml).

So I want to create something like this:

sub Load()  'this function is called once, and after it was called the .xml file is no longer required'
  fName = "some path to data.xml"
  ActiveSheet.OLEObjects.Add(Filename:=fName) 
End Sub

'Does not open external .xml file'
Sub SomeFunction()
  Dim data As MSXML2.DOMDocument
  Set data = New MSXML2.DOMDocument
  data.Load(ActiveWorkbook.OLEObjects(1)) 'Load method can not be called like that'

  ' parse the data'

End Sub

The way I see it is that the .xml file should be embedded into Excel file as an OLE object. Yet I can't find a way to read the data (as string) form file after it was embeded.

I know that using MSXML2 it's possible to read .xml stored in an external file (MSXML2.DOMDocument.Load method) so it can be parsed. Is it possible to use MSXML2 object to open an embedded document? Or is there an alternative way to store external XML structures within the workbook?

Upvotes: 0

Views: 358

Answers (1)

Charles Williams
Charles Williams

Reputation: 23505

You can do this using the CustomXMLParts object
see https://msdn.microsoft.com/en-us/library/office/ff863162.aspx
or the Object browser

Upvotes: 2

Related Questions