asked on

Using XML to get data from an external workbok

This is a followup EEQ https://www.experts-exchange.com/questions/27311321/Excel-2007-best-practices-when-linking-to-an-external-workbook.html?anchorAnswerId=36547019#a36547019

In[Book2.xls I have about 14,000 formulas like this spread over 7 sheets linking to Book1.xls:
=[Book1.xls]Stats!$C5 ... =[Book1.xls]Stats!$C500

I have about 1,400 formulas like this in which the two named ranges refer to the same external workbook:
=INDEX(Configs,(MATCH(C6,Tails,0)))

The ranges for the first formulas are similar but not exact. The first formula (=[Book1.xls]Stats!$C5) is in C6 of Book2.

Assuming it will make things faster and/or more stable, can you tell me what I need to do to each workbook to return the same results using XML? I'm not even sure that this is a proper use of XML.

This page on the Microsoft site purports to explain the process but it doesn't tell me how to complete the very first step: Adding an XML schema file (.xsd) to a workbook (http://office.microsoft.com/en-us/excel-help/export-xml-data-HP010206401.aspx)

Thanks,
John

John Carney

ASKER

I got this code for creating the XSD file from Microsoft, but as usual it bugs. First it tells you to create an xml file with this code and save the document as C:\BookData.xml. I don't have privileges to save diectly to the C Drive so I saved it to MyDocuments, which may or may not be a problem

<?xml version='1.0'?>
<BookInfo>
<Book>
<ISBN>989-0-487-04641-2</ISBN>
<Title>My World</Title>
<Author>Nancy Davolio</Author>
<Quantity>121</Quantity>
</Book>
<Book>
<ISBN>981-0-776-05541-0</ISBN>
<Title>Get Connected</Title>
<Author>Janet Leverling</Author>
<Quantity>435</Quantity>
</Book>
<Book>
<ISBN>999-1-543-02345-2</ISBN>
<Title>Honesty</Title>
<Author>Robert Fuller</Author>
<Quantity>315</Quantity>
</Book>
</BookInfo>

Then it tells you to create the XSD file with this code which doesn't refer to BookData.xmland seeks to open an as yet non-existent workbook.

Which is why I'm starting my XML course here on EE!

Thanks,
John

Sub Create_XSD()
   Dim StrMyXml As String, MyMap As XmlMap
   Dim StrMySchema As String
   StrMyXml = "< BookInfo >"
   StrMyXml = StrMyXml & "<Book>"
   StrMyXml = StrMyXml & "<ISBN>Text</ISBN>"
   StrMyXml = StrMyXml & "<Title>Text</Title>"
   StrMyXml = StrMyXml & "<Author>Text</Author>"
   StrMyXml = StrMyXml & "<Quantity>999</Quantity>"
   StrMyXml = StrMyXml & "</Book>"
   StrMyXml = StrMyXml & "<Book></Book>"
   StrMyXml = StrMyXml & "</ BookInfo >"

   ' Turn off async loading.
   Application.DisplayAlerts = False
   ' Add the string to the XmlMaps collection.
   Set MyMap = ThisWorkbook.XmlMaps.Add(StrMyXml)
   Application.DisplayAlerts = True

   ' Create an empty file and output the schema.
   StrMySchema = ThisWorkbook.XmlMaps(1).Schemas(1).XML
   Open "C:\MySchema.xsd" For Output As #1
   Print #1, StrMySchema
   Close #1
End Sub

Open in new window

John Carney

ASKER

I forgot to mention that I get an 'XML Parse error' on this line: Set MyMap = ThisWorkbook.XmlMaps.Add(StrMyXml)

Kevin Cross

Hi.

First, "C:\MySchema.xsd" is not a workbook. It is the file name for the XML Schema Document (XSD), i.e., your XmlMap. If you do not have permissions to C:\ to write files, then you want to change this to C:\Users\{your user name}\Documents\ as you did for the BookData.xml.

As far as the code, I will have to take a look.

Kevin

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Carney

ASKER

Hey Kevin, forgive me for being AWOL so long. I can't seem to get this at all. I can't even duplicate my feat of creating BookData.xml. If it's possible for you to post all the necessary elements and exactly where to save them in relation to each other so I can see how it all works, that would be awesome.

If not, let me know and I'll just award you the points and close the question.

Thanks,
John

Kevin Cross

I saved everything in the same directory, e.g., c:\temp\. Steps and code above is exactly what I did.

John Carney

ASKER

Thanks, Kevin. I've been on vacation since October 28, please forgive me for not getting back to this before I left.

- John