help with VBA and clipboard run time error 4605

A

Access Beginner

Active Member
Joined
Nov 8, 2010
Messages
311
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I have a about 20 bookmarks to copy from Excel into Word. Below is an example of two Subs I call. The issue I have that sometimes it will run through these with no probs at all and at other times it will bug out with the error: "run time error 4605 this method or property is not available because the clipboard is empty or not valid". Is there something some can suggest to resolve the Intermittent error around the clipboard


Code: 
.PasteSpecial Link:=False, DataType:=wdPasteText, Placement:= _
        wdInLine, DisplayAsIcon:=False

Code: 
Sub Goal5M1Notes()
Dim wdApp As Object
    
    'Open new Word Document
    On Error Resume Next
    
    'Try to establish link to open instance of Word
    Set wdApp = GetObject(, "Word.Application")
    
    'If this fails, open Word
    If wdApp Is Nothing Then
        Set wdApp = GetObject("", "Word.Application")
    End If
    On Error GoTo 0
    
    'Copy Excel Table of the Charts Sheet
    ThisWorkbook.Sheets("Notes").Range("T10").Copy
    
    With wdApp.Selection
        .GoTo What:=wdGoToBookmark, Name:="Goal5M1Notes"
        .PasteSpecial Link:=False, DataType:=wdPasteText, Placement:= _
        wdInLine, DisplayAsIcon:=False

    End With
        
    'Release object variable
    Set wdApp = Nothing

End Sub

Sub Goal5M2Notes()
Dim wdApp As Object
    
    'Open new Word Document
    On Error Resume Next
    
    'Try to establish link to open instance of Word
    Set wdApp = GetObject(, "Word.Application")
    
    'If this fails, open Word
    If wdApp Is Nothing Then
        Set wdApp = GetObject("", "Word.Application")
    End If
    On Error GoTo 0
    
    'Copy Excel Table of the Charts Sheet
    ThisWorkbook.Sheets("Notes").Range("u10").Copy
    
    With wdApp.Selection
        .GoTo What:=wdGoToBookmark, Name:="Goal5M2Notes"
        .PasteSpecial Link:=False, DataType:=wdPasteText, Placement:= _
        wdInLine, DisplayAsIcon:=False

    End With
        
    'Release object variable
    Set wdApp = Nothing

End Sub
 
Sort by date Sort by votes
NdNoviceHlp said:
I don't understand your code. Do U have a seperate routine for every bookmark? I don't see any chart copying? U haven't specified any document? U don't Quit the Word application? Anyways, copy and pasting charts to Word will cause the clipboard to crash. See my last post here for code to address this. HTH. Dave
http://www.mrexcel.com/forum/excel-questions/916620-issue-bookmark-3.html
Click to expand...
Hi Dave Yes I have 40 separate routines that copies a chart or a cell into bookmarks in Word. The two above are two examples. The comments are misleading, I haven't changed those. Apologies.

I have found the code below and will call "EmptyClipboard" after each routine. This seems to work.

Code: 
Option Explicit

Private Declare Function apiOpenClipboard Lib "user32" Alias "OpenClipboard" (ByVal hWnd As Long) As Long
Private Declare Function apiEmptyClipboard Lib "user32" Alias "EmptyClipboard" () As Long
Private Declare Function apiCloseClipboard Lib "user32" Alias "CloseClipboard" () As Long
Private Declare Function CountClipboardFormats Lib "user32" () As Long

Function ClipboardEmpty() As Boolean
    ClipboardEmpty = (CountClipboardFormats() = 0)
End Function

Sub EmptyClipboard()
  If apiOpenClipboard(0&) <> 0 Then
    Call apiEmptyClipboard
    Call apiCloseClipboard
  End If
End Sub
 
Upvote 0
Thanks for posting your outcome. U really only need 1 routine that opens a Word application and does all your stuff at once... OR... 1 routine that U can pass the file name, bookmark name, range and chart name to. I don't understand your ClipboardEmpty function and how/why U use it? Dave
 
Upvote 0
NdNoviceHlp said:
Thanks for posting your outcome. U really only need 1 routine that opens a Word application and does all your stuff at once... OR... 1 routine that U can pass the file name, bookmark name, range and chart name to. I don't understand your ClipboardEmpty function and how/why U use it? Dave
Click to expand...

Hi Thanks for responding. If there is a way where I could have one routine for "Sub Goal5M1Notes" and "Goal5M2Notes" ( I'd be happy if you have some code that does this. As for how the "ClipboardEmpty function" works, I don't know. I found it on the interwebs and it clears the clipboard which I think was causing my code to bug out in various place. I use the "EmptyClipboard" before I call each routine. This does seem to work and I don't get any errors. There may be a more elegant way of doing this, but I haven't found it.

Below is what I'm running atm, as you can see I have a lot of routines. I have to run these reports monthly and I have a selector which will update the charts for different regions and I have 15 regions to report on monthly as well.

Code: 
Sub Publish()

EmptyClipboard
Cover
EmptyClipboard
title
EmptyClipboard
TitleII
EmptyClipboard
ReportMonth
EmptyClipboard
Goal1M1Notes
EmptyClipboard
Goal1M2Notes
EmptyClipboard
Goal1M3Notes
EmptyClipboard
Goal1M4Notes
EmptyClipboard
EmptyClipboard
Goal2M1Notes
EmptyClipboard
EmptyClipboard
EmptyClipboard
Goal2M2Notes
EmptyClipboard
Goal2M3Notes
EmptyClipboard
Goal3M1Notes
EmptyClipboard
Goal3M2Notes
EmptyClipboard
Goal3M3Notes
EmptyClipboard
Goal3M4Notes
EmptyClipboard
Goal3M5Notes
EmptyClipboard
Goal3M6Notes
EmptyClipboard
Goal3M7Notes
EmptyClipboard
Goal4M1Notes
EmptyClipboard
Goal5M1Notes
EmptyClipboard
Goal5M2Notes
EmptyClipboard
Goal5M3Notes
EmptyClipboard
Goal6M1Notes
EmptyClipboard
Goal6M1absNotes
EmptyClipboard
Goal1M1
EmptyClipboard
Goal1M2
EmptyClipboard
Goal1M3
EmptyClipboard
Goal1M4
EmptyClipboard
Goal2M1
EmptyClipboard
Goal2M2
EmptyClipboard
Goal3M1
EmptyClipboard
Goal4M1
EmptyClipboard
Goal5M1
EmptyClipboard
Goal5M2
EmptyClipboard
Goal6M1
EmptyClipboard
Goal6M1abs
EmptyClipboard
Goal1M1NM
EmptyClipboard
Goal1M2NM
EmptyClipboard
Goal1M3NM
EmptyClipboard
Goal1M4NM
EmptyClipboard
Goal2M1NM
EmptyClipboard
Goal2M2NM
EmptyClipboard
Goal2M3NM
EmptyClipboard
Goal3M1NM
EmptyClipboard
Goal3M2NM
EmptyClipboard
Goal3M3NM
EmptyClipboard
Goal3M4NM
EmptyClipboard
Goal3M5NM
EmptyClipboard
Goal3M6NM
EmptyClipboard
Goal3M7NM
EmptyClipboard
Goal4M1NM
EmptyClipboard
Goal5M1NM
EmptyClipboard
Goal5M2NM
EmptyClipboard
Goal5M3NM
EmptyClipboard
Goal6M1NM
EmptyClipboard
FooterMonthly
EmptyClipboard

End Sub
 
Upvote 0
YucK. U need help! Word is a funny thing. VBA wants U to name the document that MUST contain the bookmarks U are referring to in code. The documet could be a template (if your doing it right) or just any old document that contains the relevant bookmark(s). Anyways, U need to stop what you're doing. Does everything go to the same document at once or piece meal... OR... does it go to differrent documents at once.. OR.. to different documents piece meal? U need to provide more info. Plese post the routine that U are using. Dave
 
Upvote 0
NdNoviceHlp said:
YucK. U need help! Word is a funny thing. VBA wants U to name the document that MUST contain the bookmarks U are referring to in code. The documet could be a template (if your doing it right) or just any old document that contains the relevant bookmark(s). Anyways, U need to stop what you're doing. Does everything go to the same document at once or piece meal... OR... does it go to differrent documents at once.. OR.. to different documents piece meal? U need to provide more info. Plese post the routine that U are using. Dave
Click to expand...

I agree it's very YUCK! Here is the code I'm using to open the Word doc and yes it is a template and all charts and cell text will be placed in the one Word doc "Performance Template.docx" and they are imported into the Word doc in the routine order above.

Code: 
Sub Cover()
    Dim wdApp As Object
    
    'Open new Word Document
    On Error Resume Next
    
    'Try to establish link to open instance of Word
    Set wdApp = GetObject(, "Word.Application")
    
    'If this fails, open Word
    If wdApp Is Nothing Then
        Set wdApp = GetObject("", "Word.Application")
    End If
    On Error GoTo 0
    
    With wdApp
        'Add existing template
        .Documents.Open ThisWorkbook.Path & Application.PathSeparator & "Performance Template.docx"
        'Make Word visible
        .Visible = True
      End With
     
   

End Sub
 
Upvote 0
That explains why your not quitting the Word application. Why do U want to open and see the Word "template" doc? For a real template to work, U open it, put stuff in it, save it as a NEW file name, and then close the template document without saving the changes (all without the user seeing it). U then open the NEW document to see your stuff. Do you always run the same routines above in the same order? U can just open the Word application and fill all the bookmarks in the "template" at once instead of doing one at a time. Dave
 
Last edited:
Upvote 0
NdNoviceHlp said:
That explains why your not quitting the Word application. Why do U want to open and see the Word "template" doc? For a real template to work, U open it, put stuff in it, save it as a NEW file name, and then close the template document without saving the changes (all without the user seeing it). U then open the NEW document to see your stuff. Do you always run the same routines above in the same order? U can just open the Word application and fill all the bookmarks in the "template" at once instead of doing one at a time. Dave
Click to expand...

I save the template with a new name, code below. The routines are always in this the order above. As for doing it one at a time or all at once, the above method is the only way I know how to do it.

Code: 
Public Sub FooterMonthly()
 
    Dim wdApp As Object
    Dim i As String
     'VII caveat on 1st page of report
    Dim vii As String
    Dim title As String
         
    'Copy range F4 to use a filename
    i = Sheets("Menu").Range("c6")
    'VII caveat on 1st page of report
    vii = Sheets("Notes").Range("c10")
    'Header based on Notes sheet cell c6
    title = Sheets("Notes").Range("c6")
               
    On Error Resume Next
    'Try to establish link to open instance of Word
    Set wdApp = GetObject(, "Word.Application")
    
    'If this fails, open Word
    If wdApp Is Nothing Then
        Set wdApp = GetObject("", "Word.Application")
    End If
    On Error GoTo 0
                


    
    ' Save the new document with file name from range l2
    Set myr = wdApp.Dialogs(wdDialogFileSaveAs)
    myr.Name = "" & i
    myr.Show
    'wdApp.ActiveDocument.SaveAs FileName:="Servicing  Report - " & i
        
    'Release object variable
    Set wdApp = Nothing
  'VII caveat on 1st page of report. Text is input on the Notes sheet and cell C10
  
    'With ActiveDocument
       ' .PageSetup.DifferentFirstPageHeaderFooter = True
        '.Sections(1).Footers(wdHeaderFooterFirstPage) _
        '.Range.InsertBefore _
        'vii
        
   ' End With
  
  
End Sub

Cheers
Haydn
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Copy Picture - VBA
2
Replies
10
Views
521
Logit
Logit
V
  • Solved
Copying word tableObjects to excelRange in VBA fails
Replies
1
Views
138
VBAneophite22
V
G
  • Question Question
Creating a Word Doc using Excel VBA
Replies
2
Views
413
rikvny02
R
D
  • Question Question
Pulling data from csv and place on worksheet without using clipboard?
Replies
1
Views
113
alansidman
alansidman
J
  • Solved
Create Word document and center title
Replies
5
Views
795
Tetra201
Tetra201

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top