Converting cells to text strings, copy to Word

[roscoe]

What I need to do:
1) Collect random data throughout a work book
2) Merge selected pieces of this data into text strings
3) Write to a word Document with certain formats

I can deal with the first two although not sure how yet (kinda depended on how I had to do step 3). I suspect that I will collect everything into an array, and then write each line to Word witht ehdesired format.

Not sure how to handle the 3rd step. Let's say I have a multiple strings of text that I want written to a new Word document, with selected lines in a bold, red font. How would I do that?

Thanks!

 

[Datsmart]

The following code will copy the selected cells to a New Word document, or an existing open Word document. The code changes the selected cells to "Bold" and "Red", copies it to Word, then changes the selected cells back to None Bold and Black format.

Sub CopySelection2Word()
'Bind to an existing or created instance of Microsoft Word
Dim objApp As Object

    Selection.Font.Bold = True
    Selection.Font.Color = vbRed
    Selection.Copy
    
'Attempt to bind to an open instance
On Error Resume Next
Set objApp = GetObject(, "Word.Application")

If Err.Number <> 0 Then
    'Could not get instance, so create a new one
    Err.Clear
    On Error GoTo ErrHandler
    Set objApp = CreateObject("Word.Application")
    With objApp
          .Visible = True
          .Documents.Add
    End With
Else
    'Bound to instance, activate error handling
    On Error GoTo ErrHandler
End If

'Paste Selection to Word document
With objApp.Documents(1)
    .Range.Paste
End With
'Remove Bold and Red Formats on Selection
    Selection.Font.Bold = False
    Selection.Font.Color = 1

ErrHandler:
'Release the object and resume normal error handling
Set objApp = Nothing
On Error GoTo 0
End Sub

Note that the Text from Excel will be input into Word with Word's default Margins and Line Spacing, etc.

 

[roscoe]

That's very helpful, except I'm not copying per se but I'm writing directly from VBA (I know I said copy in the title...my bad). How would that change the code?

 

[roscoe]

Re-engaging on this, but does anyone have a clue how to write from a VBA array directly to a word document? Not copying, but generating the the text in VBA and outputting directly.

Thanks!

 

[Datsmart]

Have you tried recording a Macro within Word?

If so, what does the code look like?

The Macro Recorder is a great way to get started in building your own code.

 

[roscoe]

Time to try again. Using a macro inside word doesn't really help ans I'm trying to write to a word object from a VBA array. I can't even begin to fathom how I'd do that.

I've found dozens of sites that describe cutting and pasting cell values to word, but not writing driectly from memory.

Thanks

 

[Datsmart]

My thought was that recording a macro with-in WORD could create the portion of code you would use in your Excel workbook to write to WORD.