Capturing the selected excel file name in a String Variable

[Anonymous]

Hello guys, this piece of code Im working at the moment pretends to capture the value of the 
selected Excel document in the variable MyFile in order to use it in another part. I want to 
retain that value and clean it at the end of the program. So far is not working, I added the Message 
box just to see if its working but Im not sure Im doing the right thing.
Any help will be much appretiated. Thanks in advanced for your help
Kind Regards
Pedro

Public Sub OpenExcel()
	Dim oFileDlg As Inventor.FileDialog = Nothing
	InventorVb.Application.CreateFileDialog(oFileDlg)
	'oFileDlg.FileName = ""
	Dim MyFile As String = oFileDlg.FileName      'I want to record the value of the selected Excel document in the Variable MyFile
	oFileDlg.Filter = "Microsoft Excel files(*.xls)|*.xls"
	oFileDlg.SuppressResolutionWarnings = True
	oFileDlg.OptionsEnabled = True
	'oFileDlg.OptionValues.Insert or Add or something???

	oFileDlg.CancelError = True
	On Error Resume Next
	oFileDlg.ShowOpen()
	MessageBox.Show("This is the value of MyFile variable" & MyFile,"Title", MessageBoxButtons.OK)
End Sub

[dutt.thakar · Accepted solution]

@Anonymous 

Just need to alter the sequence in your code. See below code, if you will use oFile in the message box you will get filename with path, I have also removed the path from filename and saved it into the shared variable x here for test.

Sub Main()
	Dim oFileDlg As Inventor.FileDialog = Nothing
	InventorVb.Application.CreateFileDialog(oFileDlg)
	'oFileDlg.FileName = ""
	'Dim MyFile As String = oFileDlg.FileName      'I want to record the value of the selected Excel document in the Variable MyFile
	oFileDlg.Filter = "Microsoft Excel files(*.xls)|*.xls"
	oFileDlg.SuppressResolutionWarnings = True
	oFileDlg.OptionsEnabled = True
	'oFileDlg.OptionValues.Insert or Add or something???

	oFileDlg.CancelError = True
	On Error Resume Next
	oFileDlg.ShowOpen()
	oFile = oFileDlg.FileName
	
	SharedVariable("x")= System.IO.Path.GetFileName(oFile)
	MessageBox.Show("This is the value of MyFile variable" & SharedVariable("x"),"Title", MessageBoxButtons.OK)
End Sub

If you are planning to use this filename in different Sub routines or in different rules in the same Inventor file with iLogic I would suggest to use Shared variable (as it is used in above rule), this will also allow you to save your filename in a shared variable and then you can call that variable anywhere in the same Inventor file in any rule.

Hope this will be helpful. 

[Anonymous]

Thank you very much, Sr, certainly I'm too tired to see with good eyes what my knowledge of coding cannot see with the soul. With all my heart thank you so much. Perhaps you can help me more.

What I'm trying to do is connect an excel spreadsheet (allowing the user to select the pre-existing file coming from SAGE Software) with inventor ilogic and capture a certain array of values in order. Once the connection is made the code will compare the filtered string value store in the Stock Number with the array of values from the excel column B2 to B12. Then if the Stock Number = Excel Code it will copy the description cell existing in excel to a hidden property in the part file Please see the code below, (is failing converting the array from excel to string)

Sub Main
	   
    Dim StartTime As DateTime			' StartTime at the begin of the processing
	Dim ElapsedTime As TimeSpan			' Capturing  the ElapsedTime
 	'Dim oDoc As Document, oDocIntName As String, oDocRevID As String, oDocGUID As String
	oDoc = ThisApplication.ActiveDocument
	StartTime = Now
	Call OpenExcel
	Call Delete_iprop()
	oDocIntName = oDoc.InternalName
	oDocRevID = oDoc.DatabaseRevisionId
	oDocGUID = oDoc.[_PrimaryDeselGUID]
	'iProperties.Value("Custom", "00_ItemID") = Mid(oDocRevID,2,36)
	Call Write_iProps()
	ElapsedTime = Now().Subtract(StartTime)
	MessageBox.Show("Asset tagg process complete. It Tooks only " & Round(ElapsedTime.TotalSeconds, 3) & " sec.", "Asset Tag information", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1)
	iLogicVb.UpdateWhenDone = True
	GoExcel.Close()
	Exit Sub

End Sub


Public Sub OpenExcel()
	Dim oFileDlg As Inventor.FileDialog = Nothing
	InventorVb.Application.CreateFileDialog(oFileDlg)
	'oFileDlg.FileName = ""
	'Dim MyFile As String = oFileDlg.FileName      'I want to record the value of the selected Excel document in the Variable MyFile
	oFileDlg.Filter = "Microsoft Excel files(*.xls)|*.xls"
	oFileDlg.SuppressResolutionWarnings = True
	oFileDlg.OptionsEnabled = True
	'oFileDlg.OptionValues.Insert or Add or something???
	oFileDlg.CancelError = True
	On Error Resume Next
	oFileDlg.ShowOpen()
	oFile = oFileDlg.FileName
	SharedVariable("x")= System.IO.Path.GetFileName(oFile)    ' to use this variable in another part of the code
	MessageBox.Show("This is the value of MyFile variable " & SharedVariable("x"),"Title", MessageBoxButtons.OK)
End Sub

Dim i As Integer
Dim MyFile As String = SharedVariable("x")= System.IO.Path.GetFileName(oFile)
'Dim MyFile As String = "E:\Users\User\Documents\$WorkingFolder\002-PROJECTS\003-FURNITURE DESIGN\00-(16-12-2020)\00-PAULA ROSA MANHATAN\01-C1\06-XLS\TALL FRIDGE FREEZER PIECE PARTS SETUP.xls"
'Dim CdeArea01 As String = "FL", CdeArea02 As String = "AC", CdeArea03 As String = "ML", CdeArea04 As String = "UN", CdeArea05 As String = "AG", CdeArea06 As String = "CS", CdeArea07 As String = "DM", CdeArea08 As String = "DF", CdeArea09 As String = "SG"
Dim oDoc As Document, oDocIntName As String, oDocRevID As String, oDocGUID As String

Sub Delete_iprop()
    oCustomPropertySet = ThisDoc.Document.PropertySets.Item("Inventor User Defined Properties")             'define custom property collection
    For Each oCustProp In oCustomPropertySet                                                                'look at each property in the collection
        oCustProp.Delete  																					'delete the custom iProperty
    Next
End Sub

Sub Write_iProps()
Line1:
    Dim Cdelist As New System.Collections.Specialized.StringCollection
    'Dim CdeCheck As String = UCase(InputBox("Item code please?", "Question"))
	Dim CdeCheck As String = Mid(iProperties.Value("Project", "Stock Number"),4,14)
	'Dim MyCoDe() As String = {"FLR-01", "MRR-01", "CLG-01", "CLG-02", "RDW-00", "FGP-01", "PNL-01", "PNL-02", "PNL-03", "WLL-01", "DRR-01", "DRR-02", "DRR-03", "DRR-04", "DRR-05", "HDL-01", "SDP-01", "SDP-02", "TPS-01", "TPS-02", "TPS-03", "SHR-01", "SHT-01", "SHE-01", "SHH-01", "SHH-02", "SHM-01", "SHM-02", "SHS-01", "SHS-02", "SHF-01", "WCU-01", "WCU-02", "CSW-01", "FPU-01", "FSU-01", "RHD-01", "BHD-01", "CHK-01", "URN-01", "BSN-01", "BSN-02", "BIN-01", "BIN-02", "TWD-01", "TWD-02", "PLT-01", "DIV-01", "PHL-01", "GRB-01", "GRB-02", "GRB-03", "GRB-04", "CLS-01" }
	Dim MyCoDe() As String = {GoExcel.CellValues(MyFile, "TALL_FF_2019_PP", "B2", "B12") }
	Cdelist.AddRange(MyCoDe)       
	 If (Cdelist.Contains(CdeCheck)) And iProperties.Value("Summary", "Subject") <> "" Then
	        Call Delete_iprop()
				'oDocIntName = oDoc.InternalName
				oDocRevID = oDoc.DatabaseRevisionId
				'oDocGUID = oDoc.[_PrimaryDeselGUID]
				'iProperties.Value("Custom", "00_ItemID") = Mid(oDocRevID,2,36)
				GoExcel.Open(MyFile, CdeCheck)
					For I = 0 To UBound(MyCoDe)
					Dim MyItEm As String = MyCoDe(i)
						If Mid(iProperties.Value("Project", "Stock Number"),4,14)= MyItEm Then
							GoExcel.Open(MyFile, MyItEm)
									Try    ' the error!  
										Dim doc As Document = ThisApplication.ActiveDocument                                                                               ' Get the active document.
  										Dim customSet As PropertySet = doc.PropertySets.Add("SAGE")                                                                        ' Create a new property set.
										    customSet.Add(UCase(Left(iProperties.Value("Project", "Stock Number"), 2)), "00-SAG_WHAREHOUSE")                                   ' Add a property.
										    customSet.Add(UCase(Left(iProperties.Value("Project", "Stock Number"), 14)), "01-SAG_PRODUCT")
										    customSet.Add("", "02-SAG_SHTDESCRIPTION")
										    customSet.Add("", "03-SAG_LGNDESCRIPTION")
										    customSet.Add("", "04-SAG_STCKWIDTH")
										    customSet.Add("", "02-SAG_STCKLENGTH")
										    customSet.Add("", "02-SAG_STCKHEIGHT")
										    customSet.Add("", "07-SAG_DRWNUMBER")

										Catch
											' do nothing!
										End Try
									End If		
				Next
			ElseIf CdeCheck = "" Then
				MessageBox.Show("Item code cannot be empty. Please enter a valid code.", "Asset Tag information", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1)
				GoTo Line1
			ElseIf (Cdelist.Contains(CdeCheck)) And iProperties.Value("Summary", "Subject") = "" Then
				MessageBox.Show("Item doesn't have code. Program will be terminated", "Asset Tag information", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1)
				GoTo Line2
			Else
			MessageBox.Show("Item doesn't have properties in excel. Please amend", "Asset Tag information", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1)
			Return
			End If
		Cdelist.Clear
Line2:		
iLogicVb.UpdateWhenDone = True 
End Sub