Cut and Paste an table in Excel to a Word Document using VBA...can it be done???

E

Eslava

Board Regular
Joined
Nov 20, 2007
Messages
112
I figured out how to transfer data from Excel to a Word document, but can I take a table that I've created in Excel and have it be a cut and paste special Picture (enhanced metafile)?

Thank you very much in advance!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Re: Cut and Paste a table in Excel to a Word Document using VBA...can it be done???

FYI, I have this code running:

Sub worddoc()

Dim objectword As Word.Document
Dim appWD As Word.Application
Dim filename2 As String




Set appWD = New Word.Application

Calculate

filename2 = Worksheets("file_inputs").Range("directory2").Value & "NSBA_Staff_Update_Program.doc"




Set objectword = appWD.Documents.Add(filename2)

objectword.Bookmarks("current_compa").Range.Text = Format(Worksheets("data").Range("current_compa").Value, "00.0%")
objectword.Bookmarks("currentnew_compa").Range.Text = Format(Worksheets("data").Range("currentnew_compa").Value, "00.0%")
objectword.Bookmarks("average_tenure").Range.Text = Format(Worksheets("data").Range("average_tenure").Value, "0.0")
objectword.Bookmarks("median_tenure").Range.Text = Format(Worksheets("data").Range("median_tenure").Value, "0.0")
objectword.Bookmarks("min_emp").Range.Text = Format(Worksheets("summary").Range("min_emp").Value, "0")
objectword.Bookmarks("min_percent").Range.Text = Format(Worksheets("summary").Range("min_percent").Value, "00.0%")
objectword.Bookmarks("low_emp").Range.Text = Format(Worksheets("summary").Range("low_emp").Value, "0")
objectword.Bookmarks("low_percent").Range.Text = Format(Worksheets("summary").Range("low_percent").Value, "00.0%")
objectword.Bookmarks("mid_emp").Range.Text = Format(Worksheets("summary").Range("mid_emp").Value, "0")
objectword.Bookmarks("mid_percent").Range.Text = Format(Worksheets("summary").Range("mid_percent").Value, "00.0%")
objectword.Bookmarks("high_emp").Range.Text = Format(Worksheets("summary").Range("high_emp").Value, "0")
objectword.Bookmarks("high_percent").Range.Text = Format(Worksheets("summary").Range("high_percent").Value, "00.0%")
objectword.Bookmarks("max_emp").Range.Text = Format(Worksheets("summary").Range("max_emp").Value, "0")
objectword.Bookmarks("max_percent").Range.Text = Format(Worksheets("summary").Range("max_percent").Value, "00.0%")
objectword.Bookmarks("total_emp").Range.Text = Format(Worksheets("summary").Range("total_emp").Value, "0")
objectword.Bookmarks("total_percent").Range.Text = Format(Worksheets("summary").Range("total_percent").Value, "00.0%")
objectword.Bookmarks("overall_compa").Range.Text = Format(Worksheets("data").Range("overall_compa").Value, "00.0%")

appWD.Visible = True




End Sub

Can a paste special enhanced metafile code be used with a specific bookmark within the word document?

Thanks again!
 
Upvote 0
Gave this a shot:

Sub worddoc()

Dim objectword As Word.Document
Dim appWD As Word.Application
Dim filename2 As String




Set appWD = New Word.Application
Set objWord = CreateObject("Word.Application")

Calculate

filename2 = Worksheets("file_inputs").Range("directory2").Value & "NSBA_Staff_Update_Program.doc"




Set objectword = appWD.Documents.Add(filename2)

objectword.Bookmarks("current_compa").Range.Text = Format(Worksheets("data").Range("current_compa").Value, "0.0%")
objectword.Bookmarks("currentnew_compa").Range.Text = Format(Worksheets("data").Range("currentnew_compa").Value, "0.0%")
objectword.Bookmarks("average_tenure").Range.Text = Format(Worksheets("data").Range("average_tenure").Value, "0.0")
objectword.Bookmarks("median_tenure").Range.Text = Format(Worksheets("data").Range("median_tenure").Value, "0.0")
objectword.Bookmarks("min_emp").Range.Text = Format(Worksheets("summary").Range("min_emp").Value, "0")
objectword.Bookmarks("min_percent").Range.Text = Format(Worksheets("summary").Range("min_percent").Value, "0.0%")
objectword.Bookmarks("low_emp").Range.Text = Format(Worksheets("summary").Range("low_emp").Value, "0")
objectword.Bookmarks("low_percent").Range.Text = Format(Worksheets("summary").Range("low_percent").Value, "0.0%")
objectword.Bookmarks("mid_emp").Range.Text = Format(Worksheets("summary").Range("mid_emp").Value, "0")
objectword.Bookmarks("mid_percent").Range.Text = Format(Worksheets("summary").Range("mid_percent").Value, "0.0%")
objectword.Bookmarks("high_emp").Range.Text = Format(Worksheets("summary").Range("high_emp").Value, "0")
objectword.Bookmarks("high_percent").Range.Text = Format(Worksheets("summary").Range("high_percent").Value, "0.0%")
objectword.Bookmarks("max_emp").Range.Text = Format(Worksheets("summary").Range("max_emp").Value, "0")
objectword.Bookmarks("max_percent").Range.Text = Format(Worksheets("summary").Range("max_percent").Value, "0.0%")
objectword.Bookmarks("total_emp").Range.Text = Format(Worksheets("summary").Range("total_emp").Value, "0")
objectword.Bookmarks("total_percent").Range.Text = Format(Worksheets("summary").Range("total_percent").Value, "0.0%")
objectword.Bookmarks("overall_compa").Range.Text = Format(Worksheets("data").Range("overall_compa").Value, "0.0%")
Worksheets("structure_data").Range("structure").Activate
Selection.Copy
objectword.Bookmarks("structure").Range.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, _
Placement:=wdInLine, DisplayAsIcon:=False



appWD.Visible = True




End Sub


But, there was an error with this line:
Worksheets("structure_data").Range("structure").Activate

Did I do it correctly?

Hope that can help the cause!
 
Upvote 0
I figured it out!!!

Here's the code:

Sub worddoc()

Dim objectword As Word.Document
Dim appWD As Word.Application
Dim filename2 As String




Set appWD = New Word.Application
Set objWord = CreateObject("Word.Application")

Calculate

filename2 = Worksheets("file_inputs").Range("directory2").Value & "NSBA_Staff_Update_Program.doc"




Set objectword = appWD.Documents.Add(filename2)

objectword.Bookmarks("current_compa").Range.Text = Format(Worksheets("data").Range("current_compa").Value, "0.0%")
objectword.Bookmarks("currentnew_compa").Range.Text = Format(Worksheets("data").Range("currentnew_compa").Value, "0.0%")
objectword.Bookmarks("average_tenure").Range.Text = Format(Worksheets("data").Range("average_tenure").Value, "0.0")
objectword.Bookmarks("median_tenure").Range.Text = Format(Worksheets("data").Range("median_tenure").Value, "0.0")
objectword.Bookmarks("min_emp").Range.Text = Format(Worksheets("summary").Range("min_emp").Value, "0")
objectword.Bookmarks("min_percent").Range.Text = Format(Worksheets("summary").Range("min_percent").Value, "0.0%")
objectword.Bookmarks("low_emp").Range.Text = Format(Worksheets("summary").Range("low_emp").Value, "0")
objectword.Bookmarks("low_percent").Range.Text = Format(Worksheets("summary").Range("low_percent").Value, "0.0%")
objectword.Bookmarks("mid_emp").Range.Text = Format(Worksheets("summary").Range("mid_emp").Value, "0")
objectword.Bookmarks("mid_percent").Range.Text = Format(Worksheets("summary").Range("mid_percent").Value, "0.0%")
objectword.Bookmarks("high_emp").Range.Text = Format(Worksheets("summary").Range("high_emp").Value, "0")
objectword.Bookmarks("high_percent").Range.Text = Format(Worksheets("summary").Range("high_percent").Value, "0.0%")
objectword.Bookmarks("max_emp").Range.Text = Format(Worksheets("summary").Range("max_emp").Value, "0")
objectword.Bookmarks("max_percent").Range.Text = Format(Worksheets("summary").Range("max_percent").Value, "0.0%")
objectword.Bookmarks("total_emp").Range.Text = Format(Worksheets("summary").Range("total_emp").Value, "0")
objectword.Bookmarks("total_percent").Range.Text = Format(Worksheets("summary").Range("total_percent").Value, "0.0%")
objectword.Bookmarks("overall_compa").Range.Text = Format(Worksheets("data").Range("overall_compa").Value, "0.0%")

ActiveWorkbook.Sheets("structure_data").Activate
Range("structure").Copy

objectword.Bookmarks("structure").Range.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, Placement:=wdInLine, DisplayAsIcon:=False



appWD.Visible = True




End Sub


At the end, it goes to the excel sheet and range that I'm copying from (which is kind of annoying) but it does work.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Copy Picture - VBA
2
Replies
10
Views
204
Logit
Logit
J
  • Question Question
How to convert a word document to excel without losing formatting
Replies
9
Views
558
JTH94BT
J
M
  • Question Question
Dynamic link from Word to Excel...
Replies
8
Views
247
Matteo85
M
D
  • Question Question
Generating Reports in Word Using Data in Excel
Replies
1
Views
402
Dan_W
D
S
  • Question Question
Saving an Excel Range as Word and PDF document
Replies
5
Views
515
Trevor G
Trevor G

Forum statistics

Threads
1,224,564
Messages
6,179,543
Members
452,924
Latest member
JackiG

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