Copy from Excel into a new word document

[one4youman]

I am looking to create an Excel macro that when ran will open a new word document, add a few lines of Text, copy a table / range from Excel, paste the range to fit, and then to run through a loop copying cells from Excel and pasting the text into word from a specified range, but only if the cell is not blank.

For example:
Copy excel range SBF!B2:H20

Open a new word document, insert and insert text:

    Selection.TypeParagraph
    Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
    Selection.TypeText Text:="STATUS BY FEATURE REPORT"
    Selection.TypeParagraph ‘Inserts a return / enter key

Add todays date centered

    Selection.TypeParagraph
    Selection.TypeParagraph
    Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
    Selection.TypeText Text:="Status:"
    Selection.TypeParagraph
    Selection.TypeParagraph

Paste the Excel Range that was copied (Paste as a table and fit to width)

    Selection.PasteExcelTable False, False, False
    Selection.TypeParagraph
    Selection.TypeParagraph

    Selection.TypeText Text:="Description of Order:"
    Selection.TypeParagraph
    Selection.TypeText Text:="Add Description of items ordered:"

    Selection.TypeParagraph

    Selection.TypeText Text:="ADJUSTMENT:”
    Selection.TypeParagraph

I would then like for the macro to loop through a range (SBF!B8:B17) within Excel (10 cells within a column), copy the cell text, and then paste the cell text into the word document without formatting, and adding text under each item, before continuing with the loop.

For Example:

Copy cell B8 and paste into Word. (Keep text only then make the text bold)
    Selection.TypeText Text:="Add Description of feature.”
    Selection.TypeParagraph

Copy cell B9 and paste into Word. (Keep text only then make the text bold)
    Selection.TypeText Text:="Add Description of feature.”
    Selection.TypeParagraph

Skip B10 as it’s blank.

Copy cell B11 and paste into Word. (Keep text only then make the text bold)

    Selection.TypeText Text:="Add Description of feature.”
    Selection.TypeParagraph

And continue the loop through the remaining cells ending with B17.

    Selection.TypeParagraph
    Selection.TypeParagraph
    Selection.TypeText Text:="Action Plan” (Make this text bold)
    Selection.TypeParagraph
    Selection.TypeText Text:="Add Closing Report.

”

Thank you in advance,
John

 

[Macropod]

Instead of using a macro to 'type' all your boilerplate text into a document, you should use a suitably-formatted Word template, preferably containing one bookmark for where your first Excel range is to be pasted and another bookmark for where your second Excel range is to be output. That way, the amount of code is greatly reduced and the whole process becomes much easier to maintain. The code for such an approach could be as simple as:

Sub CreateDocument()
'Note: A VBA reference to the Word library is required
Dim wdApp As New Word.Application, wdDoc As Word.Document
Dim xlWkSht As Excel.WorkSheet, r As Long, StrTxt As String
Set xlWkSht = Sheets("SBF")
With xlWkSht
  For r = 8 To 17
    If .Range("B" & r).Text <> "" Then StrTxt = StrTxt & vbCr
  Next
  StrTxt = Left(StrTxt, Len(StrTxt) - 1)
End With
With wdApp
  .Visible = True
  Set wdDoc = .Documents.Add("C:\MyDocs\Test\Mywordtemplate.dotx")
  With wdDoc
      xlWkSht.Range("B2:H20").Copy
      .Bookmarks("WordBookMark1").Range.Paste
      .Bookmarks("WordBookMark2").Range.Text = StrTxt
    Next
  End With
End With
End Sub

 

[one4youman]

This was my initial thought as well, but unfortunately the excel file often gets emailed around to multiple different people and it would not be practical to use a template with bookmarks. As such, I am looking for another way.

I also need to use late binding instead of word library reference.

 

[Macropod]

So, either store the template in a location all the users have access to, or email it as well. As for whether you need to use late binding, early binding will suffice provided the code is compiled on the earliest Office version you're supporting. Regardless, you now have the basic code to build on. If you run into any problems, post back and let us know what aspect you're having trouble with.