Copying from Word to Excel 2

[pradipto]

Hi,

I use the following code below to copy from a range in Excel to a bookmark in Word. Can someone help me to write the code for the reverse operation, copying from Word to Excel?

--------------------------------------------
Public Sub CopyFromExcelToWord()

Dim xl As Excel.Application
Set xl = CreateObject("Excel.Application&quot
xl.Workbooks.Open ("myExcelFile.xls&quot

ActiveWorkbook.Worksheets("Sheet1&quot.Activate

xl.ActiveSheet.Range("MyRange&quot.Copy
ActiveDocument.Bookmarks("MyBookmark&quot.Select
Selection.PasteSpecial

End Sub
--------------------------------------------

I tried doing the reverse, i.e.

ActiveDocument.Bookmarks("MyBookmark&quot.Copy

but it seems to give an error saying that an argument is required for the copy, which I haven't been able to figure out. Sorry, if this problem seems trivial - I am not an expert VBA programmer.

Thanks,
Pradipto

 

[rmikesmith]

Paradipto,

The Copy method as it applies to the Bookmark object copies the bookmarked text to another bookmark (the name of this second bookmark is the missing parameter referred to). I used the Range method on Bookmark. However, when I tried to use Copy and PasteSpecial methods, this embedded a Word Object into the selected worksheet cell rather than as text. So I did something different which seems to work. The code is as follows:

Sub CopyToExcel()
Dim xl As Object
Dim S As Variant

Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open ("myExcelFile.xls")
xl.Visible = True  'Not necessary but allows you to see what happens
S = ActiveDocument.Bookmarks("Target").Range.Text
xl.ActiveWorkbook.Worksheets("Sheet1").Activate
xl.ActiveSheet.Range("A1").Value = S
' Other workbook operations here; e.g. save & close workbook
xl.Quit
Set xl = Nothing
End Sub

Hope this helps

M. Smith

 

[pradipto]

Thanks Smith. Your code works fine with copying one cell from word to excel. Can you please help me further? In my case, the bookmark is a table (say 2x2) in word and the range is also a table (same size, 2x2) in excel. When I use your code for copying a table from word to excel then the whole table from word gets copied in each cell of the table in cell with a lot of bad characters. I tried

Range("MyRange&quot.PasteSpecial s

but it just put a picture instead. What the alternative?

 

[rmikesmith]

pradipto,

Try the following instead. This seems to work for bookmarks referencing both text and tables.

Sub CopyToExcel()
Dim xl As Object

Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open ("myExcelFile.xls")
xl.Visible = True   'Not necessary but allows you to see what happens
ActiveDocument.Bookmarks("Table1").Range.Copy
xl.ActiveWorkbook.Worksheets("Sheet1").Activate
xl.Activesheet.Range("A1").Select
xl.Activesheet.Paste
' Other workbook operations here; e.g. save & close workbook
xl.Quit
Set xl = Nothing
End Sub

Regards,
M. Smith

 

[asjeff]

Might I suggest that you use GetObject instead of CreateObject, otherwise you will be creating several instances of the Application? You can then trap the error in case the Application isnt open. Try something like this:

Dim objWord as New Word.Application
On Error goto ErrorHandler:

Set objWord = GetObject(, "Word.Application&quot

[Rest of code here]

ErrorHandler:
(I believe this is the error number if an instance
isn't open)
if Err.Number = 429 then
Set objWord = CreateObject("Word.Application&quot
Resume
End If

Just a suggestion but I hope it helps

Asjeff

 

[rmikesmith]

Thanks AsJeff.

I think that is good practical advice, especially in terms of conserving resources. If used, I would make a couple of changes to my code (i.e. if an application instance already exists, don't try to close; perhaps use specific workbook reference, etc.).

One note: my procedure would run from Word and open Excel, so I would substitute "Excel.Application" in your code.

Regards,
M. Smith