Monarch / Excel

[Phil]

I'm lacking help from my support guys and hopefully one of you can point me in the right direction.

I've got 2 project files osc.prj + osl.prj everyday we have to spool new updated reports and run the prj files and then export them to excel then using an excel macro to filter out seperate users info and print that users report.

Is there anyway (easy to understand) to automate so that monarch will automatically export files to osc.xls and osl.xls so that all we have to do is run the macro in the excel file.

Hope this makes sense :(

[Grant Perkins]

Phil,

You don't mention which version of Monarch you have (V7 for example might offer some interesting options) or where the source files are coming from (Operating system of machine and is it on a network for example) but ...

It certainly should be possible to write a script (or scripts) to pick the file, run the Monarch project(s) and export to xls.

Have a look at Autoscript for example. I have used this to do what you seem to be describing (I am being cautious just in case there is something that could be a blocker). I have also, on occasion, started from the point of creating an ftp session to automatically grab a file from a remote Unix server ...

The only tricky thing was to find a decent Windows NT (at the time) program scheduler when we wanted to automate the process fully. Could be done but nothing we found then ( a few years ago) have the frequency of update we were looking for.

Plan A on the other hand would be to write some VB code for the Excel part of the process and get that to call Monarch from Excel. You may still have some issues about getting scheduling to work though. Also I get the impression from your comments that you don't have the support required to go down that route ...

Let us know which of the above (or neither!)sounds interesting and let's take it from there.

Grant

[Edited lots of odd typos - something must have scrambled the post during the transmission! ;-( ]



Originally posted by Phil:
I'm lacking help from my support guys and hopefully one of you can point me in the right direction.

I've got 2 project files osc.prj + osl.prj everyday we have to spool new updated reports and run the prj files and then export them to excel then using an excel macro to filter out seperate users info and print that users report.

Is there anyway (easy to understand) to automate so that monarch will automatically export files to osc.xls and osl.xls so that all we have to do is run the macro in the excel file.

Hope this makes sense :(

[ September 17, 2003, 05:41 AM: Message edited by: Grant Perkins ]

[Nick Osdale-Popa]

If you are comfortable with using Excel's VBA, you can instruct the macro you have now to run Monarch and export your files.

Be sure to download the Programmers Guide (http://www.datawatch.com/pdf/products/monarch/Monarch_6_Programmers_Guide.pdf) if you haven't already done so. It has excellent examples of controlling Monarch with Visual Basic.

If the downloaded files are already scheduled at a certain time, you can use Windows Task manager to schedule a run time of the Excel macro shortly after the download is completed.

[Phil]

Thanks for the replies - I'll try to answer some of the questions.

Windows NT/2000 Teriminal Services

Monarch Version 5.02 - unlikely at this stage to ge the update

Excel 97 SR-2

The original files are exported from our own internal software as prt files - this has to be done manually.

Monarch's exe file is located on E:Program FilesMonarchprogram

Excel exe file is located on E:Program FilesMirosoft OfficeOffice

prt files (osl.prt & osc.prt) are saved to E:Program FilesMonarchReports

mod files (osl.mod & osc.mod) are saved to
E:Program FilesMonarchModels

I can also use prj files which have been created.

I'm confident with excel @ monarch and have created a few macros but using VB code is new to me and can't see our support staff helping me with this - any help with vb code (is this the Visual Basic Editor in excel)would be much appreciated

Thanx in advance

[Grant Perkins]

Phil,

I forgot to ask if the Excel file you export is a new stand alone file or whether it needs to be appended to the existing excel file (in both cases).

VB stuff is not my area of expertise but it is for some of the other guys so Iwill leave them to provide guidance.

However if you wanted to look at the Autoscript route the Monarch Help files and a quick search of the Forum for Monarch and the FAQ's would provide a lot of input. (autoscript, "export excel" and schedule(r) pick up some interesting and useful information.)

If you go that way watch out for long file names and so on, which DOS will not like. However you can get around that by modifying a script command to the DOS file name format OR simply placing the entire command within quote marks.

If you already have projects available I think you are half way there.

I hope this helps a little.

Grant



Originally posted by Phil:
Thanks for the replies - I'll try to answer some of the questions.

Windows NT/2000 Teriminal Services

Monarch Version 5.02 - unlikely at this stage to ge the update

Excel 97 SR-2

The original files are exported from our own internal software as prt files - this has to be done manually.

Monarch's exe file is located on E:Program FilesMonarchprogram

Excel exe file is located on E:Program FilesMirosoft OfficeOffice

prt files (osl.prt & osc.prt) are saved to E:Program FilesMonarchReports

mod files (osl.mod & osc.mod) are saved to
E:Program FilesMonarchModels

I can also use prj files which have been created.

I'm confident with excel @ monarch and have created a few macros but using VB code is new to me and can't see our support staff helping me with this - any help with vb code (is this the Visual Basic Editor in excel)would be much appreciated

Thanx in advance

[ September 18, 2003, 07:03 AM: Message edited by: Grant Perkins ]

[Phil]

Got the upgrade to Monarch Pro V7

Still hitting the brick wall with inhouse support!

As previously stated we transfer our internal reports into prt files so that monarch can pick them up. These files are named osc.prt and osl.prt

I have created models and project files to pick up the relevant details from the reports and export these into excel.

I have built a simple macro that picks up both reports and filters records to print reports per user.

If anyone can show me how to create VBA code as creating "bat" file with autoscript failed (missing dll etc) I would be greatly appreciated.

Cheers

Phil

[Grant Perkins]

Originally posted by Phil:
Got the upgrade to Monarch Pro V7

Still hitting the brick wall with inhouse support!


If anyone can show me how to create VBA code as creating "bat" file with autoscript failed (missing dll etc) I would be greatly appreciated.

Phil,

So the impossible becomes possible! Good upgrade.

I still can't help you with VBA knowledge but this link (http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000206#000000) may be useful if you have not already seen it.

Failing that, which dll is claimed to be missing from where?

Grant

[ May 19, 2006, 12:28 PM: Message edited by: Todd Niemi ]