Importing Component Stream Data From Hysys To Excel Using Vba

Hi everyone,

I've just started learning to use VBA to import and organise data from HYSYS and would be grateful if someone could advise me on this.

Whilst I'm able to import values like MassFlow, Temperature and Pressure in the correct units using the GetValue method, I can't seem to do the same for ComponentMassFlow.

Option Explicit
Public hyApp As HYSYS.Application
Public simCase As SimulationCase
Option Compare Text

Public Sub StartHYSYS()
Dim fileName As String
Dim i As Integer
Dim FeedGas As ProcessStream
Dim Com As Component
Dim List As Components

' LOADING HYSYS SIMULATION FILE
Set hyApp = CreateObject("HYSYS.Application")
hyApp.Visible = True

Set simCase = hyApp.ActiveDocument
    If simCase Is Nothing Then
        fileName = Worksheets("Import").Range("B1").Text
        Set simCase = GetObject(fileName, "HYSYS.SimulationCase")
        simCase.Visible = True
    End If
    
'Clear sheet
Worksheets("Import").Range("A2:AZ100").Clearcontents

'Connect Feed stream
Set FeedGas = simCase.Flowsheet.MaterialStreams.Item("Feed gas")
Worksheets("Import").Range("B3").Value = FeedGas.Name

'Load Components

Set List = simCase.Flowsheet.FluidPackage.Components

i = 0
    For Each Com In List
    i = i + 1
        Worksheets("Import").Range("A" & i + 3).Value = Com.Name              
        Worksheets("Import").Range("B" & i + 3).Value = FeedGas.ComponentMassFlow(i - 1).GetValues("kg/h")
    Next Com

'Total Stream Mass Flow

Worksheets("Import").Range("b49").Value = FeedGas.MassFlow.GetValue("kg/h")  


End Sub

Doing this gives me the Run-time error '424' (Object required)

I've tried to get around this by Setting another variable to the individual components in ComponentMassFlow first, but it appears this isn't recognised as an array?

*The Method for ComponentMassFlow is "GetValues" rather than "GetValue" as it is for MassFlow - according to the HYSYS type library
I'm guessing this has something to do with the fact that ComponentMassFlow is a RealFlexVariable whilst MassFlow is a RealVariable.

Without the GetValues method, it currently returns the whole list of component mass flows in kg/s.

If I remove "(i-1)" from after ComponentMassFlow, it does appear to return the first component in kg/h.
Could someone please advise me as to how I might program it to return the list of component mass flows in a specified unit?

*If anyone else is in my position and is looking for resources to get them started (in addition to reading the "HYSYS Customization Guide"):
http://www.easyexcelvba.com
https://www.cheresou...xport-to-excel/
https://www.cheresou...hysys-to-excel/
https://www.cheresou...mto-hysysexcel/

http://hysyssimulations.blogspot.com/2011/06/how-to-link-hysys-simulation-to-excel.html

There are probably examples at the AspenTech support site. The answer, though, is that you must first initialize an array to hold the values.

Bobby

I didn't give you the correct answer. The above step is required if you want to move component values from Excel to Hysys. Getting values from Hysys is quite simple. See the attached code.

To get started with VBA, get a book by John Walkenbach on Excel VBA. You want to be sure to get a solid start. So, learn from the professional.

Bobby

Edited by Bobby Strain, 19 August 2018 - 11:03 AM.

Hi Bobby,

Thank you for your response. I'm actually already familiar with pulling most key values from HYSYS and importing them to Excel and the code I wrote above already does this. My issue was that I could not specify the unit in which the values for ComponentMassFlow were obtained - as I am able to do for MassFlow using the GetValue method. I'll try initialising an array and applying GetValues to that instead.

Thanks,
Tim

Tim,

     You can visit the AspenTech support site and find an Excel workbook called Stream Reporter. It contains all you will ever need to extract almost everything from HYSYS. I'm not sure whether you need an array for what you are doing. Maybe you can find variables that are ComponentMassFlow in Stream Reporter. You can always extract mass fractions multiplied by the total stream flow. Let me know how you resolve the issue. ComponentMassFlow is available only in the units you have specified for the simulation.

   HYSYS has many items that are not available through normal means. But HYSYS provides backdoor variables that you can extract.

Bobby

Edited by Bobby Strain, 21 August 2018 - 01:56 PM.

Finally got back around to looking at this and I've managed to figure it out. The HYSYS Stream Reporter code is very long and complicated but I've identified the bits that will do what I was looking for (without checking for multiple component lists in multiple fluid packages).

Variable "transfer" arrays are used for HYSYS to dump data to - it must be treated as an array for the GetValues function to work. From there you can print out the array as usual.

For anyone who might find it useful, here's my code:

Option Explicit
Public hyApp As HYSYS.Application
Public simCase As SimulationCase
Option Compare Text

Sub TestCode()

    Dim S1 As Worksheet
    Dim S4 As Worksheet
    Dim fileName As String
    Dim Units As String
    Dim hySheet As Flowsheet
    Dim hyStreams As Streams
    Dim TransferVar As Variant
    Dim StreamObj As ProcessStream
    Dim CompFlow() As Double
    Dim Counter As Integer
    Dim Ubnd As Integer
    Dim hyFP As HYSYS.FluidPackage
    Dim hyStream As ProcessStream
    Dim AllCptNames() As String
    Dim NumCpts As Integer
    Dim CounterCpts As Integer
    
    Set S1 = Worksheets("Import")
    Set S4 = Worksheets("Test")
    
    ' LOADING HYSYS SIMULATION FILE
    Set hyApp = CreateObject("HYSYS.Application")
    hyApp.Visible = True
    
    Set simCase = hyApp.ActiveDocument
        If simCase Is Nothing Then
            fileName = S1.Range("B1").Text
            Set simCase = GetObject(fileName, "HYSYS.SimulationCase")
            simCase.Visible = True
        End If
    
    Set hySheet = simCase.Flowsheet
    Set hyStreams = hySheet.MaterialStreams
    Set hyFP = hySheet.FluidPackage
    
    NumCpts = hyFP.Components.Count
    TransferVar = hyFP.Components.Names
    ReDim AllCptNames(0 To NumCpts - 1)
    
    For CounterCpts = 0 To NumCpts - 1
        AllCptNames(CounterCpts) = TransferVar(CounterCpts)
    Next 'CounterCpts
    Erase TransferVar

        For CounterCpts = 0 To NumCpts - 1
            S4.Range("A4").Offset(CounterCpts, 0).Value = AllCptNames(CounterCpts)
        Next 'CounterCpts
    
    Set StreamObj = hyStreams.Item("Feed gas")
    Units = "kg/h"
    TransferVar = StreamObj.ComponentMassFlow.GetValues(Units)
    Ubnd = UBound(TransferVar)
    ReDim RetValArray(0 To Ubnd)
    
    For Counter = 0 To Ubnd
        RetValArray(Counter) = TransferVar(Counter)
    Next

    Erase TransferVar
    
        For Counter = 0 To Ubnd
            S4.Range("B4").Offset(Counter, 0).Value = RetValArray(Counter)
        Next 'Counter
    
End Sub


 

Tim,

     I would have opted for the easy solution. Get weight fraction for each component and multiply by total flow. This should not require any arrays. I haven't worked with HYSYS since 2.4. But I doubt that much has changed to the structure for automation. There are lots of goodies in Stream Reporter. I use the search function to find what I need. When developing material balances for a client PFD, I would never present component values in any form other than % or fraction. If you give them values in component mass flow, they will spend countless hours to see if they all balance. Even if you generate them from your simulation.

   I have an application that I have written with VB.NET. It works fine with earlier HYSYS. Would you be willing to check if it works with your version of HYSYS? It's a simple application that uses HYSYS to perform flash calculation for an HEM module. No installation is required.

Bobby

Hi Bobby,

In hindsight that probably would have been better. Right now I'm displaying values in terms of mass flow since the total stream mass flow changes substantially at different stages.

Sure I could check to see if it's compatible.

By the way, would you know how to set individual component mass fraction values? Haven't found anything helpful in the HYSYS Customisation guide or on the HYSYS Stream reporter. Having issues getting the "SetValues" method to work for individual components.

Here is some code that will do what you are asking. Just change componentmolarfraction to componentmassfraction. You must change the HYSYS reference because the code uses early binding.

The attached file is not the one that has read from Excel. But it should suffice. If not, let me know and I will find a better example.

Bobby

Edited by Bobby Strain, 28 November 2018 - 11:35 AM.

Oh wow, I didn't realise it was that simple. I thought you had to use SetValues.

Thanks for the help

For anyone else with the same issue:
TransferVar = FeedGas.ComponentMassFraction.Values
For i = 0 To Ubnd
    TransferVar(i) = S3.Range("L4").Offset(i, 0).Value
Next
FeedGas.ComponentMassFraction.Values = TransferVar
-where TransferVar is a variant

Don't quite get why the first line is necessary, but seems to not work without it even if I ReDim the array to the appropriate size.
Note: At the beginning of my module I turned off solver with "simCase.Solver.CanSolve = False" but found it necessary to leave it off at the end cause it meant Excel wouldn't End Sub without waiting for HYSYS to converge (whilst repeatedly letting me know it can't communicate with HYSYS)

Tim,

     All things are simple once you understand them. I think I got this solution from the AspenTech support  site quite a while back. They have lots of useful stuff there. And they will help if you email them for something that you can't find on the site. I'll post the application soon in this thread for you to test.

Bobby

Tim,

       Here is the app for testing. Let me know if it works for you.

Bobby

 Pressure Safety Valve HEM Calculation.zip   40.89KB   42 downloads

It's responding, but frankly I'm not sure how I'd verify the values it's putting out - I'm not familiar with the HEM and calculations associated with PSVs They don't seem to change when I change outlet pressure.

you can easily compare with different Excel sheets available at cheresources,

see for example

http://www.cheresour...ng-temperature/

the procedure is simple and you easily compare the code required by different tools (i.e. process simulator vs. thermodynamic  library)

Edited by PaoloPemi, 03 December 2018 - 10:52 AM.

Tim,

   Thank you for your effort and your feedback. I am not concerned about comparing the results with some other model. It has been thoroughly tested. But, like any application from an unknown source, the user has to validate it. That it works with your version of HYSYS confirms it's still functional. The problem with Paolo's suggestion is that whatever spreadsheet you use to compare, the validity of the spreadsheet is also unknown. I would never use a speadsheet from an unknown (or a known source). In fact, I only use spreadsheets that I have developed. And the code is primarily VBA. Spreadsheets with the usual cell formulas are dangerous, even for the developer.

Bobby

Edited by Bobby Strain, 03 December 2018 - 11:45 PM.