automating copy from Excel to paste in Word

[stuck]

Current situation:
I have an XL workbook as a source and a Word doc as the destination.
I open both and in XL select a chart, from one of several chart sheets, then copy it.
In Word, above a caption, I paste special as picture (enhanced metafile).
Then go back to XL, copy a different chart, switch back to Word and paste special above another caption.

What I think want is as follows:
In Word, click a button and be presented with a File Open type dialog that allows me to select an XL workbook.
Once the XL book is open I'd like to see a list of the charts so I can choose one.
Having picked a chart, be presented with a list of the captions in the Word doc so that I can choose the destination.
Click again to complete the copy & paste.
Be asked if I want to copy & paste another or exit.

Is this possible? Is it sensible?

All suggestions gratefully received.

Ken

[HansV]

Have the captions been formatted with the Caption style?

[stuck]

Yes, the captions are 'real' captions, created via Insert Caption and formatted with the Caption style.

Ken

[HansV]

I'll see if I can come up with something, but it might take a while.

[stuck]

Many thanks Hans, there is no rush. I only really need a proof of concept at this stage. I feel that what I want can be done but I'm not sure if what I've outlined is the best way to do it.

Googling for this sort of thing finds examples but they all seem to 'push' from Excel to Word, i.e. the code appears to be XL VBA that opens Word and dumps the paste at a bookmark. Perhaps my thinking, to have Word Code that finds the source in XL, i.e. to 'pull' from Word to XL, is a harder way of achieving what I'd like?

Ken

[HansV]

It shouldn't be particularly hard to run the code from Word. It's fairly standard "automation" code.

[HansV]

See the attached document. Make sure that you enable macros when you open it. It contains a small macro and a userform. Run the macro to open the userform, the rest should be self-evident.

CopyChart.docm

[Rudi]

See the attached document. Make sure that you enable macros when you open it. It contains a small macro and a userform. Run the macro to open the userform, the rest should be self-evident.

CopyChart.docm

Great idea Ken
Very nifty code Hans

[stuck]

The code works well when I run it from your demo .docm but having copied it over to a 'real' .docm it fails. The problem appears to be in Sub UserForm_Initialize.

Stepping through this code and reaching the Do While .Execute loop I can 'Selection.Text' increment through the first three captions but the next time round the loop it does not increment.

Looking more closely as it steps through each caption I've noticed that when the code selects the first and second captions the selection includes the paragraph mark at the end of the line but it doesn't select the paragraph mark at the end of the third caption. If I manually extend the selection to include that para. mark then the code will step to the next caption but again it doesn't include the para. mark and again it won't progress to the next caption until I extend it to include this mark.

Any clues why it's OK with captions one and two but then fails?

Ken

[HansV]

My guess would be that there is something wrong with those captions - perhaps they have been edited after they had been inserted?

What happens if you remove the captions then insert them again?

[stuck]

I'm experimenting with deleting and reinserting these captions but no joy yet.

Meanwhile, the first captions in the document are actually Tables, so they're not actually relevant at the moment. Is there some way of getting the code to only pick out the Figures? I've discovered that if I tweak the line just above the Do While loop that says .Text = "" to say .Text = "Figure" then the form only finds Figure captions but each entry in the list is the same, the numbers are lost. Is there a way to say .Text = "Figure*" (where * means wild card/all subsequent chars)?

Ken

[stuck]

Discovered the problem!

The 'faulty' captions are ones that are NOT followed by another paragraph, i.e. the caption sits directly above a table, no lines between it and the table.

Can this be worked around? Apart form the obvious, add an empty paragraph, which I can't do because it will add space I don't want / haven't got room for.

Ken

[StuartR]

The best way to check to see if a caption is a Figure is to compare
left(selection.Fields(1).Code,11)
to
" SEQ Figure"

[HansV]

Try this version of the code for cmdCopy_Click and Userform_Initialize (the rest of the code can remain the same):

Code: Select all

Private Sub cmdCopy_Click()
    Dim xlCht As Object
    Dim lngCaptionIndex As Long
    Dim lngComboIndex As Long
    If Me.lbxCharts.ListIndex = -1 Then
        Me.lbxCharts.SetFocus
        MsgBox "Please select a chart!", vbExclamation
        Exit Sub
    End If
    If Me.lbxCaptions.ListIndex = -1 Then
        Me.lbxCaptions.SetFocus
        MsgBox "Please select a caption!", vbExclamation
        Exit Sub
    End If
    Set xlCht = xlWbk.Charts(Me.lbxCharts.Value)
    xlCht.ChartArea.Copy
    lngComboIndex = Me.lbxCaptions.ListIndex
    Application.ScreenUpdating = False
    Selection.HomeKey Unit:=wdStory
    With Selection.Find
        .Text = Me.lbxCaptions.Value
        .ClearFormatting
        .Style = wdStyleCaption
        .Format = True
        .MatchWildcards = False
        .Execute
    End With
    Selection.MoveUp Unit:=wdParagraph
    Selection.PasteSpecial Placement:=wdInLine, DataType:=wdPasteEnhancedMetafile
    Application.ScreenUpdating = True
    DoEvents
End Sub

Private Sub UserForm_Initialize()
    On Error Resume Next
    Set xlApp = GetObject(Class:="Excel.Application")
    If xlApp Is Nothing Then
        Set xlApp = CreateObject(Class:="Excel.Application")
        If xlApp Is Nothing Then
            MsgBox "Can't start Excel!", vbCritical
            Unload Me
        End If
        blnStart = True
    End If
    Application.ScreenUpdating = False
    Selection.HomeKey Unit:=wdStory
    With Selection.Find
        .Text = "Figure*^13"
        .ClearFormatting
        .Style = wdStyleCaption
        .Format = True
        .MatchWildcards = True
        Do While .Execute
            Me.lbxCaptions.AddItem Left(Selection.Text, Len(Selection.Text) - 1)
        Loop
    End With
    Application.ScreenUpdating = True
End Sub

[stuck]

Nope, using .Text = Figure*^13" doesn't help.
Edited later
I lied, it does work I just missed the .matchwildcards bit.

Thank you,

Ken

[stuck]

I'm back at this again...

A user had XL open and minimised without realising that Excel was in the middle of an edit, i.e. the cursor was in the formula bar. Unsurprisingly when they then tried to run the copy chart code from Word it died with "Automation error, Call was rejected by callee" as the first thing the copy chart code does is to open an instance of Excel, which it can't because Excel is already busy.

Is there some code I can add to the start that traps this so that I can pop-up a message box that tells the user to finish the edit in Excel?

Thanks,

Ken

[stuck]

Thanks, got the answer via The Lounge's telepathic interface (you know how it works as soon as you post your question and the answer pops into your head)

I've worked out that if when I try to read a cell I get an error that means Excel is busy so I can display a message box:

Code: Select all

If IsError(xlApp.Range("A1").Value) Then
MsgBox "Excel is busy, finish the edit then try again", vbExclamation, "CRASH!"
Unload Me
End If

Thanks!

Ken

[stuck]

This code has worked well for about two and a half years but I've hit a snag now that I'm trying to invoke the code in a subtly different way.

Until now I had the code in Normal.dotm and ran it from a button on the Ribbon that I'd added using the built-in right-click on the Ribbon and select 'Customize the Ribbon' feature. Now though, I've moved the code to a separate template, one that loads as a global template because it lives in Word's STARTUP folder. This 'tools.dotm' template includes custom RibbonX that creates a dedicated tab on the Ribbon and one of the buttons in that tab invokes this code. So far so good, except...

In Sub UserForm_Initalize, when it gets to final bit, searching the document for (Figure) captions, it no longer finds any. It is as if the search is being run on the tools.dotm template (which is empty apart from code) rather than the open document that definitely does hold the type of caption in question.

Which is a long way of asking why should moving the code out of 'Normal.dotm' (the global, global template) into 'tools.dotm' (a global template) mean that this bit:

Code: Select all

Application.ScreenUpdating = False
Selection.HomeKey Unit:=wdStory
With Selection.Find
     .Text = "Figure*^13"
     .ClearFormatting
     .Style = wdStyleCaption
     .Format = True
     .MatchWildcards = True
     Do While .Execute
          Me.lbxCaptions.AddItem Left(Selection.Text, Len(Selection.Text) - 1)
     Loop
End With
Application.ScreenUpdating = True

no longer searches the open document?

Ken

[HansV]

It shouldn't make a difference whether you run the code from Normal.dotm or from an add-in, but apparently it does. I assume that Tools.dotm doesn't load as a visible document in Word.

What happens if you change all occurrences of Selection to ActiveWindow.Selection ?

And how about this version?

Code: Select all

Dim rng As Range
Application.ScreenUpdating = False
Set rng = ActiveDocument.Content
With rng.Find
     .Text = "Figure*^13"
     .ClearFormatting
     .Style = wdStyleCaption
     .Format = True
     .MatchWildcards = True
     Do While .Execute
          Me.lbxCaptions.AddItem Left(rng.Text, Len(rng.Text) - 1)
     Loop
End With
Application.ScreenUpdating = True

[stuck]

Hi Hans,

Thanks, I didn't think the location of the code should be a factor, it hasn't been for other stuff I've relocated to this 'tools.dotm' global template. I'll bear in mind your idea of changing Selection to ActiveWindow.Selection but since I posted my question it seems to be working as expected now and I swear:
1) It didn't work late yesterday afternoon
2) It still didn't work this morning (hence my post)
3) I've not changed anything since I made the post


There must be something else at play here. I'll report back later...

Ken