How to speed up Importing of Excel XML Mapped tables

I have about 6 or 7 excel tables (list objects) mapped to xml files. These tables contain large amounts of data, some of them 4,000 rows or more. I  use VBA to export and import these xml maps as follows:

ThisWorkbook.XmlMaps("InputTable_Map").Import (currClient.Path & "\input1.xml")

 ActiveWorkbook.SaveAsXMLData currClient.Path & "\input1.xml", ActiveWorkbook.XmlMaps("InputTable_Map")

The export works very fast and there is no problem. The import though takes very long and could be up to a minute or so.  I

The use of listobjects was chosen beacuase of the ability to add and delete rows in vba with minimal effort.  Is there a faster option than what I'm currently using for saving and retrieving xml data?
AccountantsTechAsked:
Who is Participating?
 
aikimarkConnect With a Mentor Commented:
That still seems like a lot, especially when faced with importing 6 more tables.

My train of thought is to remove the data filters from the table and then import or refresh the data.  The data filters can be restored after the XML import.

How close is that imported data to what you might see in a table format?

If we can't get the data quickly, we might populate a variable with the data and then push the data into the worksheets in one step.  I wrote about this in an EE article.
http://www.experts-exchange.com/A_2253.html
0
 
aikimarkCommented:
Make sure you have screenupdating disabled and calculations set to manual before you start the import.  Restore them after your import.
0
 
AccountantsTechAuthor Commented:
I already have that all turned off.  What seems to be slowing it down is that it is importing into a table and it is importing one line at a time.  Is there a command or code that would bring the xml data into the table in bulk instead of bringing it in line by line.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
aikimarkCommented:
>>it is importing into a table ..

Please clarify.  Since you are importing XML into an Excel worksheet, where/what is this "table"?
0
 
AccountantsTechAuthor Commented:
The workbook has a sheet called "Input" with a table named "input1" there is XML map called "InputTable_Map" that maps the xml file "input1.xml to that table "input1".
I have a summary report that references the information in the table "input"

To change to a different clients info we run the import code with the new clients path.

ThisWorkbook.XmlMaps("InputTable_Map").Import (currClient.Path & "\input1.xml")

It works extrememly well however, it imports slow.   I have around 10 different tables mapped to xml that I am running the same code on and it takes over two minutes to import everything.

All I can figure out is that it runs slow because every row that is imported excel goes and updates the table range.  So if you import 14,000 rows it updates the table range in the name manager 14,000 times.

Any suggestions?

0
 
aikimarkCommented:
I have a hunch I know what's going on, but I'd like you to do a test for me.  Please do an XML import into an empty workbook (empty cells and no 'table' definitions).

Also, please import the XML in non-table format into a worksheet.

Are both of these slow?
0
 
AccountantsTechAuthor Commented:
Yeah I think you know what is going on.  I tried to run your test but as soon as you map the xml excel automatically creates a new table.  Then when you run it the first time it take REALLY long to build the table.  If you refresh the data it run QUICKLY.  However, as soon as you add a lot of rows to the table it REALLY slows down.

Bottomline is I can't import from xml into a non-table format.

0
 
aikimarkCommented:
>>I can't import from xml into a non-table format
Please explain.

* If we can't get past the performance issue, we might be able to work around the table performance by importing in non-table format and then moving the data into a table.  We would be simulating the data table import.

* What does the data look like?  

* I'm thinking that we might disable text wrapping and the data filters prior to the XML table import.
0
 
AccountantsTechAuthor Commented:
I just found a way to import it in a non table format by using the data tab from other sources and picking XML.  this runs much faster.  It takes about 7 seconds compared to about 25 seconds using the mapping xml method and then refreshing.
0
 
aikimarkCommented:
seven seconds still seems like a long time.  How many rows in your import test?
0
 
AccountantsTechAuthor Commented:
3,500 rows takes about 4 - 7 seconds
0
 
AccountantsTechAuthor Commented:
I am creating an excel application that is storing client by client data in xml format.  The idea is to use VBA code to save and retrieve the xml files.  So they are simply the same table data just that it is stored in XML format.   At first this seemed like a great way to do it but now it runs so slow when I try and retrieve a clients data that I am looking for a speedier way to save the table data in my excel file to a specified file location and then retrieve it again.  

The idea is to create an excel workbook as my application so I can keep making changes in one workbook and use it for all clients data that I save and retrieve.

Any better suggestions.............All the inforation is generated within excel and just needs to be able to be saved and retrieved outside of the workbook QUICKLY :)
0
 
aikimarkCommented:
Why not save to a database format?  Access is an acceptable choice if everything is local.  Also, why not use plain XML as the format?

Have you considered converting this Excel VBA application into a VSTO application?  That might be worth opening a new question, once you've gotten past this performance hurdle.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.