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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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

O
  • Solved
VBA Novice here: Opening a new Word Document from Excel
Replies
2
Views
237
Offroadracer_814
O
B
  • Question
Excel Membership Data to Word Document
Replies
7
Views
239
Beaglebud
B
M
  • Question
Error 1004 - Can't Copy as Picture, but only on certain computer
2
Replies
10
Views
326
rlv01
R
D
  • Question
Create empty table (with headings) in Word document after some text
Replies
5
Views
152
dwsteyl
D
D
  • Question
Excel Word Link - part table Excel to Word, part Word to Excel
Replies
2
Views
95
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,215,066
Messages
6,122,948
Members
449,095
Latest member
nmaske

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