VBA to export from Excel, as a picture, into an exsisting word document.

[Lakesactor]

I'm trying to piece together a VBA code to export a selection from an Excel tab and add it, as a picture, to the end of a user selected word document.

So far i've gotten

Sub archivetowrd()
'
Dim objWord, objDoc As Object
Dim strFileToOpen As String
 
 

 strFileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to open", _
FileFilter:="Word Files *.docx*,")
If strFileToOpen = False Then
MsgBox "No file selected.", vbExclamation, "Sorry!"
Exit Sub
Else
Workbooks.Open Filename:=strFileToOpen
End If
 
ActiveWindow.View = xlNormalView
    Range("A1:G18").Select
    Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
    Set objWord = CreateObject("Word.<wbr>Application")
    Set objDoc = strFileToOpen
    objWord.Visible = True
    objWord.Selection.Paste
    objWord.Selection.<wbr>TypeParagraph
    ActiveWindow.View = xlNormalView
    Range("A1:G18").Select
    Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
    Set objWord = CreateObject("Word.<wbr>Application")
    Set objDoc = StrFileToOppen
    objWord.Visible = True
    objWord.Selection.Paste
    objWord.Selection.<wbr>TypeParagraph
 
End Sub

this code, however, generates errors no mater what tweaks I make. Unfortunately my coding skill is limited to what I can google, and get to talk to each other. this one's been failing me.

 

[Macropod]

It's quite unclear what you're trying to do with that code.

First, you try to select a Word document, via:
strFileToOpen = Application.GetOpenFilename
but then you try to open your Word document in Excel, via:
Workbooks.Open Filename:=strFileToOpen
That's bound to cause an error. Having tried that (and failed), you're apparently trying to copy a range from the active workbook.

You then try to instantiate Word with:
Set objWord = CreateObject("Word.<wbr>Application")
but <wbr> has no place in this code and will error-out.
After that, you then use:
Set objDoc = strFileToOpen
to assign a string variable to objDoc; that too will fail.

Then you have:
objWord.Selection.Paste
objWord.Selection.<wbr>TypeParagraph
which, aside from the errant <wbr>, would basically overtype anything you'd pasted with a paragraph break.

Your code then tries to repeat the above copy/paste steps.

Try:

Sub ArchiveToWord()
Dim objWord As Object, objDoc As Object, strFileToOpen As String
With Application.FileDialog(msoFileDialogFilePicker)
  .Title = "Please choose an file to open"
  .Filters.Add "Word Files", "*.docx"
  If .Show = -1 Then strFileToOpen = .SelectedItems(1)
End With
If strFileToOpen = "" Then
  MsgBox "No file selected.", vbExclamation, "Sorry!"
  Exit Sub
Else
  Set objWord = CreateObject("Word.Application")
  Set objDoc = objWord.Documents.Open(strFileToOpen)
  ActiveSheet.Range("A1:G18").Copy
  With objDoc
    .Range.InsertAfter vbCr
    .Characters.Last.PasteAndFormat 13
    .Close True
  End With
  objWord.Quit
End If
Application.CutCopyMode = False
Set objDoc = Nothing: Set objWord = Nothing
End Sub

 

[Lakesactor]

Thank you so much! I really am a petty dabbler when it comes to this. It doesn't help that I've been away from VBA for over a year.