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

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!
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Eslava

Board Regular
Joined
Nov 20, 2007
Messages
112
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!
 

Eslava

Board Regular
Joined
Nov 20, 2007
Messages
112
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!
 

Eslava

Board Regular
Joined
Nov 20, 2007
Messages
112
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,213
Messages
5,594,870
Members
413,945
Latest member
V51773

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
Top