topic Re: VBA for automatic data generation to Excel in Robot Structural Analysis Forum

(API) VBA for automatic data generation to Excel

Anonymous — Wed, 01 Apr 2015 07:25:25 GMT

Hello,

I am student and I am working on my master thesis. I came to the dead end and I need help with VBA.

Basically what I do is I take take initial strees (lets say 100 MPa), calculate relative elongation, and then apply it on the cable. Then I run analysis, and copy node results to excel for displacement and streesses in bars. Then I repeat the same procedure with 200 MPa, but the results are copied to table after previous results and then I continue with 300MPa, until 800MPa. After the procedure above I change, lets say, cables to bigger section and I start all over again. Then changing bars. I think you got the Idea. It is very time consuming and you have to be very careful with what and where you copy.

I know it is possible to write VBA for this repeated actions, but I have not mastered VBA and can do only simple code. I have tried to read API tutorial for Robot but i got lost. Could someone help me with this automatic data generation VBA?

I imagine proceadure should be as follows:

VBA reads a range of relative elongation values (or maybe there schould be a step increment)
It applies it to cable prestress value
Robot runs calculation
VBA copies Displacements of nodes to one sheet and stresses to another.
VBA applies another relative elangation for cable,
Robot runs analysis
and so on.

Copied results schould build one after another to one table.

I attach my Robot and excel file.

I have tried to generate some results manualy, it took me about one day, when I realized I have forgoten to change applied force value, after modifying my structure. So that means all that work went for nothing. Please help me 🙂

Sorry for my English, I am not a native speaker.

Re: VBA for automatic data generation to Excel

marcinrakus — Wed, 18 Feb 2015 08:59:30 GMT

Hi,

I've prepared for You some sample snippets for things You need for Your macro.

What You need is to adapt variables and put it in apropriate loops.

Good luck!

Dim robApp As New RobotApplication

'creating cable label
Dim cable As RobotLabel
Dim cableData As RobotBarCableData
Set cable = robApp.Project.Structure.Labels.Create(I_LT_BAR_CABLE, "cable1")
Set cableData = cable.Data
cableData.SectionAX = 0.01
cableData.AssemblingParam = I_BCAPT_ELONGATION_DL_RELATIVE
cableData.AssemblingParamValue = -0.00001
cableData.MaterialName = "Steel"
robApp.Project.Structure.Labels.Store cable

'assigning label to bar
Dim robotBar As robotBar
Dim barNumber As Integer
barNumber = 1
Set robotBar = robApp.Project.Structure.Bars.Get(barNumber)
robotBar.SetLabel I_LT_BAR_CABLE, "cable1"

'modification of label
Set cable = robApp.Project.Structure.Labels.Get(I_LT_BAR_CABLE, "cable1")
Set cableData = cable.Data
cableData.SectionAX = 0.01
cableData.AssemblingParam = I_BCAPT_ELONGATION_DL_RELATIVE
cableData.AssemblingParamValue = -0.00001
cableData.MaterialName = "Steel"
robApp.Project.Structure.Labels.Store cable

'calculations
robApp.Project.CalcEngine.Calculate

'getting displacements from nodes
Dim caseNumber As Integer
caseNumber = 1
Dim nodeNumber As Integer
nodeNumber = 1
Dim displacements As RobotNodeDisplacementData
Set displacements = robApp.Project.Structure.Results.Nodes.displacements.Value(nodeNumber, caseNumber)
MsgBox (displacements.UZ) 'eg

'getting results from bars
Dim stresses As RobotBarStressData
Dim position As Double
position = 0.5
Set stresses = robApp.Project.Structure.Results.Bars.stresses.Value(barNumber, caseNumber, position)
MsgBox (stresses.Smax) 'eg

If there is something missing and You don't find solution by analogy to above let know.

PS. Of course You need to add reference (Tools/References) to Robot API (Robot Object Model).

Re: VBA for automatic data generation to Excel

Anonymous — Sun, 22 Feb 2015 21:47:39 GMT

Thank you for your guidance,

I am still working on my macro and step by step I hope I will get it to work as I need.

Two more questions:

1. When I put "robapp.Project.CalcEngine.Calculate" in the loop, each time it starts new loop Robot asks me if I really want to update database. What is the comand for macro to always accept "yes"

2. Lets say for a loop cycle I put too large pretension and during analysis at some point i get warning message that matrix is not positive defined. What is the comand for macro to always accept "yes" or "no"

Thanks in advance

Re: VBA for automatic data generation to Excel

marcinrakus — Mon, 23 Feb 2015 06:33:28 GMT

Hi,

In both cases adding at the beginning

robapp.UserControl = False
robapp.Interactive = 0

should do the trick. These commands ignore warning and error messages and speed up running of RSA.

Re: VBA for automatic data generation to Excel

Rafal.Gaweda — Mon, 23 Feb 2015 11:03:45 GMT

http://forums.autodesk.com/t5/robot-structural-analysis/api-amp-ignore-dialogue-box/m-p/4904270/highlight/true#M21953

additionally

http://forums.autodesk.com/t5/robot-structural-analysis/run-calculation-from-vba-without-any-warnings/m-p/5512972#M30388

Re: VBA for automatic data generation to Excel

Anonymous — Tue, 24 Feb 2015 22:26:10 GMT

Hello again 🙂

Well, now I have stuck with creating a parametric tube section...

I need to create solid 3 cm diameter section and I have tried analogy with cable and i did't get it exactly right.

Here is a piece of my code:

Set section = robapp.Project.Structure.Labels.Create(I_LT_BAR_SECTION, "pipe")
Set sectionData = section.data
sectionData.Type = I_BST_STANDARD
sectionData.ShapeType = I_BSST_CIRC_FILLED
sectionData.SetValue I_BSDV_D, 3
robapp.Project.Structure.Labels.Store section

could you please write me the whole proceadure like for cables?

Re: VBA for automatic data generation to Excel

marcinrakus — Wed, 25 Feb 2015 08:04:23 GMT

Hi,

try like this:

Dim robapp As New RobotApplication
Dim section As RobotLabel
Set section = robapp.Project.Structure.Labels.Create(I_LT_BAR_SECTION, "pipe")
Dim sectionData As RobotBarSectionData
Set sectionData = section.Data
sectionData.ShapeType = I_BSST_USER_CIRC_FILLED
sectionData.Type = I_BST_NS_TUBE
Dim ns As RobotBarSectionNonstdData
Set ns = sectionData.CreateNonstd(0)
ns.SetValue I_BSNDV_TUBE_D, 0.3
sectionData.CalcNonstdGeometry
robapp.Project.Structure.Labels.Store section

Re: VBA for automatic data generation to Excel

Anonymous — Sun, 22 Mar 2015 11:09:54 GMT

It did not work. I managed to do it like this:

Set section = robapp.Project.Structure.Labels.Create(I_LT_BAR_SECTION, "pipe")
Set sectionData = section.data
sectionData.Type = I_BST_NS_TUBE
sectionData.ShapeType = I_BSST_USER_TUBE
Set ns = sectionData.CreateNonstd(0)
ns.SetValue I_BSNDV_TUBE_D, 0.01 * pasp_dia
sectionData.CalcNonstdGeometry
robapp.Project.Structure.Labels.Store section

Anyway,

I have one last question, which I could not find on forum.

I need to extract warning messages during calculation. During my loop calculation at some point my structure gets unstable (stiffnes matarix is negative), but it keeps calculating ignoring this warning. I need to get those warnings, so that later in excel I could filter those (bad) results.

Thank you in advance.

Re: VBA for automatic data generation to Excel

Anonymous — Tue, 24 Mar 2015 17:06:59 GMT

I have one last question, which I could not find on forum.

Thank you in advance.

Re: VBA for automatic data generation to Excel

Rafal.Gaweda — Wed, 25 Mar 2015 07:49:04 GMT

Try CalcMessage event or after closing robot extract warnings from robwin.log file.

Re: VBA for automatic data generation to Excel

Anonymous — Sat, 28 Mar 2015 16:21:17 GMT

Ok, I have no clue, how to get it out.

I have tried these as follows:

Dim msgno As String
msgno = robapp.IRDimMembCalc.MessagesNum

warningmsg = IRDimMembCalc.GetMessage(msgno, 1)

Cells(1, 27).Value = warningmsg

Cells(1, 24).Value = I_BCW_USER_WARNING
Cells(1, 25).Value = I_CMSL_WARNING
Cells(1, 26).Value = I_BCW_USER_WARNING

Dim message As RobotCalcEngine
Set message = robapp.Project.CalcEngine.CalcMessage(1, 1, 1, 1, 1, 1)
Cells(1, 17).Value = message

Nothing works 🙂 As I have told before, I am not that familiar with VBA at all. Could you please write me whole proceadure?

Re: VBA for automatic data generation to Excel

Rafal.Gaweda — Tue, 31 Mar 2015 07:57:37 GMT

See attached xls.

Check Module1 and Class1 code

Re: VBA for automatic data generation to Excel

Anonymous — Tue, 31 Mar 2015 21:07:37 GMT

Thank you for you effort but, sorry for being so enoyng... I really feel bad about it...

Could you please look ata my code? I have marked the lines for you, so I would not waste your time anymore.

I feel that I am close, but I am missing something. As far as I understood I cant start the event when I am in the loop...

Re: VBA for automatic data generation to Excel

Rafal.Gaweda — Wed, 01 Apr 2015 07:24:44 GMT

See Class 1 in attached file

Warnings displayed in column 17

Re: VBA for automatic data generation to Excel

Anonymous — Mon, 06 Apr 2015 12:28:39 GMT

Thank you very much.

Without your help I would not have done this.