Re: XML to Excel

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi HSalim[MVP],

Which version of Excel are we dealing with, here? this sounds more like Excel
2002 than 2003...?

I have an XML document that lists inventory items.
I will post the entire definition and sample data if needed. Briefly, the
fields are
Handle, Name, Description....,(Locations Name, Quantity, ReorderPoint,
MaxStock), (Prices Level,Price)...
fields in parenthesis represent a sub-list of repeating elements.

When I import this data into Excel, it creates a multi-line (cartesian?)
list. Assuming I have 4 locations and 5 prices for every Handle, I have 9
rows in Excel.

What I need instead is to have one row with repeating columns. For example,
Handle, Name, North_Quantity, North_ReorderPoint, North_MaxStock,
South_quantity..., Retail, Level1, Level2...
The purpose is:
a. to give the user a compact, flattened view of the data
b. allow the user to make changes rapidly, taking advantage of simple
formulas and features such as copy down and to send the data back to
the server. (Some fields, such as handle and quantity, will be read-only)

I realize that this would require a lot of coding, with hard-coding of
column layouts. A simple change such as adding a new location may break
this code and require re-coding to accomodate changes or require even more
coding up front anticipating these changes which occur occasionally.

The XML schema for sending updates back to the server is different from the
schema of this document, so breaking conformance to this schema is not a
concern.

I need suggestions on ways to accomplish this. If any one has done
something
similar and would like to share code snippets, that would be great.

I have just started working with XML in Excel and in Access and have read
more than a few articles on MSDN and sites such as XML.com, and would
welcome all suggestions.


-- Cindy

.

Relevant Pages

  • Re: xml import
    ... mir das file in mehrere Tabellen aufsplittet, ... Excel wird ein Schema erstellen, ... als XLS speichern und dieses in Access importieren oder verlinken. ...
    (microsoft.public.de.access)
  • Re: XML Mapping
    ... I just realized that "Excel" was misspelled in his link. ... "Chip Pearson" wrote: ... Download the schema and the workbook that creates the DOM and saves the XML ...
    (microsoft.public.excel.misc)
  • Re: BizTalk and Excel
    ... You can create a flat file schema that will parse this ... I would like to allow customers to be able to ... > upload data in Excel files. ... > internal XML schema for orchestration. ...
    (microsoft.public.biztalk.general)
  • Re: importing fixed length text files
    ... It's usuall simpler to use the schema information from Excel to create a ... schema.ini file with an entry for your text file. ...
    (microsoft.public.access.externaldata)
  • P: Excel 2003 mit XML Datei
    ... um mit Excel ein Schema basierend auf den XML-Quelldaten zu ... Klickt man auf Details dann kommt folgendes: ... Warum bringt Excel da einen Fehler? ...
    (microsoft.public.de.excel)