automating copy from Excel to paste in Word
-
- Panoramic Lounger
- Posts: 8177
- Joined: 25 Jan 2010, 09:09
- Location: retirement
automating copy from Excel to paste in Word
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
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
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: automating copy from Excel to paste in Word
Have the captions been formatted with the Caption style?
Best wishes,
Hans
Hans
-
- Panoramic Lounger
- Posts: 8177
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: automating copy from Excel to paste in Word
Yes, the captions are 'real' captions, created via Insert Caption and formatted with the Caption style.
Ken
Ken
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: automating copy from Excel to paste in Word
I'll see if I can come up with something, but it might take a while.
Best wishes,
Hans
Hans
-
- Panoramic Lounger
- Posts: 8177
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: automating copy from Excel to paste in Word
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
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
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: automating copy from Excel to paste in Word
It shouldn't be particularly hard to run the code from Word. It's fairly standard "automation" code.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: automating copy from Excel to paste in Word
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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: automating copy from Excel to paste in Word
Great idea KenHansV wrote: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.
Very nifty code Hans
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Panoramic Lounger
- Posts: 8177
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: automating copy from Excel to paste in Word
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
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
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: automating copy from Excel to paste in Word
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?
What happens if you remove the captions then insert them again?
Best wishes,
Hans
Hans
-
- Panoramic Lounger
- Posts: 8177
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: automating copy from Excel to paste in Word
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
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
-
- Panoramic Lounger
- Posts: 8177
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: automating copy from Excel to paste in Word
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
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
-
- Administrator
- Posts: 12612
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: automating copy from Excel to paste in Word
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"
left(selection.Fields(1).Code,11)
to
" SEQ Figure"
StuartR
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: automating copy from Excel to paste in Word
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
Best wishes,
Hans
Hans
-
- Panoramic Lounger
- Posts: 8177
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: automating copy from Excel to paste in Word
Edited later
I lied, it does work I just missed the .matchwildcards bit.
Thank you,
Ken
-
- Panoramic Lounger
- Posts: 8177
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: automating copy from Excel to paste in Word
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
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
-
- Panoramic Lounger
- Posts: 8177
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: automating copy from Excel to paste in Word
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:
Thanks!
Ken
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
Ken
-
- Panoramic Lounger
- Posts: 8177
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: automating copy from Excel to paste in Word
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:
no longer searches the open document?
Ken
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
Ken
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: automating copy from Excel to paste in Word
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?
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
Best wishes,
Hans
Hans
-
- Panoramic Lounger
- Posts: 8177
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: automating copy from Excel to paste in Word
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
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