×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Error: Import MS Excel -> VB.Net program in NX

Error: Import MS Excel -> VB.Net program in NX

Error: Import MS Excel -> VB.Net program in NX

(OP)
Hello community,

My goal is to import a few cells from a MS Excel file to a vb.net application running inside NX.

Main steps:
1) Define the file path of the excel file
2) Click a read/import-button
3) Values of certain cells (only numbers) are imported and related to respective NumericUpDown elements.
4) Do manipulations inside NX with the imported values

The advantage with the NumericUpDown elements is that after importing the values the user can see the numbers and can still modify them if needed.

In this example I am only using one NumericUpDown element.
The code A works pretty fine inside Microsoft Visual Studio 2012. I just click on the button and the value is imported to the NumericUpDown1 element.

CODE --> A

Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim excel As New Microsoft.Office.Interop.Excel.Application()
        Dim wb As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Open("C:\Test\ExcelTest.xlsx")
        Dim ws As Microsoft.Office.Interop.Excel.Worksheet = TryCast(excel.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)

        NumericUpDown1.Value = ws.Cells(1, 4).Value2

        wb.Close(True, Type.Missing, Type.Missing)
        excel.Quit()

    End Sub
End Class 

Further information about the code fragments:
http://www.dreamincode.net/forums/topic/240406-que...
Post #7 (Posted 21 July 2011 - 08:05 AM)

http://siddharthrout.wordpress.com/vb-net-and-exce...


I added the standard NX libraries references in Studio and additionally the Excel library: Project -> Reference -> COM: Microsoft Excel 14.0 Object Library

In order to paste the code into the Journal Editor (or load a vb file), I compiled the whole project as shown below and saved it as project.vb. Line 16 is responsible for the Excel reference, but there might by something wrong with it? The program does not start, I get 3 journal compile errors:

Type Microsoft.Office.Interop.Excel.Application is not defined
Type Microsoft.Office.Interop.Excel.Workbook is not defined
Type Microsoft.Office.Interop.Excel.Worksheet is not defined

System: Windows 7 Prof, 64bit
NX: 8.0.3.4

CODE --> B

Option Strict Off

Imports System
Imports NXOpen
Imports NXOpenUI
Imports NXOpen.UF
Imports NXOpen.Selection
Imports System.IO
Imports System.Collections.Generic
Imports NXOpen.Drawings
Imports System.Windows.Forms
Imports System.Math
Imports NXOpen.Utilities
Imports NXOpen.Features

Imports Microsoft.Office.Interop.Excel

<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class Form1
    Inherits System.Windows.Forms.Form


    <System.Diagnostics.DebuggerNonUserCode()> _
    Protected Overrides Sub Dispose(ByVal disposing As Boolean)
        Try
            If disposing AndAlso components IsNot Nothing Then
                components.Dispose()
            End If
        Finally
            MyBase.Dispose(disposing)
        End Try
    End Sub

    Public Shared Sub Main()
        Application.Run(New Form1)
    End Sub
    Public Sub New()
        Me.InitializeComponent()
    End Sub

'=#=#=#=#=#=#=#=#=#=#CODE<>

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim excel As New Microsoft.Office.Interop.Excel.Application()
        Dim wb As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Open("C:\Test\ExcelTest.xlsx")
        Dim ws As Microsoft.Office.Interop.Excel.Worksheet = TryCast(excel.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)

        NumericUpDown1.Value = ws.Cells(1, 4).Value2

        wb.Close(True, Type.Missing, Type.Missing)
        excel.Quit()

    End Sub

'=#=#=#=#=#=#=#=#=#=#CODE</>


    Private components As System.ComponentModel.IContainer


  

    <System.Diagnostics.DebuggerStepThrough()> _
    Private Sub InitializeComponent()
        Me.Button1 = New System.Windows.Forms.Button()
        Me.NumericUpDown1 = New System.Windows.Forms.NumericUpDown()
        CType(Me.NumericUpDown1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(101, 85)
        Me.Button1.Name = "Button1"
        Me.Button1.Size = New System.Drawing.Size(75, 23)
        Me.Button1.TabIndex = 0
        Me.Button1.Text = "Button1"
        Me.Button1.UseVisualStyleBackColor = True
        '
        'NumericUpDown1
        '
        Me.NumericUpDown1.DecimalPlaces = 1
        Me.NumericUpDown1.Location = New System.Drawing.Point(85, 114)
        Me.NumericUpDown1.Maximum = New Decimal(New Integer() {100000, 0, 0, 0})
        Me.NumericUpDown1.Name = "NumericUpDown1"
        Me.NumericUpDown1.Size = New System.Drawing.Size(120, 20)
        Me.NumericUpDown1.TabIndex = 1
        '
        'Form1
        '
        Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.ClientSize = New System.Drawing.Size(284, 262)
        Me.Controls.Add(Me.NumericUpDown1)
        Me.Controls.Add(Me.Button1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        CType(Me.NumericUpDown1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents NumericUpDown1 As System.Windows.Forms.NumericUpDown

End Class 

Thanks for any help!

Best regards,
Polynom3000

RE: Error: Import MS Excel -> VB.Net program in NX

I've not managed to get MS interops working with an uncompiled journal.
Doing the following in visual studio:
Add a reference into my project for the excel interop dll. Add the NX signing resource as a reference. Then compile it and then sign it.
This requires the NXopen author license.

Someone mentioned in another post about being able to do this un-compiled but they didn't post how.

The issue we have with the un-compiled version is that we aren't able to add the reference to the interop dll (the imports line doesn't seem to be enough)
It may be that somewhere in the NX setup we can add to the list of dll's that get loaded?

Looking at my syslog file when I run an un-compiled journal just before it runs there are several entries like this:

Adding C:\apps\nx\NX85\ugii\managed\\NXOpen.Utilities.dll as a reference item


referencing the managed folder and the dll's in it. Looks like we need to shoe horn the interop dll in here somehow....

Let me know how you get on.

Mark Benson
Aerodynamic Model Designer

To a Designer, the glass was right on CAD.

RE: Error: Import MS Excel -> VB.Net program in NX

If you have an author license, you can reference other libraries freely; however, if you are running the code as a journal, what you are allowed to reference is limited. From the NX help programmer's guide:

Quote (NX help -> programmers guide ->creating nx open automation -> journals)

The following .NET libraries are supported by journaling:
  • mscorlib.dll
  • System.dll
  • System.Windows.Forms.dll
  • System.Drawing.dll
Any .NET functionality not supported in one of these libraries will not replay from a Journal. If your application requires .NET functionality not found in these libraries then you will need to compile and link your application referencing the required library. For instance, if your application needs to implement a client/server architecture you may need to link to System.Runtime.Remoting.dll.

That said, you can still work with Excel in a journal but you will have to use late binding and you lose the convenience of intellisense in the IDE.

Quote (MSPBenson)

Someone mentioned in another post about being able to do this un-compiled but they didn't post how.

Perhaps you are referring to thread561-363398: "Talking" between Excel & NX with VB?
If so, I'd refer you to the post in that thread dated: 25 Apr 14 12:56
If you have additional questions, I'll do my best to answer them.

www.nxjournaling.com

RE: Error: Import MS Excel -> VB.Net program in NX

(OP)
I do not have the NXOpen author license and therefore cannot sign my projects and do additional stuff.
Referring thread: My NX Version is below 8.5 (8.0.3.4), so I cannot work with it. sad

The way I work is as follows:
1) Simply copy the lines shown in my first post (Code B) [this is some kind of vb-NX template]
2) Paste new code (and therefore overwrite old code) created in Visual Studio (e.g. NX commands after clicking on button xy)
inside this area: '=#=#=#=#=#=#=#=#=#=#CODE<> and '=#=#=#=#=#=#=#=#=#=#CODE</>
3) Below: Paste Windows Form code which was created automatically by Studio (size of boxes, etc.)
4) Save the 3 parts as vb file and run it with the NX Journal Editor

Might there be a slight chance to somehow “correct” the Imports xy line?
I don’t know how to import the values of the excel cells under the current circumstances.


Best regards,
Polynom3000

RE: Error: Import MS Excel -> VB.Net program in NX

The journal below shows how to retrieve a value from an Excel file. In particular, the code below will allow you to select an Excel file and will report the value of cell A1. A more interesting journal would do something useful with the value, this one just shows the value in a message box.

CODE

Option Strict Off
Imports System
Imports System.Collections.Generic
Imports System.Windows.Forms

Imports NXOpen
Imports NXOpen.Assemblies
Imports NXOpen.UF
Imports NXOpenUI

Module Module1

    Dim theSession As Session = Session.GetSession()
    Dim theUfSession As UFSession = UFSession.GetUFSession()
    Dim theUISession As UI = UI.GetUI

    Sub Main()

        If IsNothing(theSession.Parts.Work) Then
            'active part required
            Return
        End If

        Dim workPart As Part = theSession.Parts.Work
        Dim displayPart As Part = theSession.Parts.Display
        Dim lw As ListingWindow = theSession.ListingWindow
        lw.Open()

        Dim excelBom As String = ChooseBomFile()
        If excelBom = "" Then
            'user pressed cancel
            Return
        End If

		ReportA1Value(excelBom)

        lw.Close()

    End Sub

    Function ChooseBomFile() As String

        Dim fdlg As OpenFileDialog = New OpenFileDialog()
        fdlg.Title = "Select Excel BOM file"
        Dim dir As String
        dir = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
        'dir = "C:\temp"
        fdlg.InitialDirectory = dir
        fdlg.Filter = "Excel Files(*.xls;*.xlsx;*.xlsm)|*.xls;*.xlsx;*.xlsm"
        fdlg.FilterIndex = 2
        fdlg.RestoreDirectory = True
        If fdlg.ShowDialog() = DialogResult.OK Then
            Return fdlg.FileName
        Else
            Return ""
        End If

    End Function

    Sub ReportA1Value(ByVal excelFileName As String)

        'create Excel object
        Dim objExcel = CreateObject("Excel.Application")
        If objExcel Is Nothing Then
            theUISession.NXMessageBox.Show("Error", NXMessageBox.DialogType.Error, "Could not start Excel, journal exiting")
            Exit Sub
        End If

        'open excel file
        Dim objWorkbook = objExcel.Workbooks.Open(excelFileName)
        objExcel.visible = False

		MsgBox("cell value: " & objWorkbook.activesheet.cells(1, 1).value)

        objExcel.Quit()
        objWorkbook = Nothing
        objExcel = Nothing

    End Sub


    Public Function GetUnloadOption(ByVal dummy As String) As Integer

        'Unloads the image when the NX session terminates
        GetUnloadOption = NXOpen.Session.LibraryUnloadOption.AtTermination

        '----Other unload options-------
        'Unloads the image immediately after execution within NX
        'GetUnloadOption = NXOpen.Session.LibraryUnloadOption.Immediately

        'Unloads the image explicitly, via an unload dialog
        'GetUnloadOption = NXOpen.Session.LibraryUnloadOption.Explicitly
        '-------------------------------

    End Function

End Module 

www.nxjournaling.com

RE: Error: Import MS Excel -> VB.Net program in NX


Quote (cowski)

Perhaps you are referring to thread561-363398: "Talking" between Excel & NX with VB?: "Talking" between Excel & NX with VB?
That's really odd. I've checked that thread several times since the 25th of april and never seen your post ....

Thanks for this, it's really useful thumbsup2
I've been forced to compile (and loose a lot of debugging information in the process) whenever I've used MS interop stuff.

I'll be sure to see if I can get them working without in the future.

Mark Benson
Aerodynamic Model Designer

To a Designer, the glass was right on CAD.

RE: Error: Import MS Excel -> VB.Net program in NX

(OP)
Hey cowski!

Thanks a lot for the provided code! This was exactly what I was looking for. smile

Best regards,
Polynom3000

RE: Error: Import MS Excel -> VB.Net program in NX

I know I'm a little late to the thread, but I had some questions that relate.

Has anyone looked into using LINQ to Excel for querying an excel sheet / database?
https://code.google.com/p/linqtoexcel/
Would this only be possible with / without an author license?

Thanks.

RE: Error: Import MS Excel -> VB.Net program in NX

I'm about 90% sure that you can only use linq if you have an author license.

www.nxjournaling.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources