Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

Interacting with Word from Excel (VBA)

Hi,

I need some help with a macro in Excel. The macro starts in Excel where it copies an area to a new book and saves this under a new name. Then I want it to activate Word and use the Mail Merge function there to merge the newly generated file with a Word letter. If I run the two part of macroes seperately in Excel and Word they work just fine, but when I run it as just one macro from Excel it halts. I have selected the Word library from references under the tool menu, but that didn't help. Can anyone please help me? I don't think this should be too difficult (Probably just an obvious mistake I've made...)

Here's the macro:
Sub Perm()
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs FileName:= _
        "D:\Project\Names.xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    Workbooks("Names.xls").Activate
    ActiveWorkbook.Close

Set WordApp = CreateObject("Word.Basic")

    Documents.Open FileName:= _
        """d:\Project\Orginal.doc""" _
        , ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
        PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
        WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
        wdOpenFormatAuto
    ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "D:\Project\Names.xls", _
        ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:="Entire Spreadsheet", SQLStatement _
        :="", SQLStatement1:=""
    ActiveDocument.MailMerge.EditMainDocument
    Selection.MoveDown Unit:=wdLine, Count:=4
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Arbnr"
    Selection.TypeParagraph
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
        "Fornavn"
    Selection.TypeText Text:=" "
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
        "Etternavn"
    Selection.TypeParagraph
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
        "Adresse"
    Selection.TypeParagraph
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Postnr"
    Selection.TypeText Text:=" "
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
        "Poststed"
    ActiveWindow.ActivePane.SmallScroll Down:=14
    Selection.MoveDown Unit:=wdLine, Count:=16
    Selection.MoveUp Unit:=wdLine, Count:=1
    Selection.MoveLeft Unit:=wdCharacter, Count:=7
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.Delete Unit:=wdCharacter, Count:=1
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
        "Permitteres_fra"
    ActiveWindow.ActivePane.SmallScroll Down:=29
End Sub
 

Regards
bernjerg
0
bernjerg
Asked:
bernjerg
1 Solution
 
tureCommented:
bernjerg,

Include all your Word VBA code in a With / End With structure. Add a period before every object, property or method that refers to Word. Something like this:

....

Set WordApp = CreateObject("Word.Application")
With WordApp
  .Documents.Open FileName:= _
  "d:\Project\Orginal.doc", _
  ConfirmConversions:=False, _
  ReadOnly:=False, _
  AddToRecentFiles:=False, _
  PasswordDocument:="", _
  PasswordTemplate:="", _
  Revert:=False, _
  WritePasswordDocument:="", _
  WritePasswordTemplate:="", _
  Format:= wdOpenFormatAuto

  .ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
  .ActiveDocument.MailMerge.OpenDataSource _
  Name:= "D:\Project\Names.xls", _
  ConfirmConversions:=False, _
  ReadOnly:=False, _
  LinkToSource:=True, _
  AddToRecentFiles:=False, _
  PasswordDocument:="", _
  PasswordTemplate:="", _
  WritePasswordDocument:="", _
  WritePasswordTemplate:="", _
  Revert:=False, _
  Format:=wdOpenFormatAuto, _
  Connection:="Entire Spreadsheet", _
  SQLStatement:="", _
  SQLStatement1:=""
   
  ...

End With
End Sub

Ture Magnusson
Karlstad, Sweden
0
 
antratCommented:
Hi bernjerg

It think it is because you have the line "ActiveWorkbook.Close" in your code, so when it reaches this point it stops. Try taking this line out and see what happens.

There is also no need for the line Workbooks("Names.xls").Activate in your code as "Names.xls" would already be the activeworkbook.

let me know how you go?

antrat
   
0
 
calacucciaCommented:
Hi Bjernberg, try this one:

Sub Perm()
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs FileName:= _
        "D:\Project\Names.xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    Workbooks("Names.xls").Activate
    ActiveWorkbook.Close

Set WordApp = CreateObject("Word.Application")
With WordApp.Application
    .Visible = True
    .Documents.Open FileName:= _
        """d:\Project\Orginal.doc""" _
        , ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
        PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
        WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
        wdOpenFormatAuto
    .ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    .ActiveDocument.MailMerge.OpenDataSource Name:= _
        "D:\Project\Names.xls", _
        ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:="Entire Spreadsheet", SQLStatement _
        :="", SQLStatement1:=""
    .ActiveDocument.MailMerge.EditMainDocument
    .Selection.MoveDown Unit:=wdLine, Count:=4
    .ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Arbnr"
    .Selection.TypeParagraph
    .ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
        "Fornavn"
    .Selection.TypeText Text:=" " 
    .ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
        "Etternavn"
    .Selection.TypeParagraph
    .ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
        "Adresse"
    .Selection.TypeParagraph
    .ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Postnr"
    .Selection.TypeText Text:=" " 
    .ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
        "Poststed"
    ActiveWindow.ActivePane.SmallScroll Down:=14
    .Selection.MoveDown Unit:=wdLine, Count:=16
    .Selection.MoveUp Unit:=wdLine, Count:=1
    .Selection.MoveLeft Unit:=wdCharacter, Count:=7
    .Selection.Delete Unit:=wdCharacter, Count:=1
    .Selection.Delete Unit:=wdCharacter, Count:=1
    .Selection.Delete Unit:=wdCharacter, Count:=1
    .Selection.Delete Unit:=wdCharacter, Count:=1
    .Selection.Delete Unit:=wdCharacter, Count:=1
    .Selection.Delete Unit:=wdCharacter, Count:=1
    .ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
        "Permitteres_fra"
    ActiveWindow.ActivePane.SmallScroll Down:=29
End With
End Sub

Basically as Ture explained, use the With WordApp and End With, and add . before any Selection or ThisDocument or other Word related commands. Only thing is I had to set With WordApp.Application in the With loop, in order to get this to work.

I've also added the line
..Visible = True
so that you can see the started instance of Word.

Another good thing to do, is to declare your variable WordApp as an object in the top of the module
Dim WordApp As Object

The only lines I did not precede by . were the two ActiveWindow lines.

Good Luck

Calacuccia
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
bernjergAuthor Commented:
Well guys, I think we're moving in the right direction. What happens now is that the macro halts when the first mail merge field is to be added in. Again; when I run this part of the macro from Word it works fine.

The error message is:  Run timer error '450': Wrong number of arguments or invalid property assignment.

A comment to antrat: The macro executes (for some reason) another Excel application and tries to open Names.xls in the new application. If I don't close the workbook first I need to open Names.xls again as read only.

Regards
bernjerg
0
 
bernjergAuthor Commented:
Adjusted points from 100 to 150
0
 
tureCommented:
bernjerg,

You need to add the period before everything that refers to the Word application.

So, this isn't enuf:
  .ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Arbnr"
(Selection refers to the Excel selection. Not good.)


This is better:
  .ActiveDocument.MailMerge.Fields.Add Range:=.Selection.Range, Name:="Arbnr"
(.Selection refers to the Word selection. Much better.)

/Ture
0
 
bernjergAuthor Commented:
Takk, Ture!

That was much better. I found a method to work around the problem but now I can do it the right way! I created the mail merge macro in a auto_open macro in a word document and opened the Word document from the Excel macro.

Regards
bernjerg
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now