Solved

Using XML to get data from an external workbok

Posted on 2011-09-16
7
1,058 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 60

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
How To Reduce Deployment Times With Pre-Baked AMIs

Even if we can't include all the files in the base image, we can sometimes include some of the larger files that we would otherwise have to download, and we can also sometimes remove the most time-consuming steps. This can help a lot with reducing deployment times.

 
LVL 60

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 60

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

Stressed Out?

Watch some penguins on the livecam!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

617 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