Here's some more Excel examples:
'###########################################################################
######
' ---------------------------------------------------------------
' File: Excel.ebs
'
' Description:
' Uses OLE Automation to create an Excel workbook.
' A worksheet is filled with subsystem, module, and assigned
classes.
'
' Created 11/09/95 by BILLJ
'
' Entry Points: Main
'
' Created by Rational Software Corporation
' (C) Copyright Rational Software Corporation 1996 All Rights Reserved
'---------------------------------------------------------------------------
Sub CreateExcelSpreadsheet (theModel As Model)
' The following three objects are OLE Automation objects.
Dim ExcelApp As Object
Dim WorkBook As Object
Dim WorkSheet As Object
ModelName = GetAppName() '***
' If an error occurs then go to the Trap1 label
'On Error GoTo Trap1
' Create an instance of Excel
Set ExcelApp = CreateObject ("Excel.Application")
' Make the Excel instance visible
ExcelApp.Visible = TRUE
' Add a WorkBook to the Excel instance
Set WorkBook = ExcelApp.Application.Workbooks.Add
' Create a Modules sheet and populate it with module names
Set WorkSheet = WorkBook.WorkSheets.Add
Dim AllSubsystems As SubsystemCollection
Dim theClasses As ClassCollection
Dim theSubsystem As Subsystem
Dim theModule As Module
Dim theClass As Class
Set AllSubsystems = theModel.GetAllSubsystems ()
WorkSheet.Name = ModelName '*** RoseApp.Name
WorkSheet.Cells (1, 1).Value = "SubSystem"
WorkSheet.Cells (1, 2).Value = "Module"
WorkSheet.Cells (1, 3).Value = "Class"
WorkSheet.Cells (1, 4).Value = "Method"
Row = 2
For SubID = 1 To AllSubsystems.Count
Set theSubsystem = AllSubsystems.GetAt (SubID)
WorkSheet.Cells (Row, 1).Value = theSubsystem.Name
Row = Row + 1
For ModID = 1 To theSubsystem.Modules.Count
Set theModule = theSubsystem.Modules.GetAt (ModID)
WorkSheet.Cells (Row, 2).Value = theModule.Name
Row = Row + 1
Set theClasses = theModule.GetAssignedClasses ()
For ClsID = 1 To theClasses.Count
Set theClass = theClasses.GetAt (ClsID)
WorkSheet.Cells (Row, 3).Value =
theClass.Name
Row = Row + 1
Next ClsID
Next ModID
Next SubID
Exit Sub
Trap1:
MsgBox "Error!"
Exit Sub
End Sub
Sub Main
Call CreateExcelSpreadsheet (RoseApp.CurrentModel)
End Sub
Function GetAppName ' *** add this entire function to return app name
Dim windowTitle As String
Dim firstIndex As Integer
Dim lastIndex As Integer
Dim length As Integer
Dim modelName As String
windowTitle = AppGetActive$()
firstIndex = InStr(windowTitle,"-") + 2
lastIndex = InStr(firstIndex, windowTitle,"-") - 2
length = lastIndex - firstIndex + 1
modelName = Mid(windowTitle, firstIndex, length)
GetAppName = modelName
End Function
'###########################################################################
######
'This sample script creates an Excel spreadsheet of all the C++ properties
by class,
'name, type and default values. It autoformats the sheet afterwards
'Tested with Excel 97 and Rose 4.5.8163.3
'Patrick Rutledge, 2/99
Dim ExcelApp As Object
Dim WorkBook As Object
Dim WorkSheet As Object
Dim currCol As Integer 'global column counter
'takes a collection of properties, the column name
Sub AddPropsColToSheet (inProps As PropertyCollection, inName As String)
If Worksheet Is Nothing Then
CreateExcelWorkSheet
End If
midCol% = (3 * currCol%) -1
WorkSheet.Cells (1, midCol%).Value = inName
row% = 2
Dim aProp As Property
For i% = 1 To inProps.Count
Set aProp = inProps.GetAt(i%)
WorkSheet.Cells(row%, midCol% - 1) = aProp.Name
WorkSheet.Cells(row%, midCol%) = aProp.Type
WorkSheet.Cells(row%, midCol% + 1) = aProp.Value
row% = row% + 1
Next i%
currCol% = currCol% + 1
End Sub
Sub CreateExcelWorkSheet
' If an error occurs then go to the Trap1 label
On Error GoTo Trap1
' Create an instance of Excel
Set ExcelApp = CreateObject ("Excel.Application")
' Make the Excel instance visible
ExcelApp.Visible = TRUE
' Add a WorkBook to the Excel instance
Set WorkBook = ExcelApp.Application.Workbooks.Add
' Create a Modules sheet and populate it with module names
Set WorkSheet = WorkBook.WorkSheets.Add
WorkSheet.Name = "Rose C++ Properties"
Exit Sub
Trap1:
MsgBox "error creating spreadsheet"
End Sub
Sub Main
Dim defProps As DefaultModelProperties
Dim theProps As PropertyCollection
currCol = 1'initialize column counter to 1
Set defProps = RoseApp.CurrentModel.DefaultProperties
Set theProps = defProps.GetDefaultPropertySet("Class", "cg",
"default")
AddPropsColToSheet theProps, "Class"
Set theProps = defProps.GetDefaultPropertySet("Operation", "cg",
"default")
AddPropsColToSheet theProps, "Operation"
Set theProps = defProps.GetDefaultPropertySet("Attribute", "cg",
"default")
AddPropsColToSheet theProps, "Attribute"
Set theProps = defProps.GetDefaultPropertySet("Category", "cg",
"default")
AddPropsColToSheet theProps, "Category"
Set theProps = defProps.GetDefaultPropertySet("Subsystem", "cg",
"default")
AddPropsColToSheet theProps, "Subsystem"
Set theProps = defProps.GetDefaultPropertySet("Module-Spec", "cg",
"default")
AddPropsColToSheet theProps, "Module-Spec"
Set theProps = defProps.GetDefaultPropertySet("Module-Body", "cg",
"default")
AddPropsColToSheet theProps, "Module-Body"
Set theProps = defProps.GetDefaultPropertySet("Project", "cg",
"default")
AddPropsColToSheet theProps, "Project"
Set theProps = defProps.GetDefaultPropertySet("Role", "cg",
"default")
AddPropsColToSheet theProps, "Role"
Set theProps = defProps.GetDefaultPropertySet("Association", "cg",
"default")
AddPropsColToSheet theProps, "Association"
Set theProps = defProps.GetDefaultPropertySet("Uses", "cg",
"default")
AddPropsColToSheet theProps, "Uses"
Set theProps = defProps.GetDefaultPropertySet("Inherit", "cg",
"default")
AddPropsColToSheet theProps, "Inherit"
If Worksheet Is Not Nothing Then
WorkSheet.UsedRange.Autoformat
End If
'release references
Set ExcelApp = Nothing
Set WorkBook = Nothing
Set WorkSheet = Nothing
End Sub
'###########################################################################
######
I know a couple of ways to read data from an Excel sheet.
Here a couple code snippets for reading data, but how you would
like to do with the retrieved data (e.g. build some Rose model)
would, of course, require your knowledge of RoseScripting:
1. Using ADODB and SQL queries, which allows multiple coonnections to a
single XSL file:
Example:
Set cnnExcel = CreateObject("ADODB.Connection")
cnnExcel.Open "DBQ=C:\Temp\myXSL.xls;DRIVER={Microsoft Excel Driver
(*.xls)};"
:
Set rstExl = cnnExcel.Execute("SELECT * FROM MyNamedRange WHERE
Name='Shaquille';")
iCols = rstExl.Fields.Count
For I = 0 To iCols - 1
ViewPort.Print rstExl.Fields.Item(I).Name
Next 'I
rstExl.MoveFirst
' Loop through the data rows showing data in viewport.
Do While Not rstExcel.EOF
For I = 0 To iCols - 1
ViewPort.Print rstExl.Fields.Item(I).Value
Next 'I
rstExl.MoveNext
Loop
2. Using Excel's own Object-Model for a more complete set of calls for
data-retrieval
(e.g. PivotTables, etc.), but doesn't allow multiple connections to a single
XSL file.
Check out Microsoft's MSDN site for a complete object-model for Excel (97,
2002, XP).
Example:
Dim XL As Object
'Set XL = GetObject(, "Excel.Application")
Set XL = GetObject("C:\Temp\myXSL.xls")
XL.Application.Visible = False
XL.Parent.Windows(1).Visible = True
Dim SheetsCol As Object
Dim PivTablesCol As Object
Dim someValue As String
With XL
.Application.DisplayAlerts = False 'recommended for
automation calls
Set SheetsCol = .Sheets
Set PivTablesCol = SheetsCol(1).PivotTables
someValue =
PivTablesCol(1).PivotFields("Name").PivotItems(1).Name
:
End With
:
'###########################################################################
######
-----Original Message-----
From: Kennedy, Patrick [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 18, 2003 2:26 PM
To: 'Ronald W Townsen'; [EMAIL PROTECTED]
Subject: RE: (ROSE) Rose Scripting and MS Access97
Look in the Rose script sample directory. There is a script to
read info from Rose into Access (not sure what version). I also
believe I might have some examples stashed on my home machine, which if
I find I do, I will foward.
Patrick Kennedy
Rational Support
-----Original Message-----
From: Ronald W Townsen [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 18, 2003 7:59 AM
To: [EMAIL PROTECTED]
Subject: (ROSE) Rose Scripting and MS Access97
Does anyone have some example Rose Scripting Code showing how to work with
Acess97?
Create/Update/Query?
************************************************************************
* Rose Forum is a public venue for ideas and discussions.
* For technical support, visit http://www.rational.com/support
* Only plain-text messages are supported.
* HTML or Rich-Text messages may be rejected.
*
* Post or Reply to: [EMAIL PROTECTED]
* Subscription Requests: [EMAIL PROTECTED]
* Archive of messages:
* http://www.rational.com/support/usergroups/rose/rose_forum.jsp
* Other Requests: [EMAIL PROTECTED]
*
* To unsubscribe from the list, please send email
* To: [EMAIL PROTECTED]
* Subject: <BLANK>
* Body: unsubscribe rose_forum
*************************************************************************
************************************************************************
* Rose Forum is a public venue for ideas and discussions.
* For technical support, visit http://www.rational.com/support
* Only plain-text messages are supported.
* HTML or Rich-Text messages may be rejected.
*
* Post or Reply to: [EMAIL PROTECTED]
* Subscription Requests: [EMAIL PROTECTED]
* Archive of messages:
* http://www.rational.com/support/usergroups/rose/rose_forum.jsp
* Other Requests: [EMAIL PROTECTED]
*
* To unsubscribe from the list, please send email
* To: [EMAIL PROTECTED]
* Subject: <BLANK>
* Body: unsubscribe rose_forum
*************************************************************************
************************************************************************
* Rose Forum is a public venue for ideas and discussions.
* For technical support, visit http://www.rational.com/support
* Only plain-text messages are supported.
* HTML or Rich-Text messages may be rejected.
*
* Post or Reply to: [EMAIL PROTECTED]
* Subscription Requests: [EMAIL PROTECTED]
* Archive of messages:
* http://www.rational.com/support/usergroups/rose/rose_forum.jsp
* Other Requests: [EMAIL PROTECTED]
*
* To unsubscribe from the list, please send email
* To: [EMAIL PROTECTED]
* Subject: <BLANK>
* Body: unsubscribe rose_forum
*************************************************************************
