Solved

Interacting with Word from Excel (VBA)

Posted on 2000-03-17
7
299 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
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 150 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

920 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now