Solved

Using XML to get data from an external workbok

Posted on 2011-09-16
7
1,001 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

772 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