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!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,982
Messages
5,834,678
Members
430,309
Latest member
Deepjuillet

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