Gareth Horton
July 12th, 2002, 01:28 PM
I have the same need, to export specific sheets into a pre-existing Excel file.
Here is an example of VBA code ran from a master file that collects information from
1 of 25 print files ran through Monarch:
Set monarchobj = CreateObject("monarch32")
monarchobj.DisplayWindow ("0")
openfile = monarchobj.setreportfile("c:DOWNLOADstats01.PRN", False)
If openfile = True Then
openmod = monarchobj.setmodelfile("c:PROGRAM FILESmonarchmodelsSTATS - 01 - Acute Admits.mod")
If openmod = True Then
monarchobj.exporttable ("C:statsstats01.xls")
End If
End If
monarchobj.closealldocuments
AppActivate "Microsoft Excel"
Worksheets("01 - Admits").Activate
Workbooks.Open FileName:="C:statsstats01.xls"
Range("A1:d15").Select
Selection.Copy
Windows("Downloads.xls").Activate
Range("A1").Select
ActiveCell.PasteSpecial
Windows("stats01.xls").Activate
SendKeys "{esc}", True
ActiveWindow.Close
This code would proceed to another file to export and copied to a different sheet.
Any suggestions or improvements will be appreciated.
Steve Kearley wrote:
> Nicholas and Mark:
> Really appreciate your input on these automation tips and any more you care to provide ;)
> My familiarity with VBA for Excel is small, but I have had some success--Nicholas, I am very interested in understanding your use of a class for using multiple models. One of the problems I run into is that I need to run several models across a file but I need to append the exported summaries to the same Excel file. Do either of you have a code solution for appending the resulting multiple Excel files or is there a better way to accomplisy this? Does Monarch append to a txt file--so maybe it would be better to export to a .txt file then open it in Excel and run more procedures against that?
> Thanks for any help-
> Steve
>
> Nicholas Osdale wrote:
> > I've had to do the same thing, but instead of numbering my models, I placed them in a collection.
> > (I could have used an array, but the collection made it easier to add more models.) Also, I didn't include
> > the extension when I added the model name to the collection, but appended it to another variable,
> > that way I can use the name for the exported table name too, much the same way as you did.
> >
> > P.S. I don't know if you used just a snippet of your code, but in scanning through it, you might want
> > to include an error check to make sure the export completed successfully. The .ExportTable() and
> > .ExportSummary() both return a boolean stating if the operation was a success.[ There's a couple of other
> > notes about the code, but I don't want to come across with that "better than you" attitude. smile.gif ]
> >
> > Just my 2c worth!
> >
> > Nick Osdale-Popa
> >
>
Here is an example of VBA code ran from a master file that collects information from
1 of 25 print files ran through Monarch:
Set monarchobj = CreateObject("monarch32")
monarchobj.DisplayWindow ("0")
openfile = monarchobj.setreportfile("c:DOWNLOADstats01.PRN", False)
If openfile = True Then
openmod = monarchobj.setmodelfile("c:PROGRAM FILESmonarchmodelsSTATS - 01 - Acute Admits.mod")
If openmod = True Then
monarchobj.exporttable ("C:statsstats01.xls")
End If
End If
monarchobj.closealldocuments
AppActivate "Microsoft Excel"
Worksheets("01 - Admits").Activate
Workbooks.Open FileName:="C:statsstats01.xls"
Range("A1:d15").Select
Selection.Copy
Windows("Downloads.xls").Activate
Range("A1").Select
ActiveCell.PasteSpecial
Windows("stats01.xls").Activate
SendKeys "{esc}", True
ActiveWindow.Close
This code would proceed to another file to export and copied to a different sheet.
Any suggestions or improvements will be appreciated.
Steve Kearley wrote:
> Nicholas and Mark:
> Really appreciate your input on these automation tips and any more you care to provide ;)
> My familiarity with VBA for Excel is small, but I have had some success--Nicholas, I am very interested in understanding your use of a class for using multiple models. One of the problems I run into is that I need to run several models across a file but I need to append the exported summaries to the same Excel file. Do either of you have a code solution for appending the resulting multiple Excel files or is there a better way to accomplisy this? Does Monarch append to a txt file--so maybe it would be better to export to a .txt file then open it in Excel and run more procedures against that?
> Thanks for any help-
> Steve
>
> Nicholas Osdale wrote:
> > I've had to do the same thing, but instead of numbering my models, I placed them in a collection.
> > (I could have used an array, but the collection made it easier to add more models.) Also, I didn't include
> > the extension when I added the model name to the collection, but appended it to another variable,
> > that way I can use the name for the exported table name too, much the same way as you did.
> >
> > P.S. I don't know if you used just a snippet of your code, but in scanning through it, you might want
> > to include an error check to make sure the export completed successfully. The .ExportTable() and
> > .ExportSummary() both return a boolean stating if the operation was a success.[ There's a couple of other
> > notes about the code, but I don't want to come across with that "better than you" attitude. smile.gif ]
> >
> > Just my 2c worth!
> >
> > Nick Osdale-Popa
> >
>