VBA - How to open a sheet range in MS Word, complete with all formatting from the Excel sheet

Jasesair

Active Member
Joined
Apr 8, 2015
Messages
263
Office Version
  1. 2016
I have a sheet range (A1:I18) that is formatted the way I want with picture, different row and column widths, cell highlighting, cell borders, different font sizes etc. I'd like assign macro to a button that then sends the user to MS Word, opening up the Excel cells as converted in an MS Word file. Any help to do this would be fantastic!

I'm very, very green to VBA and have no idea even how to record macros etc.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Jogender singh

New Member
Joined
May 21, 2020
Messages
15
Office Version
  1. 2013
Platform
  1. Windows
you need to make link with word file to send excel range in word
 

Jogender singh

New Member
Joined
May 21, 2020
Messages
15
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

select desire range in excel
copy that
right click in word file
and select paste option
"link & keep source formating"
 

Jasesair

Active Member
Joined
Apr 8, 2015
Messages
263
Office Version
  1. 2016
select desire range in excel
copy that
right click in word file
and select paste option
"link & keep source formating"
That's exactly what I want to do but I need all that to happen with one button click from Excel. I need that button click to open a new Word file and insert the range I need, with the source formatting.
 

Jogender singh

New Member
Joined
May 21, 2020
Messages
15
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

VBA Code:
Private Sub CommandButton1_Click()
Set wordapp = CreateObject("word.application")
wordapp.documents.Open "e:\test.docx" 'word file address
wordapp.Visible = True

 Range("A1:F9").Select 'range which you want to transfer to word file
     Selection.Copy
     Application.CutCopyMode = False
End Sub
 

Jogender singh

New Member
Joined
May 21, 2020
Messages
15
Office Version
  1. 2013
Platform
  1. Windows
and write this code to word file


Private Sub Document_Open()
Selection.PasteExcelTable True, False, False
End Sub
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,570
Simpler, more reliable, and more straightforward:
VBA Code:
Private Sub CommandButton1_Click()
Dim wdApp As Object, wdDoc As Object
Range("A1:F9").Copy
Set wdApp = CreateObject("Word.Application")
With wdApp
  .Visible = True
  Set wdDoc = .Documents.Add
  wdDoc.PasteExcelTable True, False, False
End With
Application.CutCopyMode = False
End Sub
 

Forum statistics

Threads
1,141,045
Messages
5,703,922
Members
421,321
Latest member
blusky4

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