Bulk Export works for 4 charts, then fails


#1

XL Toolbox 7.3.4
Excel 64-Bit
Windows 10

I’ve exported ~160 charts as 1200 dpi tiff’s successfully, but for some reason I cannot export any additional charts.

I run the bulk exporter and a window pops up momentarily, then disappears.

I run the single chart export and I get an error message that the action was completed successfully.

I’ve unintalled the add-in, shut down my computer, then re-installed after reboot, but the error still persists. May I send you my excel file to see if you can replicate the issue?

Thank you!


#2

Hi,

thanks for your post and for your e-mail that I received separately. I reply in the forum so that others can see possible solutions (or non-solutions).

Only by trial and error did I find out what’s going on. In your case, the charts have background images, which evidently does not agree well with the XL Toolbox export. The add-in requests Excel to place a copy of the chart to be exported on the clipboard, then obtains the image data from the clipboard to draw it in memory before saving it to file. The step where it requests the image data from the clipboard fails – the operating system responds with an error, but does not care to elaborate what the problem really is.

The workaround (that you probably won’t like) is to remove the background image. As you won’t want to do this for all 70 charts in your file, you can make use of the XL Toolbox’ Copy Chart Properties command (which is hidden away unter Data VisualizationMiscellaneous). However, here is the next caveat: Only when I set the chart background fill to ‘none’ would the copying work. All other settings led to weird phenomena, e.g. black chart backgrounds. This is another idiosyncracy (or bug) of Excel that I won’t really be able to do anything about.

On a side note, exporting these large charts to 1200 dpi full-color TIFF files generates gargantuan files with ~ 120 Megapixels, are you really sure you want to do that?

Not meaning to be the devil’s advocate, but I personally have steered away from Excel a bit recently, and do more and more stuff with R. It takes some time to learn, but once you get the hang of it, generating multiple similar charts is a breeze – and 100 % deterministic.

Kind regards

Daniel


#3

Hi Daniel,

Thank you very much for the thorough response! I thought it might be related to the background image.

I appreciate the recommendation of R. I recently saw this article and it piqued my interest, so your recommendation is timely.

Thank you again for the fantastic tool.

Chris


#4

Thanks for the link to the article, very informative.

When starting with R, ‘R for Data Science’ by R guru Hadley Wickham was a good starting point for me.


#5

Not trying to debate, but for many things I have found I can much more quickly take some raw data in almost any form, pivot it, group it etc then chard it without much work. The data and results are in some ways more “shareable” in Excel that sharing an R script.
Don’t get me wrong, but R is a great tool but for much of the financial things I deal with Excel is often easier for the quick and dirty. That is why i like the XLtoolbox and other great add-ins.


#6

You’re totally, totally right, Alan. I’m not a friend of IT fundamentalism either. Don’t see any sense in Android vs. Apple, Linux vs. Microsoft, etc., etc.

We’re all using tools to accomplish various tasks, and one should always choose the appropriate tool for the current task. That’s my conviction. Doesn’t make sense to praise the beauty and elegance of a hammer if your current task is to put a screw into something…

In the past a struggled a lot with performing tasks repeatedly with Excel, each time with somewhat different data. That’s why I started to program the XL Toolbox in the first place – out of despair… Features like “Copy chart properties”, “Move chart range”, and, of course, “Batch export” are reminiscent of that time. Since then, I have tried to apply the same approach to other areas of my work life (I’m a physician in a large hospital): Identifiy repetitive tasks & have a computer do them so that humans can focus on the stuff that only they can do and computers can’t.

When I discovered R, I was repelled at first, despite several years, if not decades of amateur or maybe by then even semi-professional (?) programming experience. However, eventually I did find out that R can do all those repetitive tasks in a much more deterministic way, and now I am glad that I took the time to learn how to use it. But in the end R is just another tool that may or may not be right for the current task at hand.

Interestingly, the BBC data folks seem to have made similar experiences, cf. the article linked above.

Best,

Daniel