Help tweak my VBA. (Excel pasting to Word)

[Shales]

Hello,

I have found some code online which I have managed to tailor to my needs but I just need it tweaked very slightly.

The problem I have is the VBA only works properly is there isn't another Word Doc open. If there is another Doc open, it will paste the excel contents into the already opened Doc, not the desired Doc.

I need the VBA to activate the Complaint_Letter_Template_V1.docm before proceeding with the paste.

Thank you very much.

Here is the code;

Sub Generate_Letter()
'
' Generate_Letter Macro
'
Dim Wdoc As Object

Dim Cell As Range

'On Error Resume Next
Set Wdoc = GetObject("K:\COMPLAINTS\Complaint Handling Letter Templates\Complaint_Letter_Template_V1.docm")
Set WdApp = Wdoc.Parent
If Wdoc Is Nothing Then

Set Wdoc = WdApp.Documents.Add
End If
WdApp.Visible = True

'write cells to Word
With Wdoc.Content
.Delete
WdApp.Options.ReplaceSelection = False
For Each Cell In Range("Letter_Range")
With WdApp.Selection
.TypeText Text:=Cell.Text
.TypeParagraph
End With
Next Cell
End With


Set Wdoc = Nothing

End Sub

 

[ranman256]

Windows("Complaint_Letter_Template_V1.docm").Activate

THEN put data

 

[Shales]

Thanks for the reply, Ranman256. Where does this line fit into my code?

Thanks

 

[Macropod]

Your code structure is quite illogical you've got undeclared variables you're referencing out of order, then ignoring the original one, etc. Ranman's code really isn't going to help here.

Try something along the lines of:

Sub Generate_Letter()
Dim wdApp As Object, wdDoc As Object, StrDocNm As String, bStrt As Boolean, xlCell As Range, StrTxt As String
'Check whether the document exists
StrDocNm = "K:\COMPLAINTS\Complaint Handling Letter Templates\Complaint_Letter.dotm"
If Dir(StrDocNm) = "" Then
  MsgBox "Cannot find the designated template: " & StrDocNm, vbExclamation
  Exit Sub
End If
' Test whether Word is already running.
On Error Resume Next
bStrt = False ' Flag to record if we start Word, so we can close it later.
Set wdApp = GetObject(, "Word.Application")
'Start Word if it isn't running
If wdApp Is Nothing Then
  Set wdApp = CreateObject("Word.Application")
  If wdApp Is Nothing Then
    MsgBox "Can't start Word.", vbExclamation
    Exit Sub
  End If
  ' Record that we've started Word, so we can terminate it later.
  bStrt = True
End If
On Error GoTo 0
For Each xlCell In ActiveSheet.Range("Letter_Range")
  StrTxt = StrTxt & vbCr & xlCell.Text
Next xlCell
With wdApp
  'If bStrt = True Then .Visible = False
  .Visible = True
  'Create a new document from the template.
  Set wdDoc = .Documents.Add(StrDocNm)
  With wdDoc
    'Only now can we can process the document!!!
    .Range.InsertAfter StrTxt
    'Now we can save the document
    .SaveAs 'See Word VBA help for required parameters
    'Close the document
    .Close False
  End With
  'If bStrt = True Then .Quit
End With
Set wdDoc = Nothing: Set wdApp = Nothing
End Sub

Note 1: You should be using a Word template for this, not an existing document. Your existing code suggests the document contains macros, so save a basic copy of it as a macro enabled template (I suggest "Complaint_Letter.dotm").
Note 2: I have no idea what your loop is supposed to be doing, other than inserting a number of paragraphs. As coded, it's assumed they're to be inserted after whatever else is already in the template.
Note 3: You'll need to specify the Save parameters, if you're saving the document; otherwise replace that line with whatever code you need for what you want to do with the document.
Note 4: Once you have the code working, you should be able to deleted the '.Visible = True' line and uncomment the two lines beginning with 'If bStrt = True'

PS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.

 

[Shales]

Thanks very much for your reply, Paul.

I have tried your code but unfortunately it fails to Run.

It highlights the 'Cell' in the 'NEXT CELL' line, and says the following, " Compile Error: Invalid Next Control Variable Reference"

Note1: I am using a Template for this. What was going wrong was the Template was not being used if another Document was already open. The Template would open but the contents of the excel file would paste into the other Document.
Note2: I believe the loop was copying and pasting each line from the Excel Range one at a time until it reached the end of the range.
Note3: I will consider this at a later date.
Note4: I will consider this.

Thanks for your help. Really appreciate it.

Regards
Gavin

 

[Macropod]

It highlights the 'Cell' in the 'NEXT CELL' line, and says the following, " Compile Error: Invalid Next Control Variable Reference"

Typo should have been 'Next xlCell'. Fixed.

I am using a Template for this.

No, you're not. Your code explicitly references a macro-enabled document (Complaint_Letter_Template_V1.docm). Documents are not templates. Furthermore, the way your code referenced it is quite inappropriate for a template.

 

[Shales]

Perfect. Thank you very much for your assistance, Paul.

Apologies for sounding patronising. That's just me being a rookie.

Regards
Gavin

 

[Shales]

Actually, the code still isn't fully working.

I have stepped through each line and it fails at "Set wdDoc = .Documents.Add(Filename:=StrDocNm)" - Run-Time Error '448': Named Argument not found"

Regards
Gavin

 

[Macropod]

Try deleting 'Filename:='

 

[Shales]

Hi Paul,

This is now working perfectly.

As previously advised, I have removed the automatic "Save As" line of code for now.

Really appreciate your help.

Have a good day
Gavin