?
Solved

Interacting with Word from Excel (VBA)

Posted on 2000-03-17
7
Medium Priority
?
328 Views
Last Modified: 2008-01-09
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
Comment
Question by:bernjerg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 22

Expert Comment

by:ture
ID: 2628001
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
 
LVL 9

Expert Comment

by:antrat
ID: 2629642
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
 
LVL 17

Expert Comment

by:calacuccia
ID: 2630321
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:bernjerg
ID: 2635016
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
 

Author Comment

by:bernjerg
ID: 2635343
Adjusted points from 100 to 150
0
 
LVL 22

Accepted Solution

by:
ture earned 600 total points
ID: 2657018
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
 

Author Comment

by:bernjerg
ID: 2663621
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
New style of hardware planning for Microsoft Exchange server.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question