XML and Excel

Comments

All comments about this page:

Comment by: Paul Roewer (10-3-2009 12:07:47) deeplink to this comment

I defined an excel workbook as you have described above. What I need is to have the xml data refresh (reloaded) whenever the excel file is opened. However, the External Table Data connection properties are all grayed out so I cannot choose the "Refresh data when opening the file" option. Any idea why this is happing?

Comment by: Jan Karel Pieterse (10-3-2009 22:23:10) deeplink to this comment

Hi Paul,

You do have a cell inside the table selected?

Comment by: Paul Roewer (11-3-2009 07:28:18) deeplink to this comment

Thank you for the quick response. Yes... I've tried selecting both the column heading cell and a cell within the column, but neither one enables the External Table Data connection properties settings. I've tried it both in the DATA tab as well as the DESIGN tab. Same results. I'm assuming this should work for both a macro-enabled workbook as well as a macro-enabled template? I've tried both, but still nothing. Clicking the REFRESH ALL button does reload the XML data, but as I stated, I want to have it reloaded when I open the excel file.

Comment by: Jan Karel Pieterse (12-3-2009 02:58:03) deeplink to this comment

Hi Paul,

Odd that it does not work, it works fine for me. How did you tie the xml to the worksheet, what option did you select on the very first step after clicking Open?

Comment by: Paul Roewer (12-3-2009 22:55:01) deeplink to this comment

- I created a new blank workbook
- I selected a cell
- From the DATA tab -> Get External Data section, I chose "From Other Sources/From XML Data Import"
- I selected an XML file I had generated from a DataTable:

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<ADDRESS>
    <Address_ID>1</Address_ID>
    <ADDRESS>240 Green Acres Drive abc123</ADDRESS>
    <City>Austin    </City>
</ADDRESS>
<ADDRESS>
    <Address_ID>2</Address_ID>
    <ADDRESS>Murphy Texas</ADDRESS>
    <City>Tyler     </City>
</ADDRESS>
<ADDRESS>
    <Address_ID>3</Address_ID>
    <ADDRESS>Homeaabb</ADDRESS>
    <City>Sachse    </City>
</ADDRESS>
</NewDataSet>

- I accepted all the default property settings.
- Select a cell inside the table in the spreadsheet.
At this point the CONNECTION PROPERTIES under both the DESIGN and DATA Refresh tabs are grayed out.
If I modify the external XML file, save, close, and reopen the excel file, it does not refresh the table.

Comment by: Jan Karel Pieterse (13-3-2009 02:14:25) deeplink to this comment

Hi Paul,

I just used your xml and did the same as you mentioned and the properties button is available.

Is this in a new workbook or in an existing one?

Comment by: John (16-3-2009 22:53:26) deeplink to this comment

Is it possible to set the cell attribute(for eg., bg color) from the XML source?

Comment by: Jan Karel Pieterse (17-3-2009 07:19:50) deeplink to this comment

H John,

No, not related to the subject of this particular article, which is about xml files in general.

You can however change any attribute of an Excel file inside the new Excel 2007 file format, which is a zipped container of xml files. See some pointers at:

https://jkp-ads.com/Articles/Excel2007FileFormat.asp

Comment by: Jack (12-6-2009 08:13:43) deeplink to this comment

Hi,

I'm trying to write some VBA code in Excel that will give a user the option to import an XML file into a spreadsheet once the file has been saved. Usually, a user would just need to click on the menu "Data" then select the option "XML" and then import to find the file themselves and import it in. I'm trying to put these steps into a macro but I do not know how to code this.

Any help much appreciated, I am using Excel 2003.

Best,
Jack

Comment by: Jan Karel Pieterse (14-6-2009 21:20:36) deeplink to this comment

Hi Jack,

The easiest way to get your first bit of code is by recording a macro when you do the import.
you would get something similar to this:

Comment by: Suresh (24-6-2009 03:26:37) deeplink to this comment

Hi,

I have created the excel template in 2003 and did the xml mapping in that file. When i opened the same template in excel 2007, the mapping wasn't grow to the bottom. Please clarify.

Comment by: Jan Karel Pieterse (24-6-2009 07:29:01) deeplink to this comment

Hi Suresh,

I am not sure what you mean by "the mapping wasn't grow to the bottom"? Could you please try to clarify?

Comment by: Sarah Beth (5-8-2009 08:20:43) deeplink to this comment

Hi,
I have created an excel template in excel 2007 using xml files. I would like to be able to click the refresh button and be done each time I want to view new data. However, when I refresh the data, the formating I have worked on is refreshed as well. For example, the column widths change. Is there a way I can save the template so that things like this won't change when I refresh the data?
Thank you for whatever help you can give me.

Comment by: Jan Karel Pieterse (7-9-2009 09:06:23) deeplink to this comment

Hi Sarah,

You can change the XML map properties and tell Excel not to change column widths.

Excel 2003: Click the XML Map properties button on the List toolbar

Excel 2007: On the table tools tab of the ribbon, in the external data group, click the properties button.

Comment by: Fred (15-12-2009 13:03:41) deeplink to this comment

i´m trying to refresh information from an FTP server were the xml file is located, can´t do it, any ideas?

tks

Comment by: Jan Karel Pieterse (16-12-2009 00:24:59) deeplink to this comment

Hi Fred,

Does the ftp server require a username and password (they usually do)?

If so, maybe you can first open the ftp folder in Windows Explorer and then try to refresh the file from Excel?

Comment by: DAvid (2-2-2010 12:51:41) deeplink to this comment

I am doing same procedure here but I'd like to go further in only selecting certain elements/nodes to import to excel. The Macro recorder doesn't record these steps.

Comment by: Jan Karel Pieterse (3-2-2010 00:09:33) deeplink to this comment

Hi David,

Looks like you cannot record code while setting up an XML definition.
According to VBA Help, you can use the XPath object to define what element needs to go where on your sheet:

Using the XPath Object
Use the SetValue method to map an XPath to a range or list column. The SetValue method is also used to change the properties of an existing XPath.

The following example creates an XML list based on the "Contacts" schema map that is attached to the workbook, then uses the SetValue method to bind each column to an XPath.

Comment by: David (3-2-2010 09:04:38) deeplink to this comment

Ok, so I have entered the following code:
Sub CreateXMLList()
'
' Macro1 Macro
'

'
    ActiveWorkbook.XmlMaps.Add( _
        "C:\Documents and Settings\nbk35j5\Desktop\010510 download.xml", "Document"). _
        Name = "Document_Map"
    ActiveWorkbook.XmlMaps("Document_Map").Name = "Test1"

    
    Dim mapContact As XmlMap
    Dim strXPath As String
    Dim lstContacts As ListObject
    Dim lcNewCol As ListColumn
    
        ' Specify the schema map to use.
    Set mapContact = ActiveWorkbook.XmlMaps("Test1")

    ' Create a new list.
    Set lstContacts = ActiveSheet.ListObjects.Add

    ' Specify the first element to map.
    strXPath = "/Root/Users/User/ID"

    
    
        ' Map the element.
    lstContacts.ListColumns(1).XPath.SetValue mapContact, strXPath

     ' Specify the element to map.
    strXPath = "/Root/Users/User/ID"
    
     ' Add a column to the list.
    Set lcNewCol = lstContacts.ListColumns.Add

    ' Map the element.
    lcNewCol.XPath.SetValue mapContact, strXPath


End Sub

But when it gets to the line:

' Map the element.
    lstContacts.ListColumns(1).XPath.SetValue mapContact, strXPath

I get an error message that reads: The XPath is not valid because either the XPath syntax is incorrect or not supported by Excel. Any ideas why/what this means?

Thank you.

Comment by: Jan Karel Pieterse (3-2-2010 22:48:50) deeplink to this comment

I suspect your root element is not called "Root". If e.g. this is your XML:
<company>
<users>
<user>
John
</user>
<user>
David
</user>
</users>
<company>
Then the xpath string should read /company/users/user (not sure if it needs a trailing slash)

Comment by: David (13-2-2010 18:35:17) deeplink to this comment

OK I got the mapping to work using the coding you provided. However my columns do not contain equal rows. In other words my first element/column may contain 300 items/rows of data with actual data in each cell row. The second element may contain only 250 cells that have data, so rather then showing by row of the original 300 which are blank, the program just excludes the blank cells and reports only the 250 items. How can I keep all rows lined to one record and show blank data if that is the case? For instance this is how it should look:
column1 column2 column3
John     Yes     Good
Henry            Good
Ralph     No     Bad

But instead I get:
column1 column2 column3
John     Yes     Good
Henry    No     Good
Ralph             Bad
Because the second row/second column had no data, it just moved the data up. I don't want it to do that, I want the blanks to stay and appear with the records they belong to.

Comment by: David (22-2-2010 11:50:11) deeplink to this comment

When the import of XML data to Excel is occurring, I receive the following message:

The data you are attempting to map contains fromatting that is incompatible with the format specified in the worksheet. (with three choices) "Use existing formatting," "Match element data type," or "Cancel."

I will always select "Match element data type." Is there anyway to program this within the VB code so the user will not have to manually select the choice?

Comment by: Jan Karel Pieterse (23-2-2010 03:16:16) deeplink to this comment

Hi David,

Does it help if you add this statement before the import:

Application.DisplayAlerts = False

Comment by: David (23-2-2010 13:10:36) deeplink to this comment

That appears to have taken care of the issue. Thank you for all your help.

Comment by: per jensen (25-2-2010 11:30:58) deeplink to this comment

hi karel, is it posible to change any attribs in the xmlmap object inside excel. know that editing the external xml file and then use update will do the thing.

Comment by: Jan Karel Pieterse (26-2-2010 07:21:29) deeplink to this comment

Hi Per,

Not through the userinterface.
In VBA, you can change the XML belonging xml maps inside an Excel file. The property you are looking for is:

Comment by: Anton Coetzee (24-3-2010 23:37:02) deeplink to this comment

Thank you for sharing your knowledge on your website.

My Question:
I have a .dotm file with a custom Ribbon. I have a function that dumps the contents of each object in the project (i.e. forms, modules, etc.) to a text file which I can then use to compare to a previous version of my .dotm file to see what's changed.
It would be useful to also include the customUI.xml (my ribbon customisation) content in this text file. How do I get access to the contents of the customUI.xml component (as text) of my .dotm file using VBA?

Comment by: Jan Karel Pieterse (25-3-2010 01:26:21) deeplink to this comment

Hi Anton,

Please have a look at this page:

https://jkp-ads.com/Articles/Excel2007FileFormat03.asp

Comment by: Jeff Weir (3-5-2010 19:34:26) deeplink to this comment

Hi Jan. I've set up a web query in Excel 2007 that pulls an XML RSS feed into the spreadsheet.

I noticed that when you set the query up, the XML Properties dialog box doesn't have a Refresh EVERY X MINUTES
option. Even if you go Data/Connections/Properties later, and under the USAGE tab set refresh to say 1 minute, it still doesn't refresh the XML web query.

Is this what you would expect?

To get around this, I used this VBA

Comment by: Jan Karel Pieterse (4-5-2010 00:35:31) deeplink to this comment

Hi Jeff,

As far as I know, indeed xml maps do not have a timed refresh option built-in. Your VBA code seems to be the best way to work around this.

Comment by: Jeff Weir (4-5-2010 00:49:45) deeplink to this comment

Thanks for that Jan...I really appreciate it when someone like yourself takes time out of your no doubt busy day to help someone else out.

Kind regards

Jeff

Comment by: gary richardson (3-11-2010 23:02:17) deeplink to this comment

I’m using code similar to Sub CreateXMLList() above to map elements to a list.
my code works fine for type="xsd:string" elements but for type="xsd:integer" I get a warning:

The data you are attempting to map contains formatting that is incompatible with the format specified in the worksheet. (with three choices) "Use existing formatting," "Match element data type," or "Cancel."

using : Application.DisplayAlerts = False will remove the warning but it formats the column to String not Integer.
can you tell me what causes the warning and how to fix it.

Comment by: Jan Karel Pieterse (3-11-2010 23:42:53) deeplink to this comment

Hi Gary,

The column in question is formatted as text. Make sure you format all relevant cells as general and you should be fine.

Comment by: gary richardson (4-11-2010 09:02:44) deeplink to this comment

hi jan, I’m starting with a clean sheet and the default formatting is set to general.
I did find that if I map the integer column first it works ok.
any suggestions?

here’s my schema:

Comment by: Jan Karel Pieterse (5-11-2010 04:02:56) deeplink to this comment

Hi Gary,

Sorry, not really. I guess you'll have to experiment.

Comment by: Robert E (5-11-2010 13:06:22) deeplink to this comment

I'm glad I found this site, and the comments all look very recent, which means maybe you can answer/solve my question/problem. :)

What I am trying to do is find a way to change the source location of a current XML connection file... for example, I have an XML file already configured as a data connection, but I want to change the file used to a different file rather than create a new connection. So, if the original file was called "october.xml" but I want to use "november.xml" instead, how do I do that via VBA? The schema for both are identical, but the data set they contain are different.

I tried to record a macro manually changing the file name, but it didn't seem to work (no code was generated showing the file name change/modification for that connection name).

Is changing the source/file name of the XML data connection even possible in VBA? Thanks in advance!

Comment by: gary richardson (8-11-2010 22:34:40) deeplink to this comment

hi jan,
I did some experimenting and found that you were right.
formatting the header row first did fix the problem.

    Range("A1").Select
    ActiveCell.EntireRow.Select
    Selection.NumberFormat = "General"
    ActiveCell.Select

thanks,
gary

Comment by: Jan Karel Pieterse (8-11-2010 22:47:42) deeplink to this comment

Hi Gary,

Great, thanks for letting us know.

Comment by: De (1-12-2010 10:31:46) deeplink to this comment

I am receiving data from an email form back in xml format which I then want to put into excel. I figured out most of how to do that but my data does not come across in the excel spreadsheet in 1 line which is what I want. It does the following:
john
     doe
         12
How do I get it to come across as:
John    doe    12
Thanks.

Comment by: Jan Karel Pieterse (2-12-2010 02:38:32) deeplink to this comment

Ho John,

Could you post a small portion of your xml here?

Comment by: Armin (21-1-2011 08:54:14) deeplink to this comment

Dear Jan,

thanks for your post and for keeping it alive for so long now. I have come around with the folowing question.

I have an Excel 2007 file and I want to import xml data from different xml files into one worksheet per file. All files use the same xml map.

Whatever I tried, I ended up that all worksheets contained the same data and I don't know how to link one data file to a certain worksheet.

Thanks a lot
Armin

Comment by: Jan Karel Pieterse (22-1-2011 08:40:25) deeplink to this comment

Hi Armin,

I would create the mappings on one file, then before importing saving that file as a template.
Then for each xml file, open the template, import the xml and save-as the template.

Comment by: Armin (24-1-2011 01:45:18) deeplink to this comment

Dear Jan,

thanks. I think I caused a misunderstanding:

I have 3 xml files and I want to import them into one excel file containing three worksheets. The xml file have all the same mapping.

Thanks again for your help.

Armin

Comment by: Jan Karel Pieterse (24-1-2011 04:16:23) deeplink to this comment

Hi Armin,

Each xml file you want to import needs its own XML Map in Excel.

1. Create the first one using the steps shown above.
2. Leave the XML task pane open
3. Go to a new worksheet.
4. In the taskpane, click the XML maps button and click "Add"
5. Select the XML file you want to use as the source for the new XML map.
6. Now you've added a new xml map and you can drag its fields to the sheet.
7. Repeat from step 3.

Comment by: Armin (24-1-2011 09:30:00) deeplink to this comment

Hello again,
thanks, Jan. I hoped that there is a solution with one map.
Nevertheless, thank you and have a good day,
Armin

Comment by: Gm (27-1-2011 00:40:02) deeplink to this comment

I have tested this and it works fine .... but ... the XML source I want to use isnt always a file, I get it given to me by a provider via a web address. e.g http://www.ichameleon.eu/xml.aspx - how can I use this as a datasource ?

Comment by: Jan Karel Pieterse (27-1-2011 02:32:48) deeplink to this comment

Hi GM,

I'm not sure. You could try just entering the url as if it were a file on your system?

Comment by: HGI (1-2-2011 13:51:41) deeplink to this comment

Is it possible to make changes or update the XML map without having to remap (drag and drop) each element to an excel cell?
For example, I imported the XML map, drag and dropped etc. Now, a new element was added to the XML map (same as before + 1 new field) so I need to import a new XML map. Do I have to remap everything again? Is there a way to refresh the map and just map the new field?
Thanks,
HGI

Comment by: Jan Karel Pieterse (1-2-2011 22:21:25) deeplink to this comment

Hi HGI,

I don't think you can. Maybe with some VBA.

Comment by: Joe (23-3-2011 06:39:29) deeplink to this comment

When I import my data from XML, data shows up on different rows. For example, I'm trying to import a projet plan. I have 3 columns, name, start and finish. When I import the task name Project Initiation shows up in row 5, the start date of 3/14 shows up in row 6 and end date of 4/15 shows up in row 7. How do I get the data to show up in the same row?

Thanks!

Comment by: Jan Karel Pieterse (23-3-2011 08:42:41) deeplink to this comment

Hi Joe,

This depends on two things:
1. The exact structure of the XML file.
2. How you mapped the xml tags to your worksheet.

If the cause is #1, there isn't much you can do except writing formulas to extracte the data from the XML import range to another area of your workbook. If # 2, then you should remap the xml file to your worksheet.
Hard to say withouth knowing the details of your xml...

Comment by: Joe (23-3-2011 08:50:01) deeplink to this comment

Here's a snipet of the XML file if that helps...

<Task><UID>1</UID><ID>1</ID><Name>Project Initiation</Name><Type>0</Type><IsNull>0</IsNull><CreateDate>2011-03-23T06:51:42</CreateDate><WBS>1</WBS><OutlineNumber>1</OutlineNumber><OutlineLevel>1</OutlineLevel><Priority>500</Priority><Start>2011-03-14T08:00:00</Start><Finish>2011-04-15T17:00:00</Finish>

There are a ton of other fields, but I used the XML Source task pane to drag and drop name, start and finish into my Excel worksheet.

Do you need any more information?

Thanks

Joe

Comment by: Jan Karel Pieterse (23-3-2011 10:20:13) deeplink to this comment

Hi Joe,

The problem is with the structure of the XML. If there is a set of repeating elements, such as:

Comment by: Betty (29-3-2011 01:24:21) deeplink to this comment

Hi,
After mapping the elements and adding some data. Every time I export or save the file as .xml I get the error “Cannot save or export XML data. The XML maps in this workbook are not exportable.”
Are you able to help please?
Many thanks,

Betty.

Comment by: Jan Karel Pieterse (29-3-2011 04:49:47) deeplink to this comment

Hi Betty,

Unfortunately, Excel is not able to export more complex XML structures. I think Excel can only go as far as 1 level down the tree of an Excel structure. As soon as there are more levels of nesting Excel is unable to export the XML "out of the box". For XML structures with hight complexity all you can do is write VBA code that builds the XML from your data.

Comment by: Betty (29-3-2011 05:57:39) deeplink to this comment

Hi Jan,
Thanks for a quick response. I have limited knowledge in VBA, do you have VBA code I can use to build the XML. I can provide the xml template if required.
Many thanks,
Betty.

Comment by: Jan Karel Pieterse (30-3-2011 00:22:32) deeplink to this comment

Hi Betty,

Here is some example VBA code. Open the VBA editor and add a reference to the "Microsoft XML, v6.0" library via Tools, References. Then insert a module and paste the code shown in my next post.

Comment by: Jan Karel Pieterse (30-3-2011 00:22:51) deeplink to this comment

Hi Betty,

And here is the code:

Comment by: Ralph (9-8-2011 06:48:28) deeplink to this comment

Hi
Is there anyway to stop an XML field being printed where the data is blank? It is an optional field for the data that I am trying to submit and if there is no data in the field then the tags cannot be printed.

Thanks

Comment by: prafull Mehta (18-8-2011 03:04:09) deeplink to this comment

how to export excel file into xml. i dnt have xml map for this file so plz tell me how can i do without xml map or tell me how to make xml map for any excel file. plz reply me on my mail id: prafullmehta85@gmail.com
I will be highly obliged if you tell me as soon as possible

Comment by: Jan Karel Pieterse (22-8-2011 02:05:22) deeplink to this comment

Hi prafull,

The easiest way is to manually create an XML file with the structure you need and import that into Excel using the examples given in this article.

Then eidt the data in Excel and export to XML.

Comment by: Keith (22-8-2011 22:03:36) deeplink to this comment

Hi,

I use a language to create XML files that load up in Excel perfectly. There are multiple sheets of different layouts in a workbook.

I would like to have a company logo inserted at the top of the 1st worksheet. Is there any XML code to insert an image, overlay/load up a template, or incorporate a macro that would do that?

Regards,
Keith
Keith

Comment by: Keith (22-8-2011 23:39:30) deeplink to this comment

Oops. I forgot to mention that I'm using Excel 2003 XML code, not 2007+.

Keith

Comment by: Jan Karel Pieterse (23-8-2011 05:52:22) deeplink to this comment

Hi Keith,

Unfortunately, that isn't possible, Excel can only import and export "normal" data, such as dates, texts and numbers from/to XML.

Comment by: Shireesh (26-8-2011 01:55:11) deeplink to this comment

Hi,

I have an XML and I'm trying to import into excel sheet. Using XMLMaps I'm able to import it. The tricky thing here is if I manually drag drop the XML from XML source task pane, it shows a smart tag and I see three options in it -
1. My Data already has a Heading (which is by default selected)
2. Place XML Heading to the left (which is always disabled)
3. Place XML Heading above.
I select the third option and that shows the headings.

If I try to record this as a Macro, the Macro fails in recognizing the drag drop and selecting the options from smart tag functionalities.

I found an alternative to drag drop by using ActiveWorkbook.XmlImport method. But, now I'm stuck with the selection of 3rd option under the smart tag.

Is there a way to default the option 'Place XML Heading above'?

Any help would be highly appreciable. I'm using Excel 2010.

Thanks in Advance,
Shireesh.

Comment by: Jan Karel Pieterse (26-8-2011 05:11:39) deeplink to this comment

Hi Shireesh,

As far as I know, you cannot do this automatically using VBA and the XMLMap object directly, so my best guess is that you have to provide the headings yourself through VBA.

You know the fieldnames anyway, so it should be easy enough to add them to the header cells directly using Range("...").Value directly.

Comment by: John (5-9-2011 07:08:29) deeplink to this comment

If you have a set of xml data with various depths of the collections - what is the best way to use excel to extract them?

Comment by: Jan Karel Pieterse (7-9-2011 07:57:45) deeplink to this comment

Hi John,

That is a bit hard to answer without knowing:
a. The structure of the data
b. What it is you need to be able to do with it!

Comment by: Brendan (28-9-2011 03:16:17) deeplink to this comment

I am trying to import data from several xml files and display the information from each file on a new row in the same worksheet in excel. We are only talking about 6 fields/cells per row. I have created the mapping successfully but I can only get the import to display one xml file's details. When I import another file it just simply overwrites the previously imported xml file details. Is there any possible way to import several xml files into one worksheet in Excel 2007?
Many Thanks
Brendan

Comment by: Jan Karel Pieterse (28-9-2011 04:01:42) deeplink to this comment

Hi Brendan,

Right-click in the table and select XML, XML Map properties. Now you should be able to selct the option that says to append new imports below the table.
However, I have seen cases where this simply does not work.

Comment by: Brendan (28-9-2011 04:14:33) deeplink to this comment

Hi Jan,
Unfortunately this option is "greyed out" for me and is defaulted to the Overwrite option.

Comment by: Jan Karel Pieterse (28-9-2011 23:25:31) deeplink to this comment

Hi Brendan,

In that case, you need a small macro that copies the content of the xml range to a different location, e.g.:

Comment by: Praveen (29-9-2011 03:43:20) deeplink to this comment

How do I display this number "0090700110343131320002" in excel as such?
I tried opening this with excel. but it turns into 9.07001E+19. Please help me out here..!

Comment by: Jan Karel Pieterse (30-9-2011 02:21:13) deeplink to this comment

Hi Praveen,

First change the number format of the cell in question to text.

Comment by: George Ford (24-10-2011 09:13:04) deeplink to this comment

Hello,

I can manually add an XML map to a simple 3 column spreadsheet, drag the elements from the XML map to the corresponding columns on the spreadsheet, and then export it to a .XML file that has teh data in it ... no problems, But when I try this in a VBA macro that I recorded doing these same steps in Excel, it tells me that my map is not exportable. Please need help on this ... been driving me crazy for a week now trying to resolve !!

Comment by: Jan Karel Pieterse (26-10-2011 03:03:59) deeplink to this comment

Hi George,

Can you post your VBA code?
and perhaps a sample of the XML you are importing?

Comment by: Rachel (4-11-2011 08:16:06) deeplink to this comment

Hi,

I am trying to get the xml data that i've imported into excel to refresh automatically when the source xml file has changed. Is it possible to do this? (instead of asking excel every x seconds to refresh?)

Thanks,
Rachel

Comment by: Jan Karel Pieterse (4-11-2011 08:19:21) deeplink to this comment

Hi Rachel,

Theoretically, this is possible by using VBA which monitors the disk drive and detects a change in file date/time.
But perhaps this is overkill?

Comment by: Rachel (4-11-2011 08:26:57) deeplink to this comment

Would that be the most efficient way to do this? The problem is that my xml source file will be changing quite often (every minute to every second... never know) and i need to update the data. Would you happen to know how to monitor the disk drive to detect changes in file date/time? Would it involve some sort of timed loop in excel to accomplish this ?

thanks,
rachel

Comment by: Jan Karel Pieterse (4-11-2011 08:44:26) deeplink to this comment

Hi Rachel,

You could do it like this:

- Add a normal module to your file and paste in this code:

Comment by: Rachel (4-11-2011 09:01:27) deeplink to this comment

Your a genius! Thanks, works great.

Just a quick question, if i set it up to check every second, will it be real taxing to excel and interfere with other processes?

Comment by: Jan Karel Pieterse (5-11-2011 11:02:03) deeplink to this comment

Hi Rachel,

It will slow down Excel a little bit, I suggest to just try a setting for a while, see if you find it obtrusive.

Comment by: neil (22-12-2011 19:40:10) deeplink to this comment

I am trying to open an xml file froma directory for one time use through a vbs script. I harvest data from the file and don't use it again.
When the file opens, excel prompts for a response to the question "Please select how you want to open this file"
I choose as an xml table and press enter.
I want to have the file open automatically without prompting me. Thank you for any assistance.

Comment by: Umesh Badgujar (28-12-2011 20:11:08) deeplink to this comment

HI,
I want to create the XML from the excel. But my XML file is having the parametrized tags & i want that parmaters to appear as it is available in xml map. while using this XML map it is failed to export

please help
Thanks
Umesh

Comment by: Jan Karel Pieterse (29-12-2011 07:08:42) deeplink to this comment

Hi Neil,

I would record a macro in Excel whilst opening the file, it should give you the right syntax to open the file directly without the prompt.

Comment by: Jan Karel Pieterse (29-12-2011 07:13:16) deeplink to this comment

Hi Umesh,

Excel's XML export capabilities are limited, you will probably have to revert to writing VBA code to get this working.

Comment by: neil (29-12-2011 13:59:39) deeplink to this comment

Thanks, Does not work. The name of the file I open is different each time. I will try another workaround instead.

Comment by: Jan Karel Pieterse (30-12-2011 12:27:17) deeplink to this comment

Hi Umesh,

It is fairly simple to write a little bit of VBA code that enables you to select a file to import and then use the resulting information in your recorded macro. For example:

Comment by: Shilpee (3-1-2012 23:13:29) deeplink to this comment

Hi

My XML file contain data for 5 tables. If I want to import only a susbset of data, how can i do that?

Comment by: J (3-1-2012 23:57:48) deeplink to this comment

Hi Shilpee,

The only way I know would be to import everything and use filtering to get the right table (assuming there is a way to discern the different tables).

Comment by: Bob Tom (2-2-2012 04:02:10) deeplink to this comment

Hi Jan,

Firstly, thanks for maintaining such an extremely useful blog on xml maps.

I'm clutching onto the ends of my sanity, working on what one would assume Excel would handle elegantly!

After googling for VBA code to parse the xml to retrieve node information, I have now started using xml maps.

The only issue I have is in dynamically setting the path for xml to be imported. If I hardcode it, it works like a charm. But I'm using formulae to concatenate the TargetPath based on the Date and Currency that user chooses. When I run the macro, Excel crashes with a System Error. [I'm using Excel 2003 SP3).

I'm adding the code as well below.

Comment by: Jan Karel Pieterse (2-2-2012 04:33:14) deeplink to this comment

Hi Bob,

You need to replace the .Select with .Value. The Select method just selects the cell and returns True if the selecting succeeded. You need the Value of that cell.

Comment by: Paul Alberts (23-2-2012 19:19:51) deeplink to this comment

Hi Jan

I am attempting to import multiple xml files from a folder. I am extracting the file names to a worksheet and then wish to import the xml's using a loop. I declared a varible for the folder location & file names. As with Bob I can't get the files to import.

Comment by: Jan Karel Pieterse (24-2-2012 08:00:13) deeplink to this comment

Hi Paul,

So what does happen? Do you get an error of some sort?

Comment by: Craig (28-2-2012 03:39:28) deeplink to this comment

Hi Jan,
Thanks for a great tutorial, very much a life saver. Everything seems to work well up to the point where I go to import a different file. The Connection Properties option in the Refresh button is greyed out. Would you happen to know what this might mean?
Regards,
Craig

Comment by: mafios (2-3-2012 00:34:01) deeplink to this comment

Hi,

I would like to create an xml file from an excel spreadsheet with 5 columns (StockCode, PriceCode, SellingPrice,PriceBase,CommissionCode) where StockCode and Price Code columns are the key.

The output xml should look like this (but in proper xml syntax)

<SellingPrice>
<Item>
        <Key>
            <StockCode>
            <PriceCode>
        <Key>
        <SellingPrice>
        <PriceBase>
        <Commission>
    <Item>
<SellingPrice>

How do i achieve this using excel 2010.

Comment by: Jan Karel Pieterse (5-3-2012 05:05:34) deeplink to this comment

Hi Mafios,

The easiest is to create an xml file that already has the structure you need (as above), open that xml file in Excel and bind the needed tags to cells. Then enter the data into the associated cells and try to export the xml.
I expect however that your xml structure is more than Excel can handle, it cannot handle multiple levels of nested tags.

Comment by: Jan Karel Pieterse (5-3-2012 05:12:57) deeplink to this comment

Hi Craig,

Do you have a cell selected which is tied to the xml file?

Comment by: Shanshr (17-5-2012 22:01:08) deeplink to this comment

Hello

I am attempting to import multiple xml files from a folder. I am extracting the file names to a worksheet and then wish to import the xml's using a loop.
Also when importing data i am looking to extract specific data for specific type of comodity.

For eg: Cell A1, i mention the ID of the commodity (say, A1234BB) and in column Cell B1, i mention the Type of commodity it is (Gold), similarly Cell A2 i mention A9876B9(as the ID) and Oil as the commodity.
I save the relevant xml's in the folder and open them (as shown above)via excel.

Problems:
1) the xml's if selected all at a time, get overwritten. There is an option in XML Maps to add another taskpane tab, however it is manual task, which i am lookin to avoid.

Hence i am looking for a loop, where i select all the xml's at once, i pre-mention/record in a seperate sheet, commodity related data that i would require.
hence once selected XML_1 opens up in the taskpane, retrieves the data as per commodity mentioned in column B of the trade ID, feeds data in relevant column, then repeat the same for the second trade.

Kindly help.
I would be indebted.
Looking forward for your response.

have a good day,
cheers!

Comment by: Deb (23-5-2012 18:14:10) deeplink to this comment

When I import a single file or open it as a table, I can't get the header rows to appear as you do in your example. I just get a cell with the data.

When I try to import multiple files, it won't do that, either. The Map properties are set to "overwrite" and the "append" option isn't available (it's greyed out, so I can't change it).

Any ideas? I tried opening the same file earlier, and it worked as you describe above, but now it doesn't. I haven't changed any options.

Comment by: Jan Karel Pieterse (23-5-2012 23:11:34) deeplink to this comment

Hi Deb,

When you drag the first element to your sheet, a small thingy (I forgot what it's called) appears next to the newly filled cell. Click it and you get a dropdown menu which allows you to select to add the tag name to the sheet.

Comment by: Deb (24-5-2012 08:14:35) deeplink to this comment

Jan-

I did figure out how to add the labels, but when I try to import multiple files, it will only import one.

I tried to replicate it today (after seeing the same result on 4 different computers yesterday), but today it works like the blog post shows.

It is very strange.

deb.

Comment by: Jan Karel Pieterse (25-5-2012 02:03:51) deeplink to this comment

Hi Deb,

There is a similar remark in one of the other comments on this page, to which I replied with:

"In that case, you need a small macro that copies the content of the xml range to a different location, e.g.:

Comment by: Brian E (23-6-2012 20:05:27) deeplink to this comment

Thanks for providing this! I'm looking for a quick and dirty way of importing a simple spreadsheet into Excel from a text file, without any knowledge of xml, and it look like this definitely does the trick. Can I get rid of the light-dark blue colors, and select (by cell) other colors for text and background?

Comment by: Jan Karel Pieterse (25-6-2012 07:07:42) deeplink to this comment

Hi Brian,

Since the result is a "Table", use the Table Tools tab of the ribbon to change the appearance of the table. See:
www.jkp-ads.com/articles/excel2007tables.asp

Comment by: Rod (7-8-2012 07:55:56) deeplink to this comment

Hi Jan--excellent blog/very informative...running into a constant problem...Here's the code: (what is missing when the data is exported is the areas in bold..(SUBSTOTAL thru TOTAL) can't figure out why they won't import..I've run it 2x..--you see, the rest of the data shows up fine...thx!

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <order>
<customer>Customer</customer>
<date>Date</date>
<order_no>Order No.</order_no>
<address>Address</address>
<city>City</city>
<state>State</state>
<zip>Zip</zip>
- <customer_order>
- <order_item>
<item_id>EX004</item_id>
<item_name>Praline Mustard Glaze (9 Oz)</item_name>
<item_price>9.25</item_price>
<qty>1</qty>
<charge>9.25</charge>
</order_item>
- <order_item>
<item_id>HVM015</item_id>
<item_name>Maple Peppercorn Mustard Gallon</item_name>
<item_price>32</item_price>
<qty>2</qty>
<charge>64</charge>
</order_item>
- <order_item>
<item_id>HVM018</item_id>
<item_name>Smokey Onion Mustard ((8.7 Oz)</item_name>
<item_price>7.5</item_price>
<qty>3</qty>
<charge>22.5</charge>
</order_item>
- <order_item>
<item_id>FM013</item_id>
<item_name>Mustard With Preserved Lemon & Garlic (9 oz)</item_name>
<item_price>12.5</item_price>
<qty>2</qty>
<charge>25</charge>
</order_item>
- <order_item>
<item_id>FM010</item_id>
<item_name>Mountain Spiced Apple Mustard (6.5 Oz)</item_name>
<item_price>6.5</item_price>
<qty>3</qty>
<charge>19.5</charge>
</order_item>
- <order_item>
<item_id>HD011</item_id>
<item_name>Polish Style Mustard (9 Oz)</item_name>
<item_price>4.75</item_price>
<qty>1</qty>
<charge>4.75</charge>
</order_item>
</customer_order>
<subtotal>Subtotal</subtotal>
<discount>Discount</discount>
<after_discount>After Discount</after_discount>
<sales_tax>5% Sales Tax</sales_tax>
<total>TOTAL</total>
</order>

Comment by: Jan Karel Pieterse (7-8-2012 10:49:50) deeplink to this comment

Hi Rod,

If I remove the ampersand from this text within the xml:

"Mustard With Preserved Lemon & Garlic"

it imports just fine in my Excel 2010.

Comment by: Scott (8-8-2012 17:09:12) deeplink to this comment

I have two related questions. The first is that when I try to open an XML file that only has one row of data I do not get the nice element row. I just get a single row of data.

To get around it, I changed the XML file to have two rows so that I could map the file. Is there a way around this?

Second, I will be importing other single row XML files into this XML table in Excel. I followed the instructions and rows are added but do not seem to be part of the XML table because they are not referenced in the filtering.

What am I doing wrong?

Comment by: Jan Karel Pieterse (9-8-2012 10:56:47) deeplink to this comment

Hi Scott,

Can you perhaps post the XML in question?
The filtering problem is probably because the rows are in fact below the table and are not really part of that table.
To filter them, copy the entire set of rows to a different sheet.

Comment by: Monica (28-9-2012 00:50:48) deeplink to this comment

I'm having the same issue. When importing an XML file with just one row, the nice blue header bar does not display. I just get the data elements listed per column with no title bar. When I add another record to the XML import, then the title bar will display.

XML file data:
<?xml version="1.0" encoding="UTF-8"?>
<CAHAN_COUNTY_FACILITY_REPORT_CHANGE_LOG
><HospitalID
>5555</HospitalID
><HospitalName
>5555</HospitalName
><Address1
>555</Address1
><Address2
>55555</Address2
><City
>5555</City
><State
>CA</State
><Zip
>95621</Zip
><County
>SACRAMENTO</County
><LCType
>General Acute Care</LCType
><Notes
/><OUListed
>Yes</OUListed
><RolesListed
>Yes</RolesListed
><orMoreRolesPopulated
>Yes</orMoreRolesPopulated
><AllRolesComplete
>Yes</AllRolesComplete
><Inactive
>No</Inactive
></CAHAN_COUNTY_FACILITY_REPORT_CHANGE_LOG
>

Comment by: Jan Karel Pieterse (28-9-2012 12:59:03) deeplink to this comment

Hi Monica,

When you drag the first element to a cell, Excel shows a little square thingy next to the cell where you dragged the item to. Click that square thingy and a dropdown menu appears. Select to include the header.

Comment by: Rich (28-9-2012 15:04:31) deeplink to this comment

I am importing an RSS feed into a single excel 2007 worksheet.
I have set this up using the following steps:
Data, From Web, RSS web address, Import.

I set the properties to append the content because I would like to keep the older info in the worksheet. The initial content fills the worksheet with no problems, but when I refresh the feed, the RSS content is repeated in the worksheet even though the RSS feed is not repeating.

Additionally, for some reason all of the refresh control is disabled and I can not find a way to enable it. So I am manually refreshing the content that is repeating as described above, which I would rather set to automatic refresh once I solved the repeating data issue.

I have no VBA or other formula's in this worksheet.

Comment by: Monica (28-9-2012 17:08:35) deeplink to this comment

OK, that worked to get the headers, but now when trying to add multiple XML files to the document, only one ports over at a time. It will import the first XML file, but I get the error, "No data was imported or only some of the data was imported" on the other ones. They all have the same file schema.

Comment by: Jan Karel Pieterse (28-9-2012 17:12:53) deeplink to this comment

Hi Monica,

Does it import anything, or nothing?

Comment by: Monica (28-9-2012 18:00:14) deeplink to this comment

If I'm importing multiple files, it will import just the first file. The other file(s) have 'Failed' under import status. It doesn't import them at all. Thanks for your help.

Comment by: Jan Karel Pieterse (1-10-2012 10:23:34) deeplink to this comment

Hi Monica,

This probably means the offending xml files have some content that either Excel does not like, or that violates its schema.

Microsoft have a free tool called XML notepad that should be able to check the files for errors.

Comment by: Alex L (11-10-2012 00:32:29) deeplink to this comment

Hello All,
I have a simple XML file which consist of the following, for example
<report toDate="03/31/2011" fromDate="04/01/2009" timeStamp="2012/08/15 11:20:00" outputFile="GG0023178.xls" outputName="ABC COMPANY, BI: New Report | Paid Period - March 2012" distributionName="9999999"/>

and this file is huge has maybe more 1000 records like this. I am interested in Filtering out “BI:xxx” value.
How can I do it? I need to export it to excel.

Thank you,
Alex

Comment by: Jan Karel Pieterse (11-10-2012 09:45:47) deeplink to this comment

Hi Alex,

I would just import the file into Excel like explained in this article and then filter the resulting table.

Comment by: Alex (11-10-2012 20:15:04) deeplink to this comment

Hi Jan,
But what about if I would like to filter out "B1: New Report" value, so any value which will follow "B1: xxx" ??
Thanks

Comment by: Ali (12-10-2012 10:07:43) deeplink to this comment

Hi,
i have created data entry form in excel and storing those captured data into another excel (database).

then i thought of using xml to store my data.

but i am new to XML, kindly guide me how to make XML as my database to store captured data from excel.
regards
Ali

Comment by: Jan Karel Pieterse (12-10-2012 12:29:23) deeplink to this comment

Hi ALi,

Why would you want to store the data in XML rather than in Excel?

Comment by: Ali (12-10-2012 14:22:42) deeplink to this comment

good question :)
i have created a tool in Excel to capture our inhouse daily activities. and storing those data into another excel(ie database.xls).

but the excel is having a limitation of 65000 records only, and this figure can be easily achiev within 4-5 months.

so without any DBMS and RDBMS i am looking for an easy solutions to store my data and retrieve it whenever required.

Comment by: Jan Karel Pieterse (12-10-2012 15:09:45) deeplink to this comment

Hi Ali,

You'd be better off using the Access mdb fileformat for this. You do not need MS Access to save data to an Access format file from Excel VBA.

Send me an email so I can send you some example code (see address below this page).

Comment by: Syps (13-10-2012 22:13:22) deeplink to this comment

I have created a XML-mapping, based on one xml-file. It is a very flat mapping, where each element gets a column header and data in the second row.

Now I want to use that map to import multiple xml-files into one sheet, where each file is it's own row.

How do get excel to add rows to the sheet for each imported file? All i've managed so far is that eacht import seems to overwrite the data in the second row.

Thanks in advance,
Syps

Comment by: Jan Karel Pieterse (15-10-2012 13:06:13) deeplink to this comment

Hi Syps,

There should be an option there somewhere that allows you to append new imports, but it does not always work.

Comment by: Syps (15-10-2012 15:19:27) deeplink to this comment

Hoi Jan Karel,

Weird thing is: i see that option only when I try to *open* and xml-file, I never see it when i'm importing xml files.

thanks for you reply.

You wouldn't happen to know a place where there is extensive documentation on this feature in the different Excel-versions? Because I also see differences in how Excel treats different xml-files when creating a mapping using XML Source task pane. And i don't understand why, and i would really like to understand why :-)

groeten,
sytske

Comment by: Jan Karel Pieterse (15-10-2012 16:24:46) deeplink to this comment

Hi Sytske,

In that case, make sure you set this page to show all comments:

https://jkp-ads.com/Articles/XMLAndExcel05.asp?AllComments=True

and look for a comment labelled like this:

"Comment by: Jan Karel Pieterse (9/28/2011 11:25:31 PM)"

It has a bit of sample code that simply copies the current xml to a different worksheet.

Comment by: Sytske (15-10-2012 17:02:50) deeplink to this comment

Thanks, found it, am gonna try that!

Comment by: Jim (9-11-2012 03:15:17) deeplink to this comment

Hoi Jan Karel,

Would love some advise on this scenario
I open a set of xml files which all have the same mapping. want to import them in the same sheet of a single workbook, one underneath another.
I open the first one normally and select the top option open as XML table. Then under developer Source I click on Map Properties to create an XML map in order that I can import the next lot underneath this one using the Source Import function. I want to automate this process using VBA
My problem is I don't know in advance what the name of the xml map (between the brackets of XmlMaps). It could be different depending on which XML file I open first. How do I overcome this issue?

Comment by: Jan Karel Pieterse (12-11-2012 11:04:34) deeplink to this comment

Hi Jim,

Well, the XMLMaps collection can be addressed by the XMLMap names: XMLMaps("TheName") but also by their index:
XMLMaps(1). If your workbook has only one XMLmap bound to it, you're safe to use the index instead of the name.

Comment by: Jim (12-11-2012 11:13:07) deeplink to this comment

Hi Jan Karel,

Many thanks for your reply! It worked marvelously, using the index!

Best regards

Jim

Comment by: Andy (12-12-2012 10:27:45) deeplink to this comment

Jim - I'm trying to do the same but I'm not too experienced with VBA, is there any chance you could share the code that did the trick for you? Many thanks.

Comment by: Tomac (16-2-2013 10:55:03) deeplink to this comment

GM,
At the office I use office 2010 and each app works fine. However when I copy the file to my home office and try to open the files, which all files display the xml icon.
I receive the following dialog box when attempting to open the .xlsx.

Problems during Load
StrictParse Error
This file cannot open because of errors. Errors are listed in: C:\users\username\appdata\local\microsoft\windows\temporary internet files\content.mso\ed7ea9fc.log

Unfortunately, couldn't find the file nor folder.
Any thoughts would be much appreciated.
Very fustrated
T

Comment by: TomacMil (16-2-2013 12:46:30) deeplink to this comment

What is happening is that a second extension is being added to many files extensions (i.e. *.pdf.XML, *.xlsx.XML, *.docx.XML "resume.docx.XML". The file cannot be opened in windows 7 with office 2010. Stripping out the .xml from the file does not help either. If I go back to an XP system, the file is fine. (The .XML extension is gone.)
Tom

Comment by: Jan Karel Pieterse (18-2-2013 08:17:36) deeplink to this comment

Hi Tomac,

This is very odd. Looks like a problem in the operating system if you ask me. Perhaps even a virus of some sort.

It is very unusual for extensions to change by themselves.

Comment by: JRisch (2-3-2013 17:31:45) deeplink to this comment

Is there a way with VBA to implement the drag-drop of the XML map for the XM Source sidebar onto a worksheet populating the top row, or is it case of iterating through map items. The macro recording facility doesn't record the drag-drop. This is on Excel 2007.

Thanks for a great resource, this is terrific stuff.

Regards, J

Comment by: Jan Karel Pieterse (3-3-2013 19:40:17) deeplink to this comment

Hi JRisch,

I'm not sure I understand your question? Since you can already drag items from the map tree onto your worksheet I guess it is something else you want?

Comment by: JRisch (4-3-2013 08:31:32) deeplink to this comment

Thanks for the swift response, JKP, What I want to do is emulate the drag and drop in VBA. But the macro recorder didn't record anything for the drag-drop.

Regards, J

Comment by: Jan Karel Pieterse (4-3-2013 10:42:55) deeplink to this comment

Hi John,

There is a comment that shows how to tie an element of the tree to a cell using VBA.

Make sure this page displays all comments by clicking this link:

https://jkp-ads.com/Articles/XMLAndExcel05.asp?AllComments=True

Then look for this text on the page:
Sub CreateXMLList()

Comment by: Jrisch (4-3-2013 12:56:35) deeplink to this comment

Thanks again, Jan. Sorry I overlooked that code. It has given me a basis to work on. Thanks again.

Regards, J

Comment by: Jan Karel Pieterse (4-3-2013 13:08:22) deeplink to this comment

Hi John,

No problem, there is a lot to read on this page, it is easy to miss a bit.

Comment by: Binu (28-3-2013 05:16:30) deeplink to this comment

how we can import xml to tally??

Comment by: Jan Karel Pieterse (28-3-2013 07:27:16) deeplink to this comment

Hi Binu,

Can you please be more specific as to what your problem is?

Comment by: kalpana (14-6-2013 17:53:49) deeplink to this comment

sir i received one of other firm data in mail attachment i want to inspect that and give report to my boss but i dont know how to open that file in excel format.
i think they ware open that file in tally9 and then open in excel. please give me sugession. and file name
RSUTallyMaster.XML

Comment by: Jan Karel Pieterse (15-6-2013 19:00:23) deeplink to this comment

Hi kalpana,

Try opening the file in Excel.

Comment by: Derrik (21-6-2013 08:45:56) deeplink to this comment

Hi Jan. Loving this blog.

I have an issue that everybody keeps skirting around that I am trying to solve, and, being new to the XML stuff, am completely stumped. I've tried various start points with different blocks of VBA code, so pasting where I'm at wouldn't help all too much but I'll include what I have below.

My issue is this:
I have a set of files that are .XML Documents that are downloaded and stored on my machine. One at a time (in separate operations), I need to open/import these into Excel as part of a greater VBA script but am having a frustrating time in the small operation of bringing them in so that their entire contents are imported and not all placed into a single cell. The desired output has their contents displayed as a CSV would load, with each attribute placed in a separate column and each additional entry placed in the next available line below the last.

As is, this keeps opening the XML file in a separate workbook and, in this case, I'd like to specify which CELL it imports to on a specific worksheet (not a new workbook). In doing this operation it needs to completely wipe out any existing XML import that was in the same cell position prior to this, and then load in its place.

Any help would be GREATLY appreciated!!!

The code:

Sub ImportXMLtoList()
Dim TargetFile As String
Dim wb As Workbook
Dim Connection As Object

For Each Connection In ActiveWorkbook.Connections
'attempting to remove all existing elements of XML before importing

Connection.Delete

Next Connection

Application.ScreenUpdating = False
Application.DisplayAlerts = False
        
TargetFile = "C:\MyFile.xml"
Set wb = Workbooks.OpenXML(Filename:=TargetFile, LoadOption:=xlXmlLoadImportToList)
Application.DisplayAlerts = True

wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("Sheet2").Range("A1")
wb.Close False
        
Application.ScreenUpdating = True

End Sub

Comment by: Jan Karel Pieterse (21-6-2013 12:00:57) deeplink to this comment

Hi Derrik,

Looking at your code I would expect it to work as is, with only one snag: if you would use paste-special value rather than a straight copy-paste it would leave the connection behind in the opened XML workbook.
Other than that I don't see what is wrong with your code?

Comment by: Derrik (24-6-2013 00:26:02) deeplink to this comment

That first attempt starting working somehow after I saw that you commented back. Odd how that works. Now, from that same point, I am trying to import only select fields from that XML source but it is resulting in an error each time now. This seemed to be working fine for a few files and then quit completely, even if I reload those previous files.

Each attempt yields:
Run-time error '-2147217376 (80041020)'
XML Parse Error

Is this something I am doing or is it bad data coming in?

Thanks again!

Below is the current code I am working with:

Dim FileName As String
Dim FilePATH As String
Dim TargetFile As String
Dim wb As Workbook
Dim Connection As Object
    
FileName = ActiveWorkbook.Worksheets("IMPORT").Range("C1").Value
FilePATH = ActiveWorkbook.Worksheets("IMPORT").Range("O1").Value

For Each Connection In ActiveWorkbook.Connections 'Removes all existing elements of XML before importing

Connection.Delete

Next Connection
    
'Clear contents
Worksheets("IMPORT").Select
ActiveSheet.Range("B4").Select
Dim myLastRow As Long
Dim myLastColumn As Long

'On Error Resume Next
myLastRow = Worksheets("IMPORT").Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
myLastColumn = Worksheets("IMPORT").Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
myLastCell = Worksheets("IMPORT").Cells(myLastRow, myLastColumn).Address
myRange = "B4:" & myLastCell
Worksheets("IMPORT").Range(myRange).Select
Selection.Clear
    
'Imports XML schema via sub
'ImportXmlFromFile
ActiveSheet.Range("B4").Select
ThisWorkbook.XmlMaps("XMLDataModel_Map").Import (FilePATH & FileName & ".xml")
    
        
Application.ScreenUpdating = True

Comment by: Jan Karel Pieterse (24-6-2013 13:36:52) deeplink to this comment

HI Derrik,

A Parse error is an indication that Excel does not like something that is in the XML. What that something is is hard to say, best to try to manually open that file and check out the details.

Comment by: Mil (24-7-2013 08:39:43) deeplink to this comment

I have multiple XML files (approx 200) which have been created using InfoPath (data entered by various users).

I want to import all the XML files into Excel with each file being in a new row. Thereby constructing a nice neat set of data I can work with.

Is this possible in Excel?

Cheers,
Mil

Comment by: Vanessa (7-8-2013 13:06:29) deeplink to this comment

Hi,

I've got several XML files that I need to open in excel as tables and I'm having great trouble doing this in vbscript. Doing this manually is easy however I need to do this for hundreds of xml files so am trying to use vbscript.

problem is by using Workbooks.OpenXML excel keeps opening the files as a read only workbook.

Would really appreciate your help on this!!

Comment by: Jan Karel Pieterse (8-8-2013 21:01:59) deeplink to this comment

Hi Vanessa,

What you can have your VBA code do after opening the XML file is copying the content of the newly opened file to a worksheet in which you gather all content of all opened files below each other.

Comment by: Jan Karel Pieterse (8-8-2013 21:09:04) deeplink to this comment

Hi Mil,

There should be some example code on this page if you set it to show all comments:

https://jkp-ads.com/articles/xmlandexcel05.asp?AllComments=True

Comment by: Saranya (4-11-2013 06:55:33) deeplink to this comment

Hi Jan,
I have two tables one below the other. I am mapping the repeating xml element to one column and static fields are available at the other columns of a TOP TABLE in Excel. When i import the xml with data, then it pushes the BOTTOM TABLE column downwards (i.e. the column beneath the repeating xml element alone is pushed down).

Comment by: Jan Karel Pieterse (4-11-2013 09:09:23) deeplink to this comment

Hi Saranya,

This is one of the reasons why it is not a good idea to have something below a table that may change in size.

Comment by: Saranya (4-11-2013 09:51:52) deeplink to this comment

Hi Jan,
     Thanks for your response. Is there any other way to implement it(In response to Jan Karel Pieterse (11/4/2013 9:09:23 AM)).

     For instance,    whenever data exist in repeating xml element is it possible to insert a new row instead of inserting a single cell?

Comment by: Jan Karel Pieterse (4-11-2013 11:14:40) deeplink to this comment

I expect if you format the bottom table as a table too you will get an error if the top table expands?

Comment by: Cari (5-1-2014 02:48:33) deeplink to this comment

Hi,

Is it possible to import multiple xml files onto a single worksheet, and append each new file into successive columns rather than successive rows?
I'm fairly new to XML-Excel mapping. Each time I append a new file, it adds rows to the XML table.
Taking this a step further, is it also possible to append only data related to one column (or one mapped xml node) from each successive xml file imported rather than all the mapped items?
I'm using Excel 2010.

Comment by: Jan Karel Pieterse (5-1-2014 16:14:35) deeplink to this comment

Hi Cari,

Yes that can be done with some VBA code. The exact code depends a bit on your setup however.
You could of course only map the element you need copied instead of mapping more?

Comment by: Cari (5-1-2014 20:30:45) deeplink to this comment

Thanks Jan. I need all the mapped elements on my excel worksheet from atleast one of the xml files. Any successive imports need data from only one of the mapped items (in a successive column). The other mapped elements can either overwrite the previous ones or be masked.

Comment by: Jan Karel Pieterse (6-1-2014 07:13:31) deeplink to this comment

Hi Cari,

Perhaps this macro helps:

Comment by: Vignesh (8-3-2014 08:59:42) deeplink to this comment

Is this XML to XL can be done via VBA Script? Selecting the option "As an XML Table" programatically..
Can you provide me the code (or) links related to this..

Comment by: glen young (2-5-2014 21:37:54) deeplink to this comment

In this little bit of code what does the word true mean?

Comment by: Jan Karel Pieterse (8-5-2014 07:25:26) deeplink to this comment

Hi,

Directly from VBA help:


Overwrite

Optional

Variant

Set to True to overwrite existing data. Set to False to append to existing data. The default value is False.

Comment by: Chip Wood (6-6-2014 17:53:56) deeplink to this comment

In our system there are tables that have layers - a Master and a detail. so there can be catagories and detailes within catagories. Is there a way to build those import files from excel? We have been sucessful in importing them xml to xlm - but I have not been able to build and EXCEL file that would convert and import in a master/detail scenario.

Any Input?

Comment by: Jan Karel Pieterse (7-6-2014 11:56:38) deeplink to this comment

Hi Chip,

Not out of the box, it would require writing VBA code to build the XML files.

Comment by: S.VADIVELU (19-7-2014 04:35:54) deeplink to this comment

kindly create the xml file

Comment by: Jan Karel Pieterse (21-7-2014 21:00:11) deeplink to this comment

Hi,

I'm not sure what you need?

Comment by: Jan (16-9-2014 18:06:56) deeplink to this comment

I am trying to Export the excel file to generate the XML file using vba. First time it happens correctly but for next attempt it throws an error as same name file is already present. How to code usig vba to Override the file available with same name ?
plz help.

Comment by: Jan Karel Pieterse (17-9-2014 09:21:56) deeplink to this comment

Hi Jan,

You could delete the file first:

Comment by: Jan (30-9-2014 09:22:53) deeplink to this comment

Thanks Jan for your comment.

I have already mapped the elements in the excel file and there are some cells where I need to use single element from XSD to map. The element will be mapped on multiple cells. But I am not able to map 1 element for multiple cells, it shows an error saying that you can not map multiple cells with 1 element.
Plz Help.

Comment by: Jan Karel Pieterse (30-9-2014 09:46:49) deeplink to this comment

Hi Jan,

An element in an XML file cannot be mapped to more than one cell (or column if it is a repeating element), there is no way around that. But you could use a formula in the other cells that point to the mapped cell perhaps?

Comment by: Jan (4-11-2014 11:30:51) deeplink to this comment

Hi Jan,

Thanks again for your reply.

I am using MsgBox in my code and I want to rename the MsgBox buttons. How can we rename the buttons ?
I am using vbYesNo option for this MsgBox.

Thanks,

Comment by: Jan Karel Pieterse (10-11-2014 11:44:16) deeplink to this comment

Hi Jan,

You cannot put other text on a messagebox than the ones VBA allows you. The way around that is to build a userform.

Comment by: Sri Teja Amam (3-12-2014 13:58:31) deeplink to this comment

Hi Jan,

I read through this long enlightening chain on XML.

What I am working on is to understand the way that I can extract the values like tags/ids from the imported XML. If this can be done in an automated fashion, could you please help me out here.

Can we do this in a non VBA method please if possible.

Thanks,
Sri

Comment by: Jan (4-12-2014 08:04:11) deeplink to this comment

Hi Jan,

Thanks. I have tried different way around.

Now, I am working on a excel report, where we have certain data validation in each cell. User is using copy-paste mode to enter the data into the Sheet, because of which my data validation gets override and validation fails.
I want to know is there any way through which we can validate the data when User uses copy-paste to enter the data ? and I want to retain data validation on that cell cells.

Comment by: Jan Karel Pieterse (4-12-2014 10:50:50) deeplink to this comment

Hi Jan,

Check out this article:
https://jkp-ads.com/Articles/CatchPaste.asp

Comment by: Jan Karel Pieterse (4-12-2014 10:52:41) deeplink to this comment

Hi Sri Teja,

Can you please try to be a little more specific? Right now I am not sure what you are trying to achieve.

Comment by: Madhu (10-12-2014 10:54:34) deeplink to this comment

Hi Jan,
I wish to extract only two tags of information from a group of xml files that located in a folder. And after extracting this informatin i wish to store it in a excel file.
Could you please help me.
Thanks
Madhu

Comment by: Jan Karel Pieterse (11-12-2014 07:06:17) deeplink to this comment

Hi Madhu,

If you expand the page to show all comments:
https://jkp-ads.com/articles/xmlandexcel05.asp?AllComments=True

you should be able to find some code that demo's refreshing the connection and copying the result to another worksheet.

Comment by: Varun Sharma (5-3-2015 05:45:44) deeplink to this comment

Thanks for the helpful article. I have a requirement to convert an office xml into a csv file in an automated fashion using a script.
Any pointers / examples on how this can be approached are much appreciated!

Comment by: Jan Karel Pieterse (5-3-2015 12:05:19) deeplink to this comment

Hi Varun,

Have you got the XML import working?

Comment by: Jan (30-3-2015 13:32:36) deeplink to this comment

Hi Jan,

I am trying to open another excel file from one using code and trying to select one the sheet from new excel but it is giving 'Subscript out f range' error.

Please suggest.

Thanks.

Comment by: Jan Karel Pieterse (30-3-2015 15:19:45) deeplink to this comment

Hi Jan,

Probably the sheetname in your code is not exactly the same as the name on the tab?

Comment by: Jan (16-4-2015 15:08:07) deeplink to this comment

Hi Jan,

I am looking for solution on below problem:

I have a huge data. I am applying filter on that data and I want to loop into the filtered data to read the one of the column and take sum of the data available in that column.

Please Help.

Thanks.

Comment by: Jan Karel Pieterse (17-4-2015 12:45:51) deeplink to this comment

Hi Jan,

Have you tried inserting a pivottable?

Comment by: Grizzle (17-4-2015 16:03:24) deeplink to this comment

Hello,

I am working with CRM 2011 and have only excel version 2013 on my system. When I try to export the data to excel it will not let me. Is there some sort of connection I need to establish or a compatibility issue?

Thank you

Comment by: Jan Karel Pieterse (21-4-2015 08:02:57) deeplink to this comment

Hi Grizle,

Does it give you an error of some sort?

Comment by: Grizzle (21-4-2015 15:25:22) deeplink to this comment

Nope, no error, it is simply grayed out.

Comment by: Jan Karel Pieterse (21-4-2015 15:27:08) deeplink to this comment

Hi Grizzle,

Is the worksheet protected?
Does the XML validate against the schema?

Comment by: When pulling xml data is not coming like a Table (22-4-2015 14:34:45) deeplink to this comment

Hello, When I am pulling fields from XML source into cells, usually it gives me a table with respected header.

But unfortunately for one of the link it is giving like a blue border boxes with hear option (like, handle).

There is nothing happening, even how many times I used.

Let me know what is exact problem and what are solutions for this.

( I tried deleting and adding. again and again - but no use. )

Help me out of this!

Comment by: Jan Karel Pieterse (22-4-2015 15:31:19) deeplink to this comment

Hi,

I'm not sure why this happens. Does the XML file contain data? Try right-clicking the connected cells and selecting Xml, Refresh.

Comment by: Roland Nagy-Varga (24-5-2016 15:36:08) deeplink to this comment

Hello,

I have tried to add the second row (<!DOCTYPE ...) of this xml "sample", used excel vba, but something went wrong. Please, help me, how to add this with DOM, not OpenTextFile!

<?xml version="1.0" encoding="ISO-8859-2"?>
<!DOCTYPE OSAP_BATCH_V16 SYSTEM "https://osap.nive.hu/osap_batch/O65v16Batch.dtd.php">
<OSAP_BATCH_V16>
    <ADATLAP>
     ...
     ...


The first row is ok. Here are the programming code:

Set objDom = New MSXML2.DOMDocument60
    
Set objPre = objDom.createProcessingInstruction("xml", "version='1.0' encoding='ISO-8859-2'")
objDom.InsertBefore objPre, objDom.DocumentElement

Comment by: Chris P (12-7-2017 00:11:46) deeplink to this comment

Hi Jan

I have searched the internet and this incredible thread of help to no avail.

I want to add an XMLMap, then automatically map it to a ListObject. I have looked at `CreateXMLList()` above, but my XMLMaps will vary, so I want to *derive* `strXPath` from the XMLMap, to pass into `lcNewCol.XPath.SetValue mapContact, strXPath` in a simple loop over all elements in the XMLMap. Is this possible? I just want the default behaviour when you right click on the root of the XMLMap, click Map element... OK.

Huge thanks
Chris

Comment by: Jan Karel Pieterse (12-7-2017 10:39:41) deeplink to this comment

Hi Chris,

I suspect this is possible, but I am too tied up to help out now, suggest to post a question at eileenslounge.com.

Comment by: Bruce Schneider (28-8-2017 04:28:18) deeplink to this comment

Think I've cracked the code on Excel not changing data into a table format automatically when drag & drop mapping from XML Source pane!

Reference

Comment by: When pulling xml data is not coming like a Table (4/22/2015 2:34:45 PM)
deeplink to this comment
Hello, When I am pulling fields from XML source into cells, usually it gives me a table with respected header.

But unfortunately for one of the link it is giving like a blue border boxes with hear option (like, handle).


Suggested solution

Excel appears to be looking for multiple <record> folders in the XML Map. By using XML Notebook (free download from Microsoft), I could open a sample XML file from TestLink (I'm trying to import to this) and duplicate one of the lowest level <testcase> folders. In the Easy-Excel demo, they call theirs <record>. As soon as I saved this XML file, opened up my Excel data file, clicked XML Maps..., opened the modified XML map I'd just saved, then dragged the top folder over the upper left data item: BINGO, Excel converted my data file to a table with 18 rows automatically (that's how many rows I had. 2.5 hours on that one, so I hope this will save someone else some time. Best Wishes, Me!

Comment by: Thomas (8-1-2018 16:41:03) deeplink to this comment

This thread is very useful for understanding better the ways to manipulate the XML maps, creat them etc using VBA. But i noticed at least one comment that didnt get and answer and is the same problem I have.

so I have an rss feed mapped from yahoo finance using a url as such

https://feeds.finance.yahoo.com/rss/2.0/headline?s=F

(This is the only element i want to update/change in an existing mapped XML map

Is this possible?

In case I am not asking correctly

what i want to know is it possible for a different url (made with a macro) to somehow import into that already mapped XML schema to simply replace the link and url elements in the already mapped XML map??

I noticed one comment had this code

Sub GetInterestRates()
Dim TargetPath As String
TargetPath = ActiveSheet.Range("S12").Select
ActiveWorkbook.XmlMaps("interestRateCurve_Map").Import URL:=TargetPath
End Sub

so specifically, if i had a cell with a new URL value and a new link value

Is there a simple code for the xml maps already created to simply replace one or two elements then run the regular refresh databinding routine?

Comment by: Jan Karel Pieterse (9-1-2018 08:26:41) deeplink to this comment

Hi Thomas,

This appears to work for me:

Comment by: Thomas (9-1-2018 15:29:45) deeplink to this comment

Outstanding!!!!    That worked for me. Thank you so much Jan

Comment by: keepITcool (29-9-2019 04:56:00) deeplink to this comment

please note that you for large tables it really pays to get the count of the records you will be importing and set the range prior to import.

Comment by: Jan Karel Pieterse (29-9-2019 20:36:00) deeplink to this comment

Hi KeepITCool,

Thanks! Nice to hear from you again.

Comment by: hnodrog (21-1-2021 15:23:00) deeplink to this comment

Is it possible to get Excel to open the XML without the prompt? I want the "Open as Data Table" to be the default behaviour when I right click on my .xml and select "Open with Excel". I thought if Excel inferred the schema, then the next time this would saved within the file and it'd know what to do with it. I have tried creating a schema, and referring to this .xsd from within the xml, so it wouldn't have to infer it next time, but still got the prompt, not sure I did this correctly. Can't find much info anywhere on this, but assume it's a bridge many have crossed.

Comment by: Jan Karel Pieterse (21-1-2021 17:56:00) deeplink to this comment

Hi hnodrog,

Once you've imported the xml into the grid you can simply tell Excel to import another xml using the same schema. This page mentions a way to do that just above the first comment.

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.