Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro buttons opening saved macro | Excel Discussion (Misc queries) | |||
run macro on opening | Excel Discussion (Misc queries) | |||
Macro upon opening | Excel Worksheet Functions | |||
Run Macro Upon Opening | Excel Programming | |||
Excel Macro Issue Trying to autorun Macro Upon Opening Worksheet | Excel Programming |