Asked by:

Excel 2007 - Cannot Open Clipboard Error

Question

I am posting this problem upon reviewing many forums with regard to this issue with no avail, hence decided to post a question with expectation to hear from an expert in this field. The issue I'm facing is listed below.

I have developed an Excel 2007 application containing VBA Macros to perform an automation. The automated workbook reads many data files in order to extract data out of them (for instance 20 files). The extracted data is run over a series of calculations
which result in populating sheets of structured data and a number of charts. Upon the end of the process the workbook prints several PDF outputs (i.e. using Excels PDF addin) and completes a single run.

The problem arises at the below code segment when I run the workbook two or more times, where at the PDF print stage it gives me a run time error saying "Run-Time Error: Document not saved. The document must be open, or an error may have been encountered
when saving". However none of the PDFs generated at the first run are never opened.

Upon stopping the debugger and trying to manually copy any text in the VB Editor window throws an "Out Of Memory" warning message and when I try to copy any data from any of the worksheets in the workbook it throws a "Cannot Open Clipboard" warning
message. It is to note that the workbook completes fully at its first run
and subsequent runs causing the above mentioned issues.

I assume this is a matter with the clipboard however I am unable to resolve the issue. I have used the following code at each point of the code to ensure clipboard cleanup but even this does not seem to address the problem

All replies

Also keep in mind if this solution is running through RDP or Citrix that clipboard sharring can affect how the clipboard behaves eventwise in excel.

I've had numerous challenges with this in the past and often disable the clipboard sharring when doing automation which includes the clipboard. It is also best to avoid clipboard usage all togeather when doing automation, though this is not convienint at
all.

Thank you for your prompt response. I believe I tried this, however I shall try this again and provide feedback sometime upon running. Also the workbook was not run over RDP or Citrix but directly on my desktop where the data files were
too located on my desktop. Based on your experience did you experience any issues when using the
CutCopyMode = False code-block in your application. Could you please elaborate more on that in the meantime.

I haven't come across this problem directly but I recall problems from way back to clear the clipboard, either with simply app.CutCopyMode =false (as already suggested) or the same Clipboard API set as you, but to no avail. FWIW this was after saving formats
from the active sheet to a hidden sheet to be able to restore same (ie an undo). Memory problems occurred after repeatedly copying very large UsedRange's

So just a couple of thoughts as it seems to be some sort of memory issue

Is it viable to do your "run the workbook two or more times" each time in a new automated Excel instance, having closed the old one. Not sure if that would help release the memory but might be worth trying.

Is there any possibility your sheet size, and hence PDF print size, might be growing between sessions. Instead of printing the entire sheet can you print just the range you know needs printing (would need to locate any objects such as embedded charts).

I did not have any issues using CutCopyMode = False however if I recall I threw a DoEvents after it due to other automation in the VBA routine. This is often my first debugging step when I run into errors being thrown durring automation.

There are limitations on the amount of data which you can cut and paste at a time as well (due to allocatable memory) 32 bit Excel has many different allocation limits which you can easily find when automating groups of tasks. Peter's suggestion of breaking
the task up and using seperate excel instanciations may elliviate your problem. Also once that is done multithreading can actually be used (if your calling application is capable of it (.net application for example) to improve your overall processing time.

Thank you very much for your suggestions. With regard to Peter's suggestion it would be viable for me, however it is not possible for me to request the client i work for to do the same since it is possible that they experience this issue on the first run
where for me it is somewhat between the second or third run. Secondly the PDF reports being printed is a sheet within the workbook with few rows beginning and exactly four charts where i have clearly defined the page breaks of the print area.

With regard to Scott's suggestion i am yet about to give it another try.

I will update you guys upon my tests. However i would really appreciate if you could provide me any further ideas based on your prior experience.

I guess I am out of luck at this moment. It seems what ever I try there is no way to get around this issue. I wonder if Microsoft themselves have addressed this issue before by any chance. Anyone your suggestions/solutions are most welcome.

Secondly the PDF reports being printed is a sheet within the workbook with few rows beginning and exactly four charts where i have clearly defined the page breaks of the print area.

So just that a few times is causing all those clipboard / memory issues. Would you be able to make a workbook with non-sensitive data and code to reproduce the problem and upload to a file sharing site.

I could provide you a workbook with the macro stripping off the sensitive data, however since the data-files are fairly large and unstructured I am unable to reproduce them in order for you to perform a trial run.

However with regard to one of your solutions "Is it viable to do your "run the workbook two or more times" each time in a new automated Excel instance, having closed the old one. Not sure if that would help release the memory but might be worth
trying.", I actually recoded the sections where all opening and extracting the data from the data-files are performed in a new instance of Excel and upon performing the data manipulations and calculations copy the data back to the calling workbook
for representation. Going about this change seems to have solved the problem, allowing me to run the workbook many times. However I am yet to test it on different machines of my colleagues to make sure it functions as expected.

I shall provide my feedback upon these series of tests. If all goes well I will post my code so others who are currently facing or may face the same situation can benifit from it.

Thanks Praneeth for the feedback. It sounds like there is a memory leak going on somewhere which is resolved by closing the Excel instance. I have no idea why, whether due to some Excel bug or something in the way you are implementing things that a simple
change might clear up.