"Talking" between Excel & NX with VB?

[tlemonds]

Hey guys,

So a few months ago I posted some questions about how to get started with VB in NX. You guys gave me some great tips and even provided me with example code! I'm now familiar enough with VB where I've written a few simple scripts to run in NX and a few scripts to run in Excel as well. However, I'm curious about beginning/if it is possible to write scripts that communicate between Excel and VB. For instance, I'm creating a parametric wing creation tool. The way I currently have it set up is that my NX sketch provides me the "outline" of the wing given user-defined expression inputs, I run a VB code in NX to export these coordinates to a .txt file. I then open up the embedded spreadsheet where these .txt files automatically refresh and are manipulated to provide me eight sets of airfoil coordinates in global coordinates and a macro exports them to .dat files. I then close the spreadsheet, give control back to NX, and run a VB script to populate the points and create splines among the points. This is beginning to seem like a very inefficient process. I was wondering if there was some way to write a VB script that can basically directly take the global coordinates of these "control" points and stick them in my Excel spreadsheet. Furthermore, I was hoping the VB script could simply import the manipulated point sets by calling them from a range of cells within the spreadsheet. I have no idea if this is remotely possible, not worth my time, or really easy. I think the actual coding would not be very difficult, but I'm not sure if you can run a VB script in NX that can call upon cells in an embedded spreadsheet, and vice versa. I've posted the script that I run within NX to export the "control" point coordinates to a .txt file. I'm thinking the trick lies in the first few lines of code where maybe you specify that you want to "Import" the embedded spreadsheet data or whatnot but I really have no idea where to start....


Option Strict Off
Imports System
Imports System.IO
Imports NXOpen
Imports NXOpen.Utilities
Imports NXOpen.Features
Imports NXOpenUI
Imports NXOpen.UF

Module PointsAlongCurveToTextFile
Dim theSession As Session = Session.GetSession()
Dim workPart As Part = theSession.Parts.Work
Dim displayPart As Part = theSession.Parts.Display

Sub Main()
Dim obj1() As NXObject
Dim pointset1 As PointSet = Nothing
Dim feats As FeatureCollection = workPart.Features
Dim name1 As String = Nothing
Dim objStreamWriter As StreamWriter
objStreamWriter = New StreamWriter("C:\Users\tlemonds\Documents\Applied Project\journals\coords1.txt")
Dim cnt1 as Integer = 1
For Each ft1 As Feature In feats
name1 = ft1.GetFeatureName
If name1.Contains("Point") Then
obj1 = ft1.GetEntities()
' write out to file
WriteCoords(obj1, cnt1, objStreamWriter, ft1)
cnt1 += 1
End If
Next
'Close the file.
objStreamWriter.Close()
End Sub

Public Sub SetUpCoordsFile()
Dim file As System.IO.StreamWriter
file = My.Computer.FileSystem.OpenTextFileWriter("C:\Users\tlemonds\Documents\Applied Project\journals\coords1.txt", True)
file.Close()
End Sub

Public Sub WriteCoords(ByVal obj1() As NXObject, ByVal cnt1 as Integer, ByVal objstreamwriter As StreamWriter, ByVal feat1 As Feature)
Dim xstr As String = Nothing
Dim ystr As String = Nothing
Dim zstr As String = Nothing
Dim name1 As String = feat1.GetFeatureName
For i As Integer = 0 To obj1.Length - 1
Dim pt As Point = DirectCast(obj1(i), Point)
xstr = FormatNumber(pt.Coordinates.X, 3, , , TriState.True)
ystr = FormatNumber(pt.Coordinates.Y, 3, , , TriState.True).PadLeft(15)
zstr = FormatNumber(pt.Coordinates.Z, 3, , , TriState.True).PadLeft(15)
objstreamwriter.WriteLine(xstr & ystr & zstr)
Next
End Sub
Public Function GetUnloadOption(ByVal dummy As String) As Integer
'----Other unload options-------
'Unloads the image immediately after execution within NX
GetUnloadOption = NXOpen.Session.LibraryUnloadOption.Immediately
End Function
End Module

 

[markrief]

As long as you can make a macro in excel to do what you want there, it seems you should be able to write a vb journal in NX that writes data to a spreadsheet, does excel operations on them (like a macro), reads data from the spreadsheet, and then does something in NX with the data.

There is a samples in your installation that should help. Take a look at UGOPEN\SampleNXOpenApplications\.NET\CAM\AssignAttributesForTooltips.vb. This opens and reads a spreadsheet, and then assigns attributes based on the data from the spreadsheet. It should get you on the right track to using excel from within NX.



Mark Rief
Product Manager
Siemens PLM

 

[tlemonds]

Mark,

Thanks for the reply! Yeah, I figured with my current knowledge I should be able to handle the coding I just wasn't sure what commands did the actual "communicating" between the spreadsheet and the part file. I'll take a look at the documentation you suggested and see if I can figure it out. Thanks again!

 

[markrief]

tlemonds,
I have a supplemental document "Communicating with Excel". Here is the intro:

"There are many situations where it is useful to exchange information (in either direction) between NX and Excel.
For example, you might want to export an NX bill-of-material or other report to Excel. Alternatively, you might want
to import data from an Excel spreadsheet and use this to assign values to attributes in NX. There are two somewhat
separate steps in the data exchange process: getting data into and out of NX, and getting data into and out of Excel."

It is available to licensed users, so please email me directly (first dot last at siemens dot com) with your company information, or log a call with GTAC and tell them I have the document, and they will find me.

Mark Rief
Product Manager
Siemens PLM

 

[MSPBenson]

One thing you can do is use expressions to read directly out of a specified excel sheet.
Below is an example expression where the file name is controlled by a separate string expression called "FG_CODE"

FG_CODE = Your_excel_file
P0 = ug_cell_read( "c:\Working_folder\" + FG_CODE +".xls", "H11" )

The FG_CODE expression is useful if you refer to the excel sheet in many expressions.

H11 refers to the cell in your excel sheet you want to link your expression to.

If you set up a standard seed part that you save as to a new one each time you can just change the FG_CODE expression to point to the related excel sheet for that version and hit update. The file pings into shape.


I went down the route you aretrying with excel which is doable but does rely on being able to compile and sign your journal into a dll file.
I found the above easier to manage.

Hope that helps...

Mark Benson
Aerodynamic Model Designer

To a Designer, the glass was right on CAD.

 

[cowski]

I went down the route you are trying with excel which is doable but does rely on being able to compile and sign your journal into a dll file.

It will be easier if you have an author license and can compile to .dll, but it is NOT required...

www.nxjournaling.com

 

[MSPBenson]

but it is NOT required

Could you share....
I spent ages trying to do this without the need of microsoft interop references which I've only ever managed to do using visual studio and compilation.
I'm sure the OP would also find it useful if you could expand.

Mark Benson
Aerodynamic Model Designer

To a Designer, the glass was right on CAD.

 

[cowski]

If you have NX 8.5 or later, there is an example included located at:
{NX install dir}\UGOPEN\SampleNXOpenApplications\.NET\CAM\AssignAttributesForTooltips.vb

Another simple example is posted at:

http://nxjournaling.com/?q=content/dimensions-drawing

And finally, yamada posted some excellent information in the GTAC forum thread:

https://bbs.industrysoftware.automation.siemens.com/vbulletin/showthread.php?t=51045

www.nxjournaling.com