Scripting

[Gareth Horton]

The fastest, most dependable way to script Monarch is to use the OLE function of whatever database you use. If you are interested in scripting into excel, you would probably do best to set up a form in Access whose sole function is to be the control panel for your OLE commands. I have done this in Paradox using ObjectPAL, and by using this methodology, you don't have to call up multiple sessions of monarch in order to apply different models. Instead, you launch Monarch only once and then apply, one after the other, the various filters and/or models you would like to apply to your source file. An example of this kind of code (which of course would be different in Access's Visual Basic than it is in the following which is written in Paradoxx's ObjectPAL. I know it looks complex, but I am very ordinary, and learned this through the help files in Monarch and through the help files in Paradox. It isn't nearly as hideous as it looks. I can't encourage you enough to dig dig dig in the help files to learn this sort of thing as it makes IMMENSE improvements in productivity.

method pushButton(var eventInfo Event)
var
n1,n2 number
fs filesystem
tc tcursor
call oleauto {This is how you designate an OLE object in Paradox}
input,path,r1,a string
ar1 array[]anytype
dynar1 dynarray[]anytype
s1,s2,s3,s4,s5,s6,s7,s8,s9,s10,s11,s12,s13,s14,s15 string
sum1,sum2,sum3,sum4,sum5,sum6,sum7 string
exp1,exp2,exp3,exp4,exp5,exp6,exp7 string
Rpt1 string
element string {for dynarray ops}
endvar
path="l:isdatastaging
eports"
r1="l:isdatastaging
eports
1210.txt"
input=path+"FY01_xx.txt"
input.view("Amend File Name.")

a=path+"*.txt"

while
fs.findnext(a)
ar1.insertfirst(fs.name())
endwhile
ar1.view("File List... ")
n1=ar1.size()
n1.view("Number of Files.")

for i from 1 to n1
a=ar1[i]
dynar1[a]=a
endfor

sum1="A- Revenue x Doctor - CM" ;export to L:ISDATASTAGING
eportsMONTH_ENDSA_rpt.DB
sum2="B-All Rev+All ARxDr, BOT thruCM" ;export to L:ISDATASTAGING
eportsMONTH_ENDSB_rpt.DB
sum3="C- Revenue x Doctor CFY thru CM" ;export to L:ISDATASTAGING
eportsMONTH_ENDSC_rpt.DB
sum4="D- Capitation x Practice CFY" ;export to L:ISDATASTAGING
eportsMONTH_ENDSD_rpt.DB
sum5="E- Rev+W/Os x PtType FY-CM" ;export to L:ISDATASTAGING
eportsMONTH_ENDSE_rpt.DB
sum6="F- All AR x Pt Type - BOTthruCM" ;export to L:ISDATASTAGING
eportsMONTH_ENDSF_rpt.DB
sum7="CHARGES for Linda Patry, CM" ;export to L:ISDATASTAGING
eportsMONTH_ENDSG_rpt.DB

exp1="L:ISDATASTAGING
eportsMONTH_ENDSA_rpt.DB"
exp2="L:ISDATASTAGING
eportsMONTH_ENDSB_rpt.DB"
exp3="L:ISDATASTAGING
eportsMONTH_ENDSC_rpt.DB"
exp4="L:ISDATASTAGING
eportsMONTH_ENDSD_rpt.DB"
exp5="L:ISDATASTAGING
eportsMONTH_ENDSE_rpt.DB"
exp6="L:ISDATASTAGING
eportsMONTH_ENDSF_rpt.DB"
exp7="L:ISDATASTAGING
eportsMONTH_ENDSG_rpt.DB"

Rpt1="L:ISDATASTAGINGmodelsMEDIC_Report.mod"

if not call.open("Monarch32") then errorshow() else message("Calling Monarch Pro") sleep(500) endif

{THE ABOVE LINE CALLS MONARCH AS AN OLE automation}

foreach element in dynar1
s2=path+element
s2.view("File Being Opened.")
if not call.setreportfile(s2,1) then errorshow() else message("Loading Report") sleep (250) endif
endforeach

if not call.setmodelfile(Rpt1) then errorshow() else message("Loading MODEL ") sleep (250) endif
Message("Model Loaded.")

call.currentsummary(sum1) message("Current summary is ... "+sum1)
call.exportsummary(exp1) message("Exporting exp1 file.")
call.currentsummary(sum2) message("Current summary is ... "+sum2)
call.exportsummary(exp2) message("Exporting exp2 file.")
call.currentsummary(sum3) message("Current summary is ... "+sum3)
call.exportsummary(exp3) message("Exporting exp3 file.")
call.currentsummary(sum4) message("Current summary is ... "+sum4)
call.exportsummary(exp4) message("Exporting exp4 file.")
call.currentsummary(sum5) message("Current summary is ... "+sum5)
call.exportsummary(exp5) message("Exporting exp5 file.")
call.currentsummary(sum6) message("Current summary is ... "+sum6)
call.exportsummary(exp6) message("Exporting exp6 file.")
call.currentsummary(sum7) message("Current summary is ... "+sum7)
call.exportsummary(exp7) message("Exporting exp7 file.")

call.exit()

endMethod

Michael wrote:
> Hi Richard
>
> Here's a simple dos batch file that will do the trick for you - create it in say notepad and save it as a *.bat file on your desktop - whenever you doubleclick it, the file will first open the report named report.prn, then apply model model.mod, and export the table view (that is defined by the '/t' parameter - if you need the summary exported, the parameter should be '/s'), and finally it opens the spreadsheet excelsheet.xls.
>
> cd..
> cd..
> cd..
> cd..
> cd program files
> cd monarch
> cd program
> monarch.exe c:mjh
eport.prn c:mjhmodel.mod c:mjhexcelsheet.xls /t
> c:mjhexcelsheet.xls
>
> Good luck
>
> Michael
>
> Richard Breeze wrote:
> > Hi there
> > I have just discovered this new feature on the site - great improvement.
> > I have been using Monarch for10 - 12 years best and most useful product I have ever bought.
> > I wish to write scripts to export to the latest version of Excel, the problem is that I
> > have not found the manual very useful.
> >
> > Has does the script handle writing to a excel spreadsheet named tab !!
> >
> > Any advice would be very useful.
> > PS are there any newsletters or books available on Monarch
>
>
>