Scripting Aurora from Microsoft Excel
NOTE: Energy Exemplar recommends using Aurora's internal scripting form.
To control Aurora from Excel (or other applications) using VBA, the following commands are required:
Dim AURORACommand As Object
Dim AC As Object
Dim AURORADataset As Object
Dim ADS As Object
Set AC = CreateObject("AURORAserver.comApplication")
Set ADS = CreateObject("AURORAserver.comDataset")
Set DataType = CreateObject("AURORAserver.ScriptingDataType")
Set ProjectDSTableType = CreateObject("AURORAserver.ProjectType")
AC.InitializeAurora
These commands designate Aurora as a controllable application and allow for communication through Excel. Once these have run, all AURORA Commands and AURORA Datasets will be accessible through Excel.
The InitializeAurora AURORA Command shown above is used to launch the application. Since Aurora is not visible when accessed through this method, it is a good practice to utilize the OpenStatus argument for the LoadProject AURORA Commands.
Refer to the following example:
Dim OpenStatus as Long
AC.LoadProject "C:\Aurora\WECC Default.apz", OpenStatus
This will return one of three values:
1 = Open OK
2 = Open Error
3 = Open With Missing DB
Aurora References in Excel
When you run Aurora from Excel, you must make sure Excel has the correct references to Aurora dlls, otherwise the above code will not work. Register two Aurora dlls using the method below and create the .tlb files for Excel to reference. The example below is using Aurora 14.0.
If using version 13.4 or earlier, use the dlls located here:
C:\Program Files (x86)\AURORA\AURORA\AURORAMessaging.dll
C:\Program Files (x86)\AURORA\ AURORA\AURORAServer.dll
If using version 13.5 or 14.0, use the dlls located here, (note that the Aurora Version would be "Aurora 13.5" or Aurora 14.0" depending on what is installed on your machine):
C:\Program Files\Energy Exemplar\[Aurora Version]\AURORAMessaging.dll
C:\Program Files\Energy Exemplar\[Aurora Version\AURORAServer.dll
If using version 14.1 or above, use the dlls located here, (note that the dll names have changed slightly and that the Aurora Version would be "Aurora 14.1" or "Aurora 14.2" depending on what is installed on your machine):
C:\Program Files\Energy Exemplar\[Aurora Version]\EnergyExemplar.Aurora.Messaging.dll
C:\Program Files\Energy Exemplar\[Aurora Version]\EnergyExemplar.Aurora.Server.dll.
Example to show how to register dlls for Excel to reference:
- Open a Command Prompt in Administrator mode.
- If you have a 64 bit version of Excel, paste this into the command line:
cd C:\Windows\Microsoft.NET\Framework64\v4.0.30319
*Make sure you are using the 64-bit version of Excel, if you have a 32-bit version of Excel, type in " type in “cd C:\Windows\Microsoft.NET\Framework\v4.0.30319”. If you are using a different version of Microsoft Framework than v4.0.30319, then go to that folder instead. - Paste this into the command line (replacing [Aurora Version] with the version of Aurora installed on your machine, the image below references version Aurora 14.1):
regasm "C:\Program Files\Energy Exemplar\[Aurora Version]\AURORAMessaging.dll" /codebase /verbose /tlb”.
This will create a .tlb file for Excel to use from the AURORAMessaging.dll file.
- Paste this into the command line (replacing [Aurora Version] with the version of Aurora installed on your machine. The picture references Aurora 14.1):
regasm "C:\Program Files\Energy Exemplar\[Aurora Version]\AURORAServer.dll" /codebase /verbose /tlb”.
This will create a .tlb file for Excel to use from the AURORAServer.dll file.
- In Excel, open the Visual Basic scripting window, then go to Tools -> References and select Browse and select the .tlb files in the "C:\Program Files\Energy Exemplar\[Aurora Version]" folder.
- Then select “AURORA XMP Messaging and Interface class” and “AURORA XMP server and computation engine” as references in your project. The file path for these references should be the .tlb file created in “C:\Program Files\Energy Exemplar\Aurora 14.0”
Make sure the file path for the references you are seeing are in the correct folder, in the example picture below it is in the Aurora folder.
NOTE: If using the 32 bit version of Excel, use the regasm.exe located at: C:\Windows\Microsoft.NET\ Framework\v4.0.30319. By default, the 64 bit version of regasm.exe located in the “Framework64” folder will only register the dlls in the 64 bit registry. If you have a 32 bit version of regasm.exe to register the dlls in the 32 bit registry, type in “cd C:\Windows\Microsoft.NET\Framework\v4.0.30319”.
Troubleshooting Error Messages
Click on the following for more information:
Error: 5 was generated by mscorlibError: 5 was generated by mscorlib
Error: 5 was generated by mscorlib
Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.
at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
at System.Reflection.RuntimeAssembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection, Boolean suppressSecurityChecks)
at System.AppDomain.Load(AssemblyName assemblyRef)
at EPIS.Aurora.Common.AssemblyLoader.LoadByVersion(String myAssembly, Boolean isManaged)
at EPIS.Aurora.Model.DataAccess.ProjectServer.InitializeAurora()
Please review your Message.Log file (location is defined in the Status window when Aurora first loads). If you have the message above, please contact Support. You will need to obtain and install an additional file in the same location as the EXCEL.EXE file (typically C:\Program Files (x86)\Microsoft Office\Office14 or similar).
Scripting
