Solved

Using XML to get data from an external workbok

Posted on 2011-09-16
7
1,011 Views
Last Modified: 2012-05-12
This is a followup EEQ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27311321.html#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
0
Comment
Question by:gabrielPennyback
  • 4
  • 3
7 Comments
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 36551208
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

0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 36551231
I forgot to mention that I get an 'XML Parse error' on this line: Set MyMap = ThisWorkbook.XmlMaps.Add(StrMyXml)
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36919085
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
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36919320
Here are the steps I just went through:

- Created file C:\Temp\BookData.xml using data you provided above.
- Added this Macro to ThisWorkbook with corrections as shown:
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:\Temp\MySchema.xsd" For Output As #1
   Print #1, StrMySchema
   Close #1
End Sub

Open in new window


Just to stay in line with the Office online guide you pointed to, I did do:
- Downloaded Excel 2003 XML Tools Add-In (http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=3108)
- Opened Excel and added above Add-In

However, that was not the issue here as far as I can tell. What those do is provide the macros already built for you. i.e., you can create XSD from data in active cells, etc.
0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 37008465
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
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 37008525
I saved everything in the same directory, e.g., c:\temp\. Steps and code above is exactly what I did.
0
 
LVL 1

Author Closing Comment

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

- John
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now