slow code because it that time to open an excel sheet .. is that a way not have to reopen the sheet each time ?

[Darkforce_the_ilogic_guy]

I have a code that need to read some info form a excel fil... my problem is that this is very slow. I use excel because i have the skill and it is easy to see and add information to it. Is there a way to make it faster ?

[A.Acheson]

Can you show the code your using? Depending on your code running an excel file should open in 1.8 to 2 seconds. There may be other issues at play. Also check if unecessary addin are removed from excel document like 3d  connection mouse etc. Check them off and test. 

[Darkforce_the_ilogic_guy]

Sub Main()
	Dim partnumber 
	ExcelPath = "C:\Working Folder\CAD\Kallesoe\Kallesoe iLogic\Underdevelopment\Accessories Finder\Accessories Filder.xlsm"
	ExcelSheet = "Assessoires"

'ask you to select spareparts
	Dim oRefDoc As ComponentOccurrence = ThisApplication.CommandManager.Pick(SelectionFilterEnum.kAssemblyLeafOccurrenceFilter, "Select FG component")
'setBL(oRefDoc)


partnumber = iProperties.Value(oRefDoc.Name, "Project", "Part Number") 
GoExcel.Open(ExcelPath, ExcelSheet)
Dim i
i=2

Do Until i > 600
    'MsgBox("Row A " & GoExcel.CellValue(ExcelPath , ExcelSheet, "A" & i) & " " & partnumber  )
	If GoExcel.CellValue(ExcelPath , ExcelSheet, "A" & i) = partnumber Then
'test = test & " " & GoExcel.CellValue(ExcelPath, ExcelSheet, "B" & i) & "	" & GoExcel.CellValue(ExcelPath, ExcelSheet, "C" & i) & vbLf

test = test & GoExcel.CellValue(ExcelPath, ExcelSheet, "B" & i) & " OR " 


Else 
	
End If
	
	
    i = i + 1
Loop

GoExcel.Close
If (test) <> "" Then
	'MessageBox.Show(test, "Accessories for " & partnumber)


Else
	MsgBox("No Accessries found")
End If
'MsgBox(test.Tostring.count)
test = Left(test, test.Tostring.count-4)
filecreate(test)
End Sub


Sub setBL(oOcc As ComponentOccurrence)
	Try
		

		partnumber = iProperties.Value(oOcc.Name, "Project", "Part Number") 
		'InventorVb.DocumentUpdate()
	Catch

		'iLogicVb.RunExternalRule("EditLengthOfOldTypeProfile")

	End Try
	
	'InventorVb.DocumentUpdate()
	'msgbox (partnumber)
End Sub


Sub filecreate(txt)
	
	'%appdata%\Autodesk\VaultCommon\Servers\Services_Security_1_7_2019\cadsrv\Vaults\Kallesoe\Searches
'oWrite = System.IO.File.CreateText("C:\Users\bt\Desktop\" & "TestSearch") 
oWrite = System.IO.File.CreateText("C:\Users\bt\AppData\Roaming\Autodesk\VaultCommon\Servers\Services_Security_1_7_2019\cadsrv\Vaults\Kallesoe\Searches\" & "TestSearch") 
'&#34;

Dim nr3 = txt
Clipboard.SetText(nr3)



Dim line1 = "<?xml version=""1.0""?>"
Dim line2 = "<SearchParameters xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" TypeName=""Connectivity.Explorer.Framework.IExplorerObject"" LatestOnly=""true"" SearchSubfolders=""true"" SearchFullContent=""false"" IsFolder=""true"" Id=""d772de38-ccc7-42c0-b771-59b86cac6627"" xmlns=""http://schemas.autodesk.com/msd/plm/SearchParameters/2009-07-23"">"
Dim line3 = "  <SearchConditions>"
Dim line4 = "    <SearchCondition Value=""" & nr3 & """ PromptForValue=""false"" PropertyKey=""PartNumber"">"
Dim line5 = "      <Type>CONTAINS</Type>"
Dim line6 = "    </SearchCondition>"
Dim line7 = "  </SearchConditions>"
Dim line8 = "  <SearchLocations>"
Dim line9 = "    <Locations URI=""vaultfolderpath:$/Designs/Tegninger"" />"
Dim line10 = "    <Locations URI=""vaultfolderpath:$/Designs/Standard components"" />"
Dim line11 = "  </SearchLocations>"
Dim line12 = "</SearchParameters>"

oWrite.WriteLine(line1)
oWrite.WriteLine(line2)
oWrite.WriteLine(line3)
oWrite.WriteLine(line4)
oWrite.WriteLine(line5)
oWrite.WriteLine(line6)
oWrite.WriteLine(line7)
oWrite.WriteLine(line8)
oWrite.WriteLine(line9)
oWrite.WriteLine(line10)
oWrite.WriteLine(line11)
oWrite.WriteLine(line12)

oWrite.Close()
'MsgBox("--> "" <--")


'open the file
'ThisDoc.Launch("C:\Users\"username Here"\AppData\Roaming\Autodesk\VaultCommon\Servers\Services_Security_1_7_2019\cadsrv\Vaults\Kallesoe\Searches\" & "TestSearch")

End Sub 

This is the complete code.. it have about 150 lines in Excel

[kresh.bell]

Hi,

sorry for the intrusion, I had extremely big problems with excel. I finally found the cause of the problem. One addin in excel (opening tabs instead new windows) caused the problem, also some other addins but to a lesser extent

[Curtis_Waguespack]

Hi @Darkforce_the_ilogic_guy 

You might try using something like this, so that it the code opens the Excel file, and then just uses it ( rather than opening it again) .

I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com

GoExcel.Open(ExcelPath, ExcelSheet)
i = 2

Do Until i > 600
	If GoExcel.CellValue("A" & i ) = partnumber Then
		test = test & GoExcel.CellValue("B" & i) & " OR "
	End If
	i = i + 1
Loop

GoExcel.Close

[A.Acheson]

Here is one such COM Addin  and the negative effect is approx 3 secs. 

Here is a testing code for both go excel and Excel dll method. Both seem to be equally as fast with this simple task. 

'Option Explicit On
'AddReference "microsoft.office.interop.excel.dll"
'Imports XL = Microsoft.Office.Interop.Excel

Sub Main
	'https://forums.autodesk.com/t5/inventor-forum/ilogic-measure-time-elapsed-during-run-rule/td-p/5578813
	Dim stopWatch As New Stopwatch()

	stopWatch.Start()

	Call ExcelApplication_dll()
	'Call Go_Excel()

	stopWatch.Stop()	

	' Get the elapsed time as a TimeSpan value.
	Dim ts As TimeSpan = stopWatch.Elapsed

	' Format and display the TimeSpan value.
	Dim elapsedTime As String = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10)
	MsgBox("RunTime " & elapsedTime)

End Sub


'5.45 5.55 secs 3d Connexion on, 3.04,2.86,2.36 3d Connexion off
Sub ExcelApplication_dll()
	
	Dim i As Integer = 0
	
	Dim xlApp As XL.Application = CreateObject("Excel.Application")
	Dim xlWb As XL.Workbook = xlApp.Workbooks.Open("C:\Users\BOM.xlsx")
	
	'comment out or change to false in order to not show Excel
	'xlApp.Visible = True 

	Dim xlWs As XL.Worksheet = xlApp.ActiveWorkbook.ActiveSheet
	
	Do Until i > 600
		i = i + 1
		xlWs.Range("A" & i).Value = i
	Loop

	xlWb.Save
	xlWb.Close 
	'xlApp.Quit
	
End Sub

'5.3 Secs 3d Connexion on, 2.89,2.62,3.07-3d Connexion off
Sub Go_Excel()
	Dim i As Integer = 0
	GoExcel.Open("C:\Users\BOM.xlsx", "Sheet1")

	Do Until i > 600
		i = i + 1
		GoExcel.CellValue("A" & i ) = i 
	Loop
	
	GoExcel.Save
	GoExcel.Close
End Sub