Opening doc in macro

  • Thread starter Thread starter Michelle Hanan
  • Start date Start date
M

Michelle Hanan

I have a macro in excel that mail merges a document from word. I can't seem
to get the verbage correct to open the document at the beginning of my
macro. I have used: Set WordBasic = GetObject ("document"), but this isn't
working.
 
See if this helps:

Dim oWd as Word.Application, oWdoc As Word.Document
Set oWd = CreateObject("Word.Application")
Set oWdoc = oWd.Documents.Add
 
try

Dim myDoc As Word.Document

Set myDoc = Word.Documents.Open("C:\MyFiles\MyDoc.doc")

remember to reference the word object library
 
Sorry I'm a beginner and I don't know if I keyed this in correctly..it's not
working. I keep getting Compile error: User-defined type not defined

This is the code that I am trying to run and it's not working.

Dim myDoc As Word.Document
Dim oWd As Word.Application, oWdoc As Word.Document
Set myDoc = Word.Documents.Open("C:\MyFiles\MyDoc.doc")
Set oWd = CreateObject(("\\powervault2\home_pl\common\referrals\"))
Set oWdoc = oWd.Documents.Add
Documents.Open Filename:="""Referal Agency - Ecology.doc""", _
ConfirmConversions:=True, ReadOnly:=False, AddToRecentFiles:=False,
_
PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
wdOpenFormatAuto, XMLTransform:=""
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"\\powervault2\home_pl\common\Referrals\Referals.xls",
ConfirmConversions _
:=False, ReadOnly:=False, LinkToSource:=True,
AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto,
_
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data
Source=\\powervault2\home_pl\common\Referrals\Referals.xls;Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDB:Database Password=" _
, SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="",
SubType:= _
wdMergeSubTypeAccess
With ActiveDocument.MailMerge
.Destination = wdSendToPrinter
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
ActiveWindow.Close
 
I think you have too many sets of quotemarks in places. This could be
causing a problem.

Instead of """Referal Agency - Ecology.doc""" "Referal Agency - Ecology.doc"

Also, be sure you have spelled document names exactly as they are spelled.
 
So I got the macro to run. The only problem now is that I recorded it into
word. For some reason my pc wouldn't let me record any macros in excel. So
my issue now is that I can't get it to run in excel when I copy it over. My
guess is that there needs to be command at the beginning that needs to be
changed? Thank you so much! Here is what I have:

Dim myDoc As Word.Document
Set myDoc =
Word.Documents.Open("\\powervault2\home_pl\common\Referrals\Referal Agency -
Ecology.doc")
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"\\powervault2\home_pl\common\Referrals\Referals.xls",
ConfirmConversions _
:=False, ReadOnly:=False, LinkToSource:=True,
AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto,
_
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data
Source=\\powervault2\home_pl\common\Referrals\Referals.xls;Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDB:Database Password=" _
, SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="",
SubType:= _
wdMergeSubTypeAccess
With ActiveDocument.MailMerge
.Destination = wdSendToPrinter
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
ActiveWindow.Close
End Sub
 
Trying to get the referencing right is not easy in these situations i
you don't have much experience.

A first step though : change ActiveDocument to myDoc. Basically at th
beginning you open the document and "call it" myDoc. Now no matter wha
else happens if you use myDoc it is the document you opened. Wherea
ActiveDocument is just the document that is active at the time - whic
maynot be the one you opened.

This point is true for anything that is labelled Active... o
current... etc

Do you know how to use the debug tools? look up these - especially F8



hope this help
 
Could someone rewrite the macro and pay attention to line breaks,
especially the writedocumentpassword part. TIA

Greg
 
I understand what you're saying Tony, however when I run the macro in excel
it highlights the myDoc as Word.Document line of code and gives me a
compile error: User-defined type not defined. I don't understand why or how
to fix it.
 
I figured it out. I didn't realize that I only had MS Office referenced,
instead of Office and Word. The macro is running now.
I have one more question. I need to run this macro for several documents,
do I need to make a complete new macro for each document or is there a way
that I can add the docs to this macro have it work for each doc.?
 
Back
Top