Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
close doc and call next macro
I have a series of macros in one module. I need them to run one right after
the other and for each one to close without saving. The macros are in excel and merge data from an excel worksheet into word documents. I'm not sure what codes to use or where to put them. Here are two of the macros so you can get an idea of what I am trying to do. Sub Ecology() Dim wdApp As Object Dim myDoc As Object Set wdApp = CreateObject("Word.Application") Set myDoc = wdApp.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 Pa" _ , 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 End Sub Sub BuildingFireSafety() Dim wdApp As Object Dim myDoc As Object Set wdApp = CreateObject("Word.Application") Set myDoc = wdApp.Documents.Open("\\powervault2\home_pl\common \Referrals\Referal Agency - Building & Fire Safety.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 Pa" _ , 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 End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
close doc and call next macro
Michelle,
I'm not quite sure if I follow what you are trying to do, but if you want these 2 macros to together one right after the other, I would create a new macro that calls these 2 from within it. Something like this: Sub MainMacro() Ecology BuildingFireSafety End Sub Then all you would have to do is run this MainMacro. If you have more macros you want to run, just add their names to this MainMacro in the order that you want them to run. I'm not quite sure what you mean by "I need them to run one right after the other and for each one to close without saving." Do you want the Word Documents you are working with to close w/o saving? If that is the case, then ad the following line to each macro (probably at the very end): myDoc.Close wdDoNotSaveChanges I hope this helps, Conan "Michelle Hanan" wrote in message ... I have a series of macros in one module. I need them to run one right after the other and for each one to close without saving. The macros are in excel and merge data from an excel worksheet into word documents. I'm not sure what codes to use or where to put them. Here are two of the macros so you can get an idea of what I am trying to do. Sub Ecology() Dim wdApp As Object Dim myDoc As Object Set wdApp = CreateObject("Word.Application") Set myDoc = wdApp.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 Pa" _ , 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 End Sub Sub BuildingFireSafety() Dim wdApp As Object Dim myDoc As Object Set wdApp = CreateObject("Word.Application") Set myDoc = wdApp.Documents.Open("\\powervault2\home_pl\common \Referrals\Referal Agency - Building & Fire Safety.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 Pa" _ , 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 End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can run a macro ( call a macro) on selection of any filtercriteria? | Excel Worksheet Functions | |||
Call an Access macro from an Excel macro | Excel Discussion (Misc queries) | |||
Excel Macro call Word Macro with Parameters | Excel Programming | |||
Call Macro in Add-in | Excel Programming | |||
Call another Macro | Excel Programming |