Export Multiple Files

[Ron Raes]

A Monarch 6 pdf file stated that from the summary, one could export multiple excel files or one excel file with multiple worksheets. I was able to export one excel file with multiple worksheets but I can't figure out how to export multiple files, one for each summary. Do you know how this works?

[Tom Whiteside]

Hi, Ron!

I think you are looking at the same .pdf file that I am looking at, the Monarch V6 Upgrade Brochure, topic 4, on page 3. What it actually says, is, "... Monarch V6 now lets you export all of your summary views at the same time, either as separate files in a desired folder, or as multiple spreadsheet pages or databases within a single file!"

Unfortunately, the options do not include separate Excel spreadsheet files in a desired folder. What actually happens is that your choice of export file format for the summaries, i.e., the file extension, determines whether you may export as multiple files within a folder, or, as multiple sheets/tables within a file.

The file extensions that will produce multiple sheets/tables within one file are: mdb, xls, and wk3. All the other listed file extensions, db, dbf, htm, txt, wk1, wks, and xml, will produce multiple files within a folder. Note that the xml option is labeled "Excel 2002 XML Spreadsheet." As I have not yet been able to successfully setup the Office XP Web Components, I cannot tell you how the xml option looks as output.

Looking through the Monarch Knowledge Base and Archive, several postings were made before Monarch V6 came out, that used VBA code to export a sequence of summaries to either a single Excel file, or to multiple Excel files.

Could either Nick Osdale-Popa, or Gareth help out here? They were the two most frequent code contributors.

Anyway, sorry not to have a more cheerful answer. Other people have misread that same feature/benefit point. Hopefully, you can get some good code out of this!

Best wishes!

Tom Whiteside
whitesidetom@yahoo.com

[Stephen Reid]

Hi, try the following code. You will need to change the txt and mod files to your own and also the export directory.

Sub Export_Routing()

Dim Monarchobj As Object ' Define the Object
Dim openfile As Boolean ' True/False variable used for opening report
Dim SummaryCount As Integer ' Holds the # of Summaries in the model
Dim LoopCounter As Integer ' Variable used in For loop

Set Monarchobj = CreateObject("Monarch32") 'Create the object


openfile = Monarchobj.setreportfile("J:/cs.txt", False) 'Open the report file
If openfile = False Then
MsgBox ("Unable to open Report!")
End
End If

openfile = Monarchobj.setmodelfile("S:steve_rmonarchModels
outing.mod") ' Open the model file
If openfile = 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 ("S:steve_rTemp
oute" & Monarchobj.GetSummaryNameAt(LoopCounter) & ".XLS") 'Export the Summary
Next LoopCounter
Else
MsgBox ("Unable to open Model File")
End
End If
Monarchobj.closealldocuments
Monarchobj.Exit
Set Monarchobj = Nothing


End Sub

Hope this helps. Sorry the code isnt laid out correctly but I couldn't get tabs to work.

[Nick Osdale-Popa]

That looks like it should accomplish what Ron needs. Stephen, if you start and end your code snippets with
you'll get better formatting of your code. (blank lines seem to get dropped, though :( )

[Stephen Reid]

Just re-posting my code with Nick's tip.

Thanks for that Nick, I appreciate it.
smile.gif


Sub Export_Routing()

Dim Monarchobj As Object ' Define the Object
Dim openfile As Boolean ' True/False variable used for opening report
Dim SummaryCount As Integer ' Holds the # of Summaries in the model
Dim LoopCounter As Integer ' Variable used in For loop

Set Monarchobj = CreateObject("Monarch32") 'Create the object

openfile = Monarchobj.setreportfile("J:/cs.txt", False) 'Open the report file
If openfile = False Then
MsgBox ("Unable to open Report!")
End
End If

openfile = Monarchobj.setmodelfile("S:steve_rmonarchModels
outing.mod") ' Open the model file
If openfile = 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 ("S:steve_rTemp
oute" & Monarchobj.GetSummaryNameAt(LoopCounter) & ".XLS") 'Export the Summary
Next LoopCounter
Else
MsgBox ("Unable to open Model File")
End
End If
Monarchobj.closealldocuments
Monarchobj.Exit
Set Monarchobj = Nothing

End Sub

[Brian Hammer]

Hi guys!

That code tip is great, but I already knew how to do that one. My question is how do I go about putting the summaries into the same workbook, but separate worksheets?

I have version 4, so I cannot use the JetExportSummary utility. What I want to do is export 3 summaries into 3 separate worksheets in an Excel file. I know how to do it by using VBA code within an Excel Macro and importing the data into the current Excel file, but I'd like to do it from VB. I'd like a VB app that will use Monarch's abilities to create one Excel file containing these 3 worksheets.

Is this possible? If not I can go ahead and put my code into an Excel Macro and do it that way, but I really don't want to, if I don't have to. Thanks for any help you guys can provide!