Opening doc in macro
 

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.

Opening doc in macro
 

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.

Opening doc in macro
 

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

Opening doc in macro
 

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\referr als\"))
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.x ls",
ConfirmConversions _
:=False, ReadOnly:=False, LinkToSource:=True,
AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto,
_
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Data
Source=\\powervault2\home_pl\common\Referrals\Refe rals.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" wrote in
message ...

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

Opening doc in macro
 

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\referr als\"))
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.x ls",
ConfirmConversions _
:=False, ReadOnly:=False, LinkToSource:=True,
AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto,
_
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Data
Source=\\powervault2\home_pl\common\Referrals\Refe rals.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" wrote in
message ...

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

Opening doc in macro
 

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.x ls",
ConfirmConversions _
:=False, ReadOnly:=False, LinkToSource:=True,
AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto,
_
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Data
Source=\\powervault2\home_pl\common\Referrals\Refe rals.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" wrote in message
...
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\referr als\"))
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.x ls",
ConfirmConversions _
:=False, ReadOnly:=False, LinkToSource:=True,
AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False,
Format:=wdOpenFormatAuto,
_
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er
ID=Admin;Data
Source=\\powervault2\home_pl\common\Referrals\Refe rals.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" wrote in
message ...

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

Opening doc in macro
 

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

--
tony
-----------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=56075

Opening doc in macro
 

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

Opening doc in macro
 

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" wrote in
message ...

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

Opening doc in macro
 

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" wrote in message
...
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" wrote in
message ...

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