none
Excel 2007 - Cannot Open Clipboard Error

    Question

  • Hi All,

    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.

    RMV_ReportsWS.ExportAsFixedFormat Type:=xlTypePDF, fileName:=filePathAndName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    

    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

    Option Explicit
    Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
    Public Declare Function EmptyClipboard Lib "user32" () As Long
    Public Declare Function CloseClipboard Lib "user32" () As Long
     
    Public Function ClearClipboard()
      If OpenClipboard(Application.hwnd) = 0 Then
       ClearClipboard = False
      Else
       EmptyClipboard
       CloseClipboard
       ClearClipboard = True
      End If
    End Function
    

    As for the specs I currently run the workbook on a Windows 7 32bit, with 2GB RAM in Excel 2007 SP2.

    I would really appreciate feedback from as it is a very frustrating situation.

    Thank You and Best Regards,
    Praneeth


    Praneeth Wickramasinghe
    Tuesday, July 12, 2011 12:33 PM

All replies

  • You could try setting CutCopyMode (of the excel application) = False

    This will raise other events which you may have better luck with.

    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.

     

    Hope this helps.

     

     

    Tuesday, July 12, 2011 12:38 PM
  • Hi Scott Gall,

    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.

    Thank You and Best Regards,
    Praneeth 


    Praneeth Wickramasinghe
    Tuesday, July 12, 2011 12:51 PM
  • 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).

    Peter Thornton

    Tuesday, July 12, 2011 1:50 PM
  • Good Day,

    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.

    Hope this is helpful.

    Tuesday, July 12, 2011 4:21 PM
  • Hi Peter & Scott,

    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.

    Thank You and Best Regards,
    Praneeth 


    Praneeth Wickramasinghe
    Wednesday, July 13, 2011 4:09 AM
  • Hi,

    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.

    Thanks and Best Regards,
    Praneeth


    Praneeth Wickramasinghe
    Wednesday, July 13, 2011 8:48 AM
  • Praneeth,

    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.

    Peter Thornton

    Wednesday, July 13, 2011 8:59 AM
  • Hi Peter,

    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.

    Thank You and Best Regards,
    Praneeth 


    Praneeth Wickramasinghe
    Monday, July 18, 2011 9:10 AM
  • 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.

    Peter Thornton

    Monday, July 18, 2011 12:07 PM
  • Praneeth

    I see that you are already in good hands. However can you show me the entire code where you call

    RMV_ReportsWS.ExportAsFixedFormat Type:=xlTypePDF, fileName:=filePathAndName, Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    Sid (A good exercise for the Heart is to bend down and help another up...)
    Monday, July 18, 2011 5:59 PM