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.
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
Thanks for any help!
Best regards,
Polynom3000
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
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
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.
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
Referring thread: My NX Version is below 8.5 (8.0.3.4), so I cannot work with it.
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
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 Modulewww.nxjournaling.com
RE: Error: Import MS Excel -> VB.Net program in NX
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
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
Thanks a lot for the provided code! This was exactly what I was looking for.
Best regards,
Polynom3000
RE: Error: Import MS Excel -> VB.Net program in NX
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
www.nxjournaling.com