Solved

Using XML to get data from an external workbok

Posted on 2011-09-16
7
1,032 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Stop display of alerts in Word via Excel 12 18
remove extra space at end of cell 12 31
List Box Entries Excel 2010 5 29
Filtering - Visible Rows 21 19
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

856 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