4 Replies Latest reply: May 15, 2014 9:59 AM by Lynette _ RSS

    Automated projects

    Lynette _

      Hi,

      Am sure my question might have been addressed before and honestly did check the faqs...but guess i need more info.

       

      I have monarch 5.02pro and 6 std versions..

      My query:

      Some of our reports are scheduled for autodownload

      from Oracle 11i to a local PC. What I need is is

      1. A scheduled run of a monarch model to generate various summaries.

      2. Specified summary sheets to be emailed to certain users..

      Any good suggestions.... please revert, preferably with detailed replies.

        • Automated projects
          Mark Huston

          For scheduling, I'd use Macro Scheduler (a trial version is available) or Windows' Task Scheduler to open an Excel workbook at a certain time. In the workbook, a Workbook_Open event would trigger a subroutine in Excel.

           

          The sub would access the Monarch object model, open Monarch, open the report, apply the model, export the summaries, then access the Outlook object model and e-mail the summary workbooks.

           

          Here's code that exports all the summaries for a model. It assumes multiple reports in your destination folder.

           

          [font="courier"]<< Variables Dimmed >>

           

          Sub SliceAndDiceLynnettesReports()

           

           

          Set MonarchObj = GetObject("", "Monarch32")

          PathName = "C:DownloadLynnette'sReports" '(Acts on all files in this folder)

          fName = Dir(PathName & ".")  '(Set up dir and returns first file in Pathname)

          While (fName <> "")  '(Loops until "" found)

              If (fName <> ".") And (fName <> "..") Then

                  If Left(fName, 24) = "Lynnette's Oracle Report" Then

                      PathAndFile = PathName & fName

                      OpenFile = MonarchObj.SetReportFile(PathAndFile, False)

                      If OpenFile = True Then

                          ModFile = "C:Monarch ModelsOracleModel.mod"

                          DestPath = "C:ProgramFilesMonarchExport"

                          OpenMod = MonarchObj.SetModelFile(ModFile)

                          If OpenMod = True Then

                          SummaryCount = MonarchObj.SummaryCount ' Determine the # of summaries

                              For LoopCounter = 0 To (SummaryCount - 1) Step 1

                                  MonarchObj.CurrentSummary = MonarchObj.GetSummaryNameAt(LoopCounter) 'Apply the Summary

                                  MonarchObj.exportsummary (DestPath & fName & MonarchObj.GetSummaryNameAt(LoopCounter) & ".XLS") 'Export the Summary

                              Next LoopCounter

                          End If

                      End If

                  End If

              End If

          fName = Dir()

          Wend

           

          End Sub /font[/quote]Ran out of time today, but I'll try to send an example of code to send a worksheet through Outlook on Monday.

          HTH,

          Mark!

          • Automated projects
            Lynette _

            Thanks Mark..will check it out and revert.Look foward to the  example of code to send a worksheet through Outlook ...whenever time permits...

            • Automated projects
              Mark Huston

              Hi, Lynette.

              Here's some basic code to send an Excel file as an e-mail attachment. With a little tweaking, you can loop through a list of e-mail recipients, loop through a set of summaries to send as attachments, or both. If you need more specific help, let me know. Good luck.

              Mark!

               

              [font="courier"] 

               

              Sub MailLynnettesReports()

               

              Set ol = CreateObject("Outlook.Application")

              Set mailitem = ol.CreateItem(olMailItem)

               

              ThisRecipient = "mark.huston@wwireless.com"

              ThisAttachment = "C:ProgramFilesMonarchExport" & YourSummaryFileName

              msg = "Hi, Boss!" & vbCrLf & vbCrLf & "Here's the latest Oracle report!" _

              & vbCrLf & vbCrLf & "Regards," & vbCrLf & "Lynnette" & vbCrLf & vbCrLf

               

              With mailitem

                  .To = ThisRecipient

                  .Subject = "Latest auto-generated Oracle report"

                  .Body = msg

                  .Attachments.Add ThisAttachment

                  .Display

                  .Send

              End With

               

              Set ol = Nothing

              Set mailitem = Nothing

               

              End Sub[/font][/quote]

              • Automated projects
                Lynette _

                Thanks Mark.... I have been successful.... and with the macro scheduled I save a lot of time.