Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Export Excel Table to XML (via XSD -> XML Mapping)

Hello,

I'm very new to Excel and I need to export a user-populated Excel spreadsheet to XML. So far I've created an XSD schema and defined the XML mapping for most of the fields without many problems, but one section of the spreadsheet needs to have an expandable table (unlimited number of rows) and the generated XML needs to capture all of the dynamically-created rows of data. I'm imagining that the user can simply insert new rows as needed to the table, and Excel should be able to generate XML elements that capture the data in the fields of each table row.

Based on my Google searching so far, my understanding is that I can drag an XSD element onto a table column header (rather than the data cell like normal) to define the correct XML mapping for a table of data. But no matter what I try, I keep getting the following error when I try to save the spreadsheet as XML:

"Cannot save or export XML data. The XML maps in this workbook are not exportable."

Excel's Help documentation says this error indicates that there's something wrong with the hierarchical relationships, but I've been unable to determine what that problem is. My simple test table looks like this:

Column 1	Column 2
C1R1	C2R1
C1R2	C2R2
And my XSD definition looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="Root">
        <xs:complexType>
            <xs:sequence>

                <xs:element name="Table">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="Column1" type="xs:string"/>
                            <xs:element name="Column2" type="xs:string"/>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
                
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>
Has anyone successfully done this before, and/or can anyone see what could be wrong with my relationship definitions that Excel keeps complaining about?

Thank you!


Post your answer or comment

comments powered by Disqus
I have succesfully added a map to my excel workbook.My problem is when trying to map the fields i need.

It seems that excel tryes to find a pattern but dosent find it in my excel file.

Here is some of the xml that i want to obtain as exported from the html. The structure is repetitive. I can't be able to map my workbook (see atachement) on that. Can it be done? Have any tips ?

Thanks in advance.

- <meniu>
- <week>
- <day>
  <name>luni</name> 
  <date>13.05.2008</date> 
- <food>
- <menu>
  <title>SALATA DE CRUDITATI CU PIEPT DE PUI</title> 
  <weight>300g</weight> 
  </menu>
- <menu>
  <title>CIORBA DE PUI CA LA RADAUTI</title> 
  <Gramaj>60/340g</Gramaj> 
  </menu>
- <menu>
  <title>RULOURI DE PUI CATALANE</title> 
  <Gramaj>100/200g</Gramaj> 
  </menu>
- <menu>
  <title>FRIPTURA DE PORC CA LA PRAGA</title> 
  <weight>100/200g</weight> 
  </menu>
- <menu>
  <title>SALATA VERDE CU SOS DE LAMAIE</title> 
  <weight>200g</weight> 
  </menu>
- <menu>
  <title>CROISSANT CU CIOCOLATA</title> 
  <weight>100g</weight> 
  </menu>
  </food>
  </day>
- <day>
  <name>Marti</name> 
  <date>14.05.2008</date> 
- <food>
[...]
 </week>
- <variante>
- <offer>
  <title>Varianta 1</title> 
  <description>4 feluri 4b3b2n</description> 
  <price1>123</price1> 
  <price2>125</price2> 
  </offer>
- <offer>
  <title>Varianta 2</title> 
  <description>4 feluri 4b3b2n</description> 
  <price1>123</price1> 
  <price2>125</price2> 
  </offer>
- <offer>
  <title>Varianta 3</title> 
  <description>4 feluri 4b3b2n</description> 
  <price1>123</price1> 
  <price2>125</price2> 
  </offer>
  </variante>
  </meniu>
I have also attached the xls. file.

Hi,

I have a question and although I am afraid it is not entirely correct to post it here I will give it a try.

I am exporting some of my excel tables to word and since I have some questions on tables in Word I was wondering whether there is a similar forum like exceltip.com for microsoft Word? I ahve tried to google, but I only found some inactive useless forums.

Can anyone help me?

Thanks!

Regards

Hello,
This is my first post so if anything is wrong with it I apoligize in advance. I have looked everywhere and come up empty handed trying to make a macro that exports an excel table to SQL server through an odbc connection. As of right now, I currently am able to access the database and import a particular table to my spreadsheet. The code that makes this connection is here:

With Sheet2.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DSN=" + DSN + ";Trusted_Connection=Yes;APP=2007 Microsoft Office system;DATABASE=" +
DATABASE + ";" _
        , Destination:=Range("Sheet2!$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT * FROM " + DATABASE + ".dbo." + TABLE + " " + TABLE _
        )
Now what I want to do is set up something similar to go the other direction, using INSERT statements to populate a database table with the table that had been imported into Excel. Any help in setting up the initial connection or a reference would be great. It seems there is a lot of information out there for other connection types and scenarios, but nothing I could find on this. Thanks a lot.

I have created in Excel a number of automated tables on separate worksheets
and would like to export these tables to Word through use of a macro. The
tables all have similar formats (i.e. headings and width) but can vary in
length. So far I have succeeded in placing them in Word but cannot
automatically insert page breaks between each table, which is key for this
particular project. I am working in VBA in Excel in order to create this
Word application. I'm fairly certain that the code will not accept any
reference to "Selection" (e.g. table, paragraph, etc.). Any ideas on how to
insert these page breaks?

Hi everyone,
I''m new in here~ I'm facing a problem.... that I would like to export below table to specific format....

No Site Name SIU_Name "Sync Ref1"
1 Site_1 SIU_Example_1 10.245.132.133

format in text file.... thank thankss!!!!!!

SIU_Example_1
10.245.132.133

I'm using Excel 2010 on Win 7 64-bit and have some spreadsheets I need to export to XML via a map (which I've done in the past in Excel 2003 and didn't imagine there'd be a problem with!).

The XML mapping functionality seems buried now - but I found instructions (via help), which are also at http://office.microsoft.com/en-gb/ex...010342365.aspx - these specify to install the Excel 2003 XML Tools Add-in.

When follow the instructions, adding the XmlTools.xla add in fails with:
Compile error
The code in this project must be updated for use on 64-bit systems [...]

I then tried introducing PtrSafe and LongPtr as described at http://msdn.microsoft.com/en-us/library/ee691831.aspx (which only required one signature to change).

However, I when I re-added the add-in it showed a compilation error again - this time seemingly re. not recognising DomDocument50 - but I couldn't add a reference since the Reference option was disabled. Annoyingly, I can't recreate that compilation error any more - but the Add In is not installed.

Has anyone found a way to get XML export working on Windows 2010 64-bit - or do I need to uninstall and install 32-bit??

All the best,

Kevin

hello all,

Has anybody got any good VBA to export Excel tables as XML, and alternatively import XML into Excel with VBA.

Thanks in advance
Conor

Hi All,

I was wondering if you could help with this Excel query, converting an Excel Spreadsheet to XML.

I need the tag headings to go down the rows rather than across the columns, if that makes sense. The sheer number of tags I need means that I would need more than the maximum allocation of columns available on Excel.

Is there any way to do this? I am unsure whether Excel is flexible enough to do this, or if it is down to the XML schema to define how the data is presented in Excel? The blue map area with the star seems to only want to create new data going down.

I attach the file I have been using. Screenshots are here:

*** Warning *** Linked page has dubious adverts that may be considered inappropriate

http://www.mypicx.com/05132009/Excel_Screenshots/

The first is the actual spreadsheet and the second is the desired layout.

The XML Schema is currently as follows:

------

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"><xsd:element nillable="true" name="CLSDetails"><xsd:complexType><xsd:sequence minOccurs="0"><xsd:element minOccurs="0" maxOccurs="unbounded" nillable="true" name="CustDetails" form="unqualified"><xsd:complexType><xsd:sequence minOccurs="0">

<xsd:element minOccurs="0" nillable="true" type="xsd:integer" name="CustID" form="qualified"></xsd:element>

<xsd:element minOccurs="0" nillable="true" type="xsd:string" name="FirstName" form="unqualified"></xsd:element>

<xsd:element minOccurs="0" nillable="true" type="xsd:string" name="LastName" form="unqualified"></xsd:element>

<xsd:element minOccurs="0" nillable="true" type="xsd:string" name="***" form="unqualified"></xsd:element>

<xsd:element minOccurs="0" nillable="true" type="xsd:integer" name="Age" form="unqualified"></xsd:element>

<xsd:element minOccurs="0" nillable="true" type="xsd:string" name="Address1" form="unqualified"></xsd:element>

<xsd:element minOccurs="0" nillable="true" type="xsd:string" name="Address2" form="unqualified"></xsd:element>

<xsd:element minOccurs="0" nillable="true" type="xsd:string" name="Address3" form="unqualified"></xsd:element>

</xsd:sequence></xsd:complexType></xsd:element></xsd:sequence></xsd:complexType></xsd:element></xsd:schema>

------

Many Thanks.

Is it possible to add a button to an excel sheet that exports a table to word when its pressed?

Hi All,

I was wondering if anyone out there have any instructions to setup micro exporting excel data to Ms SQL Server 2000.
Meaning:
We have an Excel file that gets populated on the weekly bases, and I like to have a micro setup in Excel so that users could click on it and export the data to the existing table on the MS SQL server.

Thanks in advance.

Abrahim

anyone have a macro that converts a table to xml format?

Subject: Periodically exporting Excel sheet to HTML

Hi,

I am new to Excel, and have the following question:

Is there a way to have Excel export the worksheet to HTML at an interval of X minutes. I have a sheet that keeps getting modified by certain calculation and auto-data input and I need to dump it every X minutes to a Web-accesible HTML file.

Thanks,

SupportX.

Hello,

I want to export excel data to txt file in certain format. I have excel file with data in certain columns and I only want to select certain columns. Here is what data looks like in excel file. Also I am attaching excel file just to be more clear.

_A B C D E F G H I
1 b1 c1 e1 f1 h1
2 b2 c2 e2 f2 h2
3 b3 c3 e3 f3 h3
4
5 b4 c4 e4 f4 h4
6 b5 c5 e5 f5 h5

Here Columns A, D G, and I are empty. Also row 4 is empty
I want to generate a text file with columns C, E, H only ignoring any blank row or cell. So that the format of the txt file would be something like following.
data_e1(c1,e1,h1)
data_e2(c2,e2,h2)
data_e3(c3,e3,h3)
data_e4(c4,e4,h4)
data_e5(c5,e5,h5)

Note that I'm appending "data_e#" depending on the data from column E.

Any help would be greatly appreciated,

Thanks

.

hi,
I have a question , i need excel document to xml document by
programs. can you give me any information . thanks!
--
Liang Zhen
Microsoft Windows MVP
Diyinside Community
http://www.diyinside.com

Hello again.

Partially I solved my previous problem using some macros I found on the net

When the macro is run it will crate PPT file
Slide 1 - A1
Slide 2 - B1
Slide 3 - A2
Slide 4 - B2
Slide 5 - A3
Slide 6- B3

what I want is
Slide 1 - A1 and B1 (different position)
Slide 2 - A2 and B2 (different position)
Slide 3 - A3 and B3 (different position)

additional: how to set font property.thanks

thanks in advance

----------------------------------------------------------
Hello

Is there a way to copy excel data to powerpoint via macro?

I am trying to make a presentation for new vocabulary words that are in the Excel list
Please see attached files

Data_Source.xls --> source of data
Result.zip --> a powerpoint file (destination of data)

Thanks in Advance

Hey all!
First of all i'm not even sure if i should be asking this here or somewhere else so apologies if it doesnt belong here.

I have a a matrix table with Rows (A1:A65) and column (B1:F1) as headers containing events and the dates filling up the matrix.
So suppose A10 has "#1015"
and D1 has "Tax"
then D10 has the due date of Tax payment of #1015

How can i export this table to google calenders to let it remind me of all the coming due dates?

All of these events/dates need to be recurring every year.

Want it to be specifically for google calender because i need to be able to forward those reminders to concerned people to take care of it.
I only know google lets us import csv files of icalender but i dont have the format or the structure of the csv file...

Can i be helped or do i need to get some more info?

Thanks And Regards
Mohit

Excel Tables to Pivot Lists

Hello,

I'm trying to convert excel tables into pivot table lists and I am
looking for a method to do this besides cutting and pasting. The table
has 6 columns (see below) with count of product for each year e.g. xxx1
prod1 100 in Yr1, 200 in Yr2, 300 in Yr3 etc. I want to end up with a
4 column list like, (see "Get into pivot table list in this form)

Thanks

Home....Prod....Yr1....Yr2....Yr3....Yr4
xxx1.....prod1....100....200....300....400
xxx2.....prod2....110....210....310....410
xxx1.....prod3....120....220....320....420
xxx2.....prod4....130....230....330....430

Get into pivot table list in this form:

Home....Prod....Yr.......Cnt
xxx1.....prod1...Yr1 100
xxx2.....prod2...Yr1 110
xxx1.....prod1...Yr2 200
xxx2.....prod1...Yr2 210
xxx1.....prod3...Yr2 220
xxx2.....prod4...Yr2 230

Thanks
JB

how can I save a excel file to xml?

Thanks,

qq

I need some script to export excel data to a text file. I have found a lot of these online, but my situation is a little tricky. I have 100s of excel sheets with a variable number of groups. These goups consist of 5 columns full of data and between each group there is a blank column. For example, I could have 2 groups as shown below (letters are to show columns).

A B C D E F G H I J K
1 4 7 1 4 1 2 3 4 5
2 5 8 2 5 6 7 8 9 1
3 6 9 3 6 2 3 4 5 6

Here column F is blank and I want the data exported to a text file so each group goes vertically under the previous group. So the above excel data would be exported to a text file as follows:
1 4 7 1 4
2 5 8 2 5
3 6 9 3 6
1 2 3 4 5
6 7 8 9 1
2 3 4 5 6

Each of my excel worksheets has a variable number of groups and each group does contain a blank column between it. I need a script that would take an excel file with a variable number of these groups and export the data to a text file like I described above. I am new to this and not very farmiliar with it and therefore do not really know how to go about doing this. I am trying to save a lot of time rather than manually going into each worksheet and copying the data to a text file following the above guidelines.

Any help is appreciated. thanks.

I trying to export an excell datasheet as xml using the sava as "xml data"
function
I got an error that my schema is not goot for saving the data
can some one give me a working scema example.

this is what I tried:

- "G:AccesWorksheet.mdb" is my access database, and the table I want to export is named "table1".
- "C:ExcelEoL.xls" is the worksheet where the code has to be written. I have a button here that already can open the access database, but
it doesnt automatically export the table.
-"C:ExcelWorksheet.xls" is the new file created from exporting the "table1" to excel.

So what needs to be done when i press the button in EoL.xls:

-Open the access database;
-export table1 to C:ExcelWorksheet.xls;

If anyone could help, it would be very much appreciated!

x

As ever our MD has "changed the goalposts" somewhat with regard to what he is asking for within Excel......

The problem is now as follows:

We need to be able to export data from an excel spreadsheet (using mailmerge) but transpose the fields & records *within a single operation*. We currently receive an excel document from BT which (bizarrely) is laid out so that the "fields" are in rows and the "records" are in the columns. At the moment he is exporting data to MS Word using mailmerge in order to achieve an XML format text document which he can use to place orders with BT. (Apparently the document has to be in this format to allow the automated placement of orders into their system). Basically he is following the mailmerge procedure within Word to pull the data through, but because of the "back to front" nature of the initial spreadsheet when he selects the "field" from the drop down box in Word, it is not pulling through the correct information. He wants to be able to either:

a) Add the "transpose" operation to the mail merge to allow the fields to be converted to the correct position.

or

b) Produce the same result by just using excel - thereby eliminating Word from the equation ?

Ive advised ref transposing data and then running the mailmerge but due to the number of times he has to run this process he would like us to automate it if at all possible ?

I have a html page which contains a table that I would like to export to
excel just by clicking a link. I can do this by specifying:

contentType="application/vnd.ms-excel"

This automatically opens excel and imports the html table directly into
it. This is fine for the most part. What my problem is, one of the
columns has IDs that has zeros in front (eg. 00001). In the html table,
it is displaying fine. But in the excel table it strips the zeros so
now my id is 1 when in fact I still want to display it as 00001. Any
ideas on how to do this or turn off this auto-formatting feature in
excel? TIA.

Hello,

I have two tables in Ms Access, which I want to export using VBA to an excel
workbook.

I know that I can export a table in Ms Access to an Excel workbook using the
command:
DoCmd.OutputTo acOutputTable, "Table_Name", acFormatXLS, "C:myfile.xls"

Is there a method to export the second table to the same workbook on the
second worksheet? I have been able to do that only using some long method,
which is, opening the workbook, adding a worksheet, selecting it, looping
through the table's fields and writing them down to specific cells, and then
looping through the data in the table, all of which is manual, unlike the
code above.

Will appreciate any input,

Thanks!

Tj


No luck finding an answer? You could always try Google.