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.

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


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

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

[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!




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

[Lynette]

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