48k xml file turning into 10mb excel file? I wonder what that script is doing that takes an hour and generates an output that is more than 200x the size of the input.
Printable View
48k xml file turning into 10mb excel file? I wonder what that script is doing that takes an hour and generates an output that is more than 200x the size of the input.
The OP won't/hasn't posted the XML file and the VB script. So....
In these cases, I'd be looking to read/process the XML file separately to (outside of) Excel, to produce say a csv file that can be imported into Excel with all the data and then using VB script/macros if needed for any required formatting. Without further info, I'd be surprised if this would take more than a few minutes.
Are you processing images in the file ? This can be very deceiving. Sometimes you see a 10mm x 10mm image, but it is actually 20MB in size. That once happened to me as the customer did not understand that stretching an image down to a small size does not change the image size on disk. I had a similar experience in processing times.
I have shared the sample input XML, script, and generated excel in the following link. Please follow the steps written in ReadMe.txt attached in the same link to execute the excel writing.
I have done the Excel writing in my machine with the same data attached is taking more than 1 hr. Please suggest your input to improve the performance of excel writing.
Link,
https://drive.google.com/drive/folde...iB?usp=sharing
Thanks in the advance.
Ah. The generated spreadsheet from the xml file contains 170 sheets with many sheets containing images. All are heavily formatted as Engineering description pages. My previous experience of improving Excel load times from xml files only included dealing with one sheet and text/numbers. The used technique wouldn't be applicable here. Unfortunately in this case I can't help further. Sorry.
Perhaps some VB guru could suggest improvements to the VB code to increase performance.
If possible, the 170 sheets will process quicker if they are NOT VISIBLE during creation. Not sure if you are able to set this as an option PRIOR to generating the sheets. This has a dramatic speed increase when populating Grids with data ie, Grid.visible = false property is set to increase speed of generation.
I think regardless, 170 sheets with image is going to take a long time.
Why not schedule the generation to happen at 6am before the workers arrive. That way it really doesn't matter how long it takes.
You might get better performance creating the spreadsheet outside of Excel using a tool such as Aspose Cells. See https://products.aspose.com/cells Although this is not a free solution 30-day evaluation licences are available. They are available for C++, Java & .Net(VB/c#) and require programming knowledge to use.
That's another way to approach this issue. Create the required spreadsheet using Excel automation from C++/VB.net/c#Quote:
And I use it from C++ code via Excel automation.