Solved

Interacting with Word from Excel (VBA)

Posted on 2000-03-17
7
296 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
My experience with Windows 10 over a one year period and suggestions for smooth operation
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…

760 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

20 Experts available now in Live!

Get 1:1 Help Now