Error 4605 while transferring data from Excel to Word 1

[JosjaWillems]

Hello,

I keep getting the following error while executing a macro:
"error 4605: This method or property is not available because the clipboard is empty or not valid"

Here is the code (and don't mind the Dutch words: they are irrelevant):

Sub ExporteerParticuliereOfferte2()

    Application.ScreenUpdating = False
    Dim wd As Object
    Set wdApp = CreateObject("Word.Application")
    Set wd = wdApp.Documents.Add
    wdApp.ScreenUpdating = False
    wdApp.Visible = True
    
    Sheets("ParticuliereOfferte2").Visible = True
    Sheets("ParticuliereOfferte2").Select
    
    If Cells(72, 1).Value = "" Then Cells(72, 1).EntireRow.Hidden = True
    If Cells(73, 1).Value = "" Then Cells(73, 1).EntireRow.Hidden = True
'This code is repeated several times, but in order to make this easier to read, I left out most of 'em

    Range("A39:A81").Select
    Selection.Copy
    wdApp.Selection.Paste
    wdApp.Selection.EndKey
    wdApp.Selection.TypeParagraph
    
    Range("B1:C38").Select
    Selection.Copy
    wdApp.Selection.Paste
    wdApp.Selection.EndKey
    wdApp.Selection.TypeParagraph
    
    Range("A83:A134").Select
    Selection.Copy
    wdApp.Selection.Paste
    wdApp.Selection.GoTo What:=wdGoToLine, Which:=wdGoToAbsolute, Count:=1
    
    Cells.Select
    Selection.EntireRow.Hidden = False

    Sheets("ParticuliereOfferte2").Visible = False
    Sheets("Hoofdmenu").Select
    
    Application.ScreenUpdating = True
    wdApp.ScreenUpdating = True
    wdApp.Activate
    
End Sub

The error only comes up on lines like "wdApp.Selection.Paste". However, sometimes the error doesn't come up at all, and sometimes it comes up on a different paste-command.

I really can't figure it out. Unending gratitude to the one who can crack this

Kind regards,

Josh Williams

 

[PHV]

You may try to call DoEvents between each copy and paste.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JosjaWillems]

It seems to work! We'll still need to use this for a couple of days to make sure it keeps working. But for now: thanks man!

 

[JosjaWillems]

As I feared, the error is back. It's the exact same as before. Any other thoughts?

The addition of the 'DoEvents' line did change something, however. Sometimes VBA gives me an error that only gives me the options 'Yes' or 'Help' and doesn't give me any information about the error at all.

Again, I'd be eternally gratefull to the one who manages to solve this!

 

[JosjaWillems]

Right now, VBA even breaks down with a code like this:

Sub ExporteerZakelijkeOfferte1()
    
    Application.ScreenUpdating = False
    Dim wd As Object
    Set wdApp = CreateObject("Word.Application")
    Set wd = wdApp.Documents.Add
    wdApp.ScreenUpdating = False
    wdApp.Visible = True

    Sheets("ZakelijkeOfferte1").Visible = True
    Sheets("ZakelijkeOfferte1").Select

    Range("A39:A62").Select
    Selection.Copy
    wdApp.Selection.Paste
    wdApp.Selection.EndKey
    wdApp.Selection.TypeParagraph

    Range("B1:C32").Select
    Selection.Copy
    wdApp.Selection.Paste
    wdApp.Selection.EndKey
    wdApp.Selection.TypeParagraph

    Range("A64:A86").Select
    Selection.Copy
    wdApp.Selection.Paste
    wdApp.Selection.GoTo What:=wdGoToLine, Which:=wdGoToAbsolute, Count:=1
 
    Sheets("ZakelijkeOfferte1").Visible = False
    Sheets("Hoofdmenu").Select

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    wdApp.ScreenUpdating = True
    
    wdApp.Activate
    
End Sub

Error message is the same as the one I described in my previous post. The error 4605 seems to be gone, now that I've removed the 'hide cells' lines.

Please help me out! This problem is driving me insane!

 

[PHV]

What about this ?

Sub ExporteerZakelijkeOfferte1()
  Application.ScreenUpdating = False
  Dim wd As Object
  Set wdApp = CreateObject("Word.Application")
  Set wd = wdApp.Documents.Add
  wdApp.ScreenUpdating = False
  wdApp.Visible = True
  With Sheets("ZakelijkeOfferte1")
    .Visible = True
    .Range("A39:A62").Copy
    DoEvents
    wdApp.Selection.Paste
    wdApp.Selection.EndKey
    wdApp.Selection.TypeParagraph
    .Range("B1:C32").Copy
    DoEvents
    wdApp.Selection.Paste
    wdApp.Selection.EndKey
    wdApp.Selection.TypeParagraph
    .Range("A64:A86").Copy
    DoEvents
    wdApp.Selection.Paste
    wdApp.Selection.GoTo What:=wdGoToLine, Which:=wdGoToAbsolute, Count:=1
    .Visible = False
  End With
  Sheets("Hoofdmenu").Select
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
  wdApp.ScreenUpdating = True
  wdApp.Activate
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JosjaWillems]

I've found it! The cause of this problem lies in copying and pasting multiple times: even though you copy once and paste afterwards, the copy remains on the clipboard. So after copying and pasting more than once, the computer won't know what to paste anymore.
Solution: add a "application.cutcopymode = false" line after every copy/paste combo.

Thank you all for your help.

 

[JosjaWillems]

So once again, after a few days of use, the same old problem is back. The code currently looks like this:

Sub ExporteerZakelijkeBevestiging1()

    Dim wd As Object
    Application.ScreenUpdating = False
    Set wdApp = CreateObject("Word.Application")
    wdApp.Documents.Add
    wdApp.ScreenUpdating = False
    wdApp.Visible = True
    
    Sheets("ZakelijkeBevestiging1").Visible = True
    Sheets("ZakelijkeBevestiging1").Select

    Range("A39:A65").Copy
    wdApp.Selection.Paste
    wdApp.Selection.EndKey
    wdApp.Selection.TypeParagraph
    Application.CutCopyMode = False

    Range("B1:C32").Copy
    wdApp.Selection.Paste
    wdApp.Selection.EndKey
    wdApp.Selection.TypeParagraph
    Application.CutCopyMode = False

    Range("A67:A80").Copy
    wdApp.Selection.Paste
    wdApp.Selection.EndKey
    Application.CutCopyMode = False

    Range("B81:C90").Copy
    wdApp.Selection.Paste
    wdApp.Selection.GoTo What:=wdGoToLine, Which:=wdGoToAbsolute, Count:=1
    Application.CutCopyMode = False

    Sheets("ZakelijkeBevestiging1").Visible = False
    Application.ScreenUpdating = True
    wdApp.ScreenUpdating = True
    wdApp.Activate
    
End Sub

(Hide/unhide cells are excluded)

The main difference between this code and the previous, is that it now shuts down with an error without a message if I run it by normally activating the macro. However, if I 'scroll through' using F8, nothing happens. Perhaps that's a clue.

And once again, any help would be greatly appreciated.

 

[PHV]

What about the code I suggested 11 Feb 11 4:09 ?

 

[JosjaWillems]

I'm sorry I hadn't responded yet, but it didn't work. Thank you for your input though.

Meanwhile, I've tried using the following line to copy the stuff:
Range("A1:B100").SpecialCells(xlCellTypeVisible).Copy

I'm thinking Word can't paste hidden cells. That would explain the vague error of the "invalid clipboard".

Once again, this seems to work. However, as in others attemps to solve this, I'll have to wait and see if this works permanently.

 

[JosjaWillems]

An error without message still occurs sporadically, though not as regularly as error 4605. Perhaps using the 'Dim wd as object' sucks up too much capacity, causing the subroutine to crash sometimes. Also, the IT-infrastructure here is known to be unstable. I don't know if this is relevant, but I thought I should mention this.

 

[rlamping]

>> 9 Feb 11 11:36 PHV (MIS) Wrote:
>> You may try to call DoEvents between each copy and paste.

I did so and Time to failure dropped, but still I got the error 4605.

Then I tried to call DoEvents also before the Copy action.
That seemed to be enough to let the problem disappear.
I think I will insert another one at some strategic point in my procedure, because in a duration test, the error appeared after the creation of 26 documents in 30 minutes or so. (Creation of a document using Excel as input)

For information about DoEvents see:

http://en.allexperts.com/q/Visual-Basic-1048/DoEvents-Function.htm

DoEvents is used to force the application to yield it's use of the processor for one cycle (usually 50ms), it's commonly used to allow other events in your application to fire while you're performing a resource intensive operation.

 

[SkipVought]

I can run PHV's complete code posted, 11 Feb 11 4:09, WITHOUT ERROR!

Did you actually COPY & PASTE his code and run?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[rlamping]

Dear Skipvought,

I was not refering to PHV's code. But I was glad that after many searches for this error 4506, that I found someone suggesting to use DoEvents. And therefore I added my two cents for possible further improvement.