Hello,
after performing the yearly calculation I want to read the monitor channel with VBA and put the data into the spreadsheet. The following works but is sloooow:
MyMonitors.Name = "MonP01P"
For k = 0 To 525599
j = k + 2
ActiveSheet.Cells(j, 2).Value = MyMonitors.Channel(1)(k)
Next k
fills all cells of the Range with the same value, i.e. the first value of the monitor channel.
Trying
Dim monarr(525600) As Double
monarr = MyMonitors.Channel(1)
ActiveSheet.Range(Cells(2, 6), Cells(525601, 6)).Value = monarr
results in a VBA "error during compilation: Assignment to array not possible". Same error if monarr is declared as Variant.
Any idea how to transfer the monitor channel in a fast way, other than exporting the monitor data to file and reading that file to Excel?
P.S. in Excel's object catalogue, the Monitors.Channel object is described as
Property Channel(Index As Long). Read only.
Variant array of doubles for the specified channel (usage: MyArray = DSSMonitor.Channel(i))
but as I wrote above the assignment of the monitor channel to a VBA array results in an error.
Last edit: Wilfried Hennings 2015-05-19
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
That will be very slow! Each time you access Channel(i)(k) you will go back across the interface and make a new Variant array from which you extract the k-th element only. So that's a lot of extra work that is not necessary.
The monarr.. approach will not work because VBA is expecting a Variant not an array of Double.
Try this. It should be a lot faster.
Dim MyVariantArray as Variant
....
MyMonitors.Name = "MonP01P"
MyVariantArray = MyMonitors.Channel(1) ' Get Channel 1 array
For j=LBound(MyVariantArray) to UBound(MyVariantArray)
ActiveSheet.Cells(j+1, 2).Value = MyVariantArray(j)
Next k
This will still take a little bit if you are trying to move 500,000 points. But it should be a lot faster than what you were doing
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thank you very much!
The clue was to NOT specify the dimension of MyVariantArray (or, in my code, monarr).
Transfer of 525600 values takes about one minute.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi there, I wanted to know if it is possible to use Excel VBA to change variables in my .dss script?
For example, I want to increment the size of my PV system and export the results after each increment, can I write a code in VBA to increment the PV system kVA rating?
Or is it possible to comment or uncomment lines in my .dss script through Excel VBA?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
You can do everything that it is possible to do through the COM interface with Excel VBA. Since both are Microsoft, it actually works surprisingly well. It is easy to increment the kVA ratings and do another solution as well as collect result as you go. It is generally easy to change any number.
I wouldn't attempt to alter a dss script with Excel VBA. It is better to just change the definition the line of script was doing. You can execute any text command at almost any time just like you would from the normal interface. And you have the Enable/Disable functions if you want to add or delete device definitions.
😄
1
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi, I am struggling to get the correct results from my code. See below:
I am trying to run a loop that increments the PV rating by 2kVA, using the DSSText.Command = "BatchEdit.."
However, the values being exported are only for the first PV case of 2 kVA, the PV ratings are not incrementing according to the counter in my loop? I have checked the counter using a watch and the counter 'n' is incrementing but the Open DSS solution is not changing according to the counter?
Can someone please point out where I am going wrong with this?
Public Sub Voltages()
' Execute the loop to export the voltage in the range 0-20kVA for each control scheme at line 7
' Dim DSSText As OpenDSSengine.Text
Dim VLine7File As String
Dim wkb1, wkb2 As Workbook 'Wkb1 is for the results, wkb2 is for the .dss exported results
Dim sht1, sht2 As Worksheet
Dim ResultsTargetFile As String
ResultsTargetFile = Sheets("Main").range("C10")
'START THE LOOP FOR PENETRATION RANGE
Dim n As Integer
n = 0
Do Until n = 10
n = n + 1
DSSText.Command = PenetrationArray(n) 'change the penetration and solve
DSSSolution.Solve
DSSText.Command = "Export monitors line7_v"
VLine7File = DSSText.Result
Have you tried by reading the monitors directly from COM instead of exporting the content into a text file? You see, you may be overwriting the file or, trying to write an open file, which of course is not allowed by the OS.
To learn more on how to read monitors using COM, use this command from your script:
DSSText.Command = "COMHelp"
There you'll find examples and the explanation for all the properties and methods included in COM.
Best regards
Davis
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hello,
after performing the yearly calculation I want to read the monitor channel with VBA and put the data into the spreadsheet. The following works but is sloooow:
MyMonitors.Name = "MonP01P" For k = 0 To 525599 j = k + 2 ActiveSheet.Cells(j, 2).Value = MyMonitors.Channel(1)(k) Next kTrying
fills all cells of the Range with the same value, i.e. the first value of the monitor channel.
Trying
results in a VBA "error during compilation: Assignment to array not possible". Same error if monarr is declared as Variant.
Any idea how to transfer the monitor channel in a fast way, other than exporting the monitor data to file and reading that file to Excel?
P.S. in Excel's object catalogue, the Monitors.Channel object is described as
Property Channel(Index As Long). Read only.
Variant array of doubles for the specified channel (usage: MyArray = DSSMonitor.Channel(i))
but as I wrote above the assignment of the monitor channel to a VBA array results in an error.
Last edit: Wilfried Hennings 2015-05-19
That will be very slow! Each time you access Channel(i)(k) you will go back across the interface and make a new Variant array from which you extract the k-th element only. So that's a lot of extra work that is not necessary.
The monarr.. approach will not work because VBA is expecting a Variant not an array of Double.
Try this. It should be a lot faster.
Dim MyVariantArray as Variant
....
MyMonitors.Name = "MonP01P"
MyVariantArray = MyMonitors.Channel(1) ' Get Channel 1 array
For j=LBound(MyVariantArray) to UBound(MyVariantArray)
ActiveSheet.Cells(j+1, 2).Value = MyVariantArray(j)
Next k
This will still take a little bit if you are trying to move 500,000 points. But it should be a lot faster than what you were doing
Thank you very much!
The clue was to NOT specify the dimension of MyVariantArray (or, in my code, monarr).
Transfer of 525600 values takes about one minute.
Hi there, I wanted to know if it is possible to use Excel VBA to change variables in my .dss script?
For example, I want to increment the size of my PV system and export the results after each increment, can I write a code in VBA to increment the PV system kVA rating?
Or is it possible to comment or uncomment lines in my .dss script through Excel VBA?
Hello,
Sure, check this out:
https://sourceforge.net/p/electricdss/code/HEAD/tree/trunk/Version8/Distrib/Examples/Excel/
Best regards
Davis
Thank you Davis, which file are you referring to?
You can do everything that it is possible to do through the COM interface with Excel VBA. Since both are Microsoft, it actually works surprisingly well. It is easy to increment the kVA ratings and do another solution as well as collect result as you go. It is generally easy to change any number.
I wouldn't attempt to alter a dss script with Excel VBA. It is better to just change the definition the line of script was doing. You can execute any text command at almost any time just like you would from the normal interface. And you have the Enable/Disable functions if you want to add or delete device definitions.
Hi, I am struggling to get the correct results from my code. See below:
I am trying to run a loop that increments the PV rating by 2kVA, using the DSSText.Command = "BatchEdit.."
However, the values being exported are only for the first PV case of 2 kVA, the PV ratings are not incrementing according to the counter in my loop? I have checked the counter using a watch and the counter 'n' is incrementing but the Open DSS solution is not changing according to the counter?
Can someone please point out where I am going wrong with this?
Public Sub Voltages()
' Execute the loop to export the voltage in the range 0-20kVA for each control scheme at line 7
' Dim DSSText As OpenDSSengine.Text
Dim VLine7File As String
Dim wkb1, wkb2 As Workbook 'Wkb1 is for the results, wkb2 is for the .dss exported results
Dim sht1, sht2 As Worksheet
Dim ResultsTargetFile As String
ResultsTargetFile = Sheets("Main").range("C10")
'START THE LOOP FOR PENETRATION RANGE
Dim n As Integer
n = 0
Do Until n = 10
n = n + 1
DSSText.Command = PenetrationArray(n) 'change the penetration and solve
DSSSolution.Solve
DSSText.Command = "Export monitors line7_v"
VLine7File = DSSText.Result
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Set wkb2 = ThisWorkbook
Set wkb1 = Workbooks.Open(ResultsTargetFile)
Set wkb2 = Workbooks.Open(VLine7File)
Set sht2 = wkb2.Sheets("Grid_Mon_line7_v_1")
Set sht1 = wkb1.Sheets("Voltage profile")
sht2.range("C2:C25").Copy
'Dim iRowOffset As Integer: iRowOffset = 2
Dim iColumnOffset As Integer: iColumnOffset = n
'sht1.range("A2:A25").Offset(RowOffset:=iRowOffset, ColumnOffset:=iColumnOffset).PasteSpecial xlPasteValues
sht1.range("A2:A25").Offset(ColumnOffset:=iColumnOffset).PasteSpecial xlPasteValues
Application.CutCopyMode = False
wkb2.Close True
Loop
End
Hello,
Have you tried by reading the monitors directly from COM instead of exporting the content into a text file? You see, you may be overwriting the file or, trying to write an open file, which of course is not allowed by the OS.
To learn more on how to read monitors using COM, use this command from your script:
There you'll find examples and the explanation for all the properties and methods included in COM.
Best regards
Davis