Opening doc in macro

Michelle Hanan · 07-12-2006, 12:20 PM

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.

Jim Jackson · 07-12-2006, 12:35 PM

See if this helps:

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

--
Best wishes,

Jim

"Michelle Hanan" wrote:

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

**tony h** · 07-12-2006, 12:40 PM

try

Dim myDoc As Word.Document

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

remember to reference the word object library

Michelle Hanan · 07-12-2006, 01:15 PM

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

"tony h" <tony.h.2aucx5_1152722709.6593@excelforum-nospam.com> wrote in
message news:tony.h.2aucx5_1152722709.6593@excelforum-nospam.com...
>
> try
>
> Dim myDoc As Word.Document
>
> Set myDoc = Word.Documents.Open("C:\MyFiles\MyDoc.doc")
>
> remember to reference the word object library
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile:
> http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=560754
>

Jim Jackson · 07-12-2006, 01:25 PM

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.
--
Best wishes,

Jim

"Michelle Hanan" wrote:

> 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
>
> "tony h" <tony.h.2aucx5_1152722709.6593@excelforum-nospam.com> wrote in
> message news:tony.h.2aucx5_1152722709.6593@excelforum-nospam.com...
> >
> > try
> >
> > Dim myDoc As Word.Document
> >
> > Set myDoc = Word.Documents.Open("C:\MyFiles\MyDoc.doc")
> >
> > remember to reference the word object library
> >
> >
> > --
> > tony h
> > ------------------------------------------------------------------------
> > tony h's Profile:
> > http://www.excelforum.com/member.php...o&userid=21074
> > View this thread: http://www.excelforum.com/showthread...hreadid=560754
> >
>
>
>

Michelle Hanan · 07-12-2006, 04:55 PM

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
"Jim Jackson" <JimJackson@discussions.microsoft.com> wrote in message
news:890775F0-D52A-4838-B4D1-05CD65FF58C3@microsoft.com...
>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.
> --
> Best wishes,
>
> Jim
>
>
> "Michelle Hanan" wrote:
>
>> 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
>>
>> "tony h" <tony.h.2aucx5_1152722709.6593@excelforum-nospam.com> wrote in
>> message news:tony.h.2aucx5_1152722709.6593@excelforum-nospam.com...
>> >
>> > try
>> >
>> > Dim myDoc As Word.Document
>> >
>> > Set myDoc = Word.Documents.Open("C:\MyFiles\MyDoc.doc")
>> >
>> > remember to reference the word object library
>> >
>> >
>> > --
>> > tony h
>> > ------------------------------------------------------------------------
>> > tony h's Profile:
>> > http://www.excelforum.com/member.php...o&userid=21074
>> > View this thread:
>> > http://www.excelforum.com/showthread...hreadid=560754
>> >
>>
>>
>>

**tony h** · 07-13-2006, 06:23 AM

Trying to get the referencing right is not easy in these situations if you don't have much experience.

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

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

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

hope this helps

GregR · 07-13-2006, 11:05 AM

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

Greg
tony h wrote:
> Trying to get the referencing right is not easy in these situations if
> you don't have much experience.
>
> A first step though : change ActiveDocument to myDoc. Basically at the
> beginning you open the document and "call it" myDoc. Now no matter what
> else happens if you use myDoc it is the document you opened. Whereas
> ActiveDocument is just the document that is active at the time - which
> maynot be the one you opened.
>
> This point is true for anything that is labelled Active... or
> current... etc
>
> Do you know how to use the debug tools? look up these - especially F8.
>
>
>
> hope this helps
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=560754

Michelle Hanan · 07-13-2006, 11:20 AM

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.
"tony h" <tony.h.2avpzr_1152786306.6491@excelforum-nospam.com> wrote in
message news:tony.h.2avpzr_1152786306.6491@excelforum-nospam.com...
>
> Trying to get the referencing right is not easy in these situations if
> you don't have much experience.
>
> A first step though : change ActiveDocument to myDoc. Basically at the
> beginning you open the document and "call it" myDoc. Now no matter what
> else happens if you use myDoc it is the document you opened. Whereas
> ActiveDocument is just the document that is active at the time - which
> maynot be the one you opened.
>
> This point is true for anything that is labelled Active... or
> current... etc
>
> Do you know how to use the debug tools? look up these - especially F8.
>
>
>
> hope this helps
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile:
> http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=560754
>

Michelle Hanan · 07-13-2006, 11:55 AM

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.?

"Michelle Hanan" <Michelle.Hanan@colchelan.wa.us> wrote in message
news:ub8n48opGHA.2256@TK2MSFTNGP03.phx.gbl...
>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.
> "tony h" <tony.h.2avpzr_1152786306.6491@excelforum-nospam.com> wrote in
> message news:tony.h.2avpzr_1152786306.6491@excelforum-nospam.com...
>>
>> Trying to get the referencing right is not easy in these situations if
>> you don't have much experience.
>>
>> A first step though : change ActiveDocument to myDoc. Basically at the
>> beginning you open the document and "call it" myDoc. Now no matter what
>> else happens if you use myDoc it is the document you opened. Whereas
>> ActiveDocument is just the document that is active at the time - which
>> maynot be the one you opened.
>>
>> This point is true for anything that is labelled Active... or
>> current... etc
>>
>> Do you know how to use the debug tools? look up these - especially F8.
>>
>>
>>
>> hope this helps
>>
>>
>> --
>> tony h
>> ------------------------------------------------------------------------
>> tony h's Profile:
>> http://www.excelforum.com/member.php...o&userid=21074
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=560754
>>
>
>