Could not match column title if closing excel

[Anonymous]

Hello.

I have this Rule below that reads some data from an Excel file. (It's longer than this, but this is the part that's not working)

Dim minV As Double = Double.MaxValue
Dim minV_1 As Double = Double.MaxValue
Dim minV_2 As Double = Double.MaxValue

Dim List_45_35_L As New ArrayList
Dim List_NL_L As New ArrayList

Dolžina_Obstoječa_NL_L = GoExcel.CellValues("\\BASIC-SRV\Inventor\GostolTST_AI\iLogic POLŽ\iLogic_OBSTOJEČE_POLŽEVKE_FI_200.xlsx", "NOTRANJA LEVO", "B3", "B1000")
Dolžina_Obstoječa_45_35_L = GoExcel.CellValues("\\BASIC-SRV\Inventor\GostolTST_AI\iLogic POLŽ\iLogic_OBSTOJEČE_POLŽEVKE_FI_200.xlsx", "ČEP FI 45_35", "B3", "B1000")
	
For Each oItem1 In Dolžina_Obstoječa_45_35_L
	
	List_45_35_L.Add(oItem1)
	
Next

For Each oItem2 In Dolžina_Obstoječa_NL_L
	
	List_NL_L.Add(oItem2)
	
Next 

For i = 0 To List_45_35_L.Count - 1

	If Abs(List_45_35_L(i) - ((Vmesno:1 - 40) / 2)) < minV_1
		
		minV_1 = Abs(List_45_35_L(i) - ((Vmesno:1 - 40) / 2))
		a_1 = i
		
	End If

Next

For i = 0 To List_NL_L.Count - 1
	
	If Abs(List_NL_L(i) - ((Vmesno:1 - 40) / 2)) < minV_2
		
		minV_2 = Abs(List_NL_L(i) - ((Vmesno:1 - 40) / 2))
		a_2 = i
		
	End If

Next

	Polževka_45_35_dolžina = List_45_35_L(a_1)

	GoExcel.TitleRow = 2
	b = GoExcel.FindRow("\\BASIC-SRV\Inventor\GostolTST_AI\iLogic POLŽ\iLogic_OBSTOJEČE_POLŽEVKE_FI_200.xlsx", "ČEP FI 45_35", "DOLŽINA_L [mm]", "=", Polževka_45_35_dolžina)
	Polževka_45_35 = GoExcel.CellValue("\\BASIC-SRV\Inventor\GostolTST_AI\iLogic POLŽ\iLogic_OBSTOJEČE_POLŽEVKE_FI_200.xlsx", "ČEP FI 45_35", "A" & b)
	
	
	Polževka_NL_dolžina = List_NL_L(a_2)

	GoExcel.TitleRow = 2
	b = GoExcel.FindRow("\\BASIC-SRV\Inventor\GostolTST_AI\iLogic POLŽ\iLogic_OBSTOJEČE_POLŽEVKE_FI_200.xlsx", "NOTRANJA LEVO", "DOLŽINA_D [mm]", "=", Polževka_NL_dolžina)
	Polževka_NL = GoExcel.CellValue("\\BASIC-SRV\Inventor\GostolTST_AI\iLogic POLŽ\iLogic_OBSTOJEČE_POLŽEVKE_FI_200.xlsx", "NOTRANJA LEVO", "A" & b)

Now this all works perfectly fine, until I simulate another part of my code (located in a part of my assembly) which accesses another Excel file, writes some data to it and then saves and closes that file.

When the code returns to this first rule, I guess the Excel file is closed and the TitleRow and FindRow doesn't work because of it?

To simulate the part of the rule that is written in another part, insert an GoExcel.Close after reading the values:

Dolžina_Obstoječa_NL_L = GoExcel.CellValues("\\BASIC-SRV\Inventor\GostolTST_AI\iLogic POLŽ\iLogic_OBSTOJEČE_POLŽEVKE_FI_200.xlsx", "NOTRANJA LEVO", "B3", "B1000")
Dolžina_Obstoječa_45_35_L = GoExcel.CellValues("\\BASIC-SRV\Inventor\GostolTST_AI\iLogic POLŽ\iLogic_OBSTOJEČE_POLŽEVKE_FI_200.xlsx", "ČEP FI 45_35", "B3", "B1000") 

GoExcel.Close

After doing this, the GoExcel.FindRow stops working (could not match column title). Even if I write a GoExcel.Open("excelfilepath", "sheet"), it still can't find the column title.

I've attached the Excel file.

Any help would be very appreciative, I've been losing my mind over this for quite some time now.

[Daan_M]

What happends when you use the snippet below to exctract the columname? 

GoExcel.CellValue("FileName", "Sheetname", "ColumnHeaderCell")

If it doesn't you know the problem is in the document, or in your reference to the document.

If it does extract the name, you can re-use that as input for your code to see if it works /to check what is wrong

[Anonymous]

Thank you for your reply.

I've tried doing that like this:

a = GoExcel.CellValue("\\BASIC-SRV\Inventor\GostolTST_AI\iLogic POLŽ\iLogic_OBSTOJEČE_POLŽEVKE_FI_200.xlsx", "ČEP FI 45_35", "B2")
MessageBox.Show(a, "Title")

The message returned the cell's title as string, which is what I wanted, but when I inserted a GoExcel.Close in front, like this:

GoExcel.Close

a = GoExcel.CellValue("\\BASIC-SRV\Inventor\GostolTST_AI\iLogic POLŽ\iLogic_OBSTOJEČE_POLŽEVKE_FI_200.xlsx", "ČEP FI 45_35", "B2")
MessageBox.Show(a, "Title") 

 I received this error:

Error in rule: Rule7, in document: POLŽ FI200_88_test.iam

Overload resolution failed because no Public 'Show' is most specific for these arguments:
'Public Shared Function Show(text As String, caption As String) As System.Windows.Forms.DialogResult':
Not most specific.
'Public Shared Function Show(owner As System.Windows.Forms.IWin32Window, text As String) As System.Windows.Forms.DialogResult':
Not most specific.

I appreciate any additional help.

Regards.

[Daan_M]

I don't fully understand why you would close your active worksheet and then try to search something in it after you have closed it. I think this is not going to work because you basically shut the door for the API to get into your document.

What is the reason you want to close your active worksheet?

[Anonymous]

I have multiple sub-assemblies in my main assembly, which need to access the same excel file. Each of those sub-assemblies either read or write to the excel file which makes it necessary to close the file after each file has written to it, otherwise I get a message saying that the excel file is already in use (which results in either an error or the excel file doesn't get written to). The GoExcel.Close in this case only simulates one of the sub-assemblies closing the excel file.

To better understand what my whole iLogic code is all about:

The code checks the excel file for existing screw conveyor lengths and their corresponding file names. If it finds a suitable one, it replaces it with the part or the assembly in inventor. If it doesn't find a suitable one, it starts creating a new one with the template being one of the parts/assemblies in the main assembly. It then saves the length of the new screw conveyor and the new file name in the excel file, so the next time the code reads from the excel, a new file is available.

If multiple new parts/assemblies need to be created, each of the template assemblies need to access the excel file.

I hope it's understandable.

Is there a workaround?

[Daan_M]

I think you need to streamline your code's workflow.

If you want to read and write multiple times to your excelfile you need to make sure this happens in the right order.

You can use GoExcel.Open and GoExcel.Close multiple times aswell, this shouldnt be a problem.

Keep in mind you can't look up or write variables in a closed document.

1. Before you want to read or write to the document, use GoExcel.Open

2. After youre finished reading or writing to the document, use GoExcel.Close

Repeat step 1 and 2 for the next sub assembly, make sure step 1 and 2 do not run simaltaniously. So avoid running rules at the same time in multiple documents.

[Anonymous]

Yeah, that makes sense. My rules in sub-assemblies all run one after another, one sub-assembly at a time. After all the rules in one sub-assembly finish, the code returns to the rule in the main assembly, from where it either goes to the next sub-assembly or runs until the end of code.

The thing that boggles my mind is, why using GoExcel.Close and then GoExcel.Open and then GoExcel.CellValues doesn't work, whereas using GoExcel.CellValues without using GoExcel.Open to start with works just fine.

(For some reason, I can't seem to paste the code here, cause it automatically deletes the reply.)

To sum up... I need to close the excel in order for the code in either the main rule or in sub-assembly rules to read from or write to the excel file without getting the message about the file being in use.

[Daan_M]

For me the code below works fine, which does the  'GoExcel.Close and then GoExcel.Open and then GoExcel.CellValue(s)'. Make sure you're not mixing up GoExcel.CellValue and GoExcel.CellValues either.

Sub main
	
oExLoc = "C:\Data\ExDoc.xlsx"
Dim oSheet As String = "Sheet1"
Dim CellValue As String

GoExcel.Open(oExLoc, oSheet)
CellValue = GoExcel.CellValue(oExLoc, oSheet, "A1")
MsgBox(CellValue)
GoExcel.Close

GoExcel.Open(oExLoc, oSheet)
CellValue = GoExcel.CellValue(oExLoc, oSheet, "A2")
MsgBox(CellValue)
GoExcel.Close

End Sub

[Anonymous]

Yes, your code also works for me. However, if I add an additional piece of code after that, I get an error "could not match column title".

Dolžina_Obstoječa_ND_L = GoExcel.CellValues("\\BASIC-SRV\Inventor\GostolTST_AI\iLogic POLŽ\iLogic_OBSTOJEČE_POLŽEVKE_FI_200.xlsx", "NOTRANJA DESNO", "B3", "B1000")


oExLoc = "\\BASIC-SRV\Inventor\GostolTST_AI\iLogic POLŽ\iLogic_OBSTOJEČE_POLŽEVKE_FI_200.xlsx"
Dim oSheet As String = "NOTRANJA DESNO"
Dim CellValue As String

GoExcel.Open(oExLoc, oSheet)
CellValue = GoExcel.CellValue(oExLoc, oSheet, "B2")
MsgBox(CellValue)
GoExcel.Close

GoExcel.Open(oExLoc, oSheet)

Polževka_ND_dolžina = 2225

GoExcel.TitleRow = 2 
b = GoExcel.FindRow(oExLoc, oSheet, "DOLŽINA_L [mm]", "=", Polževka_ND_dolžina)
Polževka_ND = GoExcel.CellValue(oExLoc, oSheet, "A" & b)
MessageBox.Show(Polževka_ND_dolžina, "Title")
MessageBox.Show(b, "Title")
MessageBox.Show(Polževka_ND, "Title")	

GoExcel.Close   

Is there a way you could try it with the excel file attached in the first post?

[Daan_M]

Strange, your code executes fine for me, i get all the titles in the MsgBox...

Maybe it has to do with a special characters? 

Excel document settings?

Its not something iLogic related, i dont think i can help further than this

[Anonymous]

I tried changing the characters, but it still doesn't work. I'll keep trying to find the solution.

Thank you for taking your time to help. 🙂

Best regards.

[Anonymous]

@Daan_M 

Hello again.

I've been trying to solve the problem for some time now and there's this one solution that kind of works.

After closing the excel file, it seems that I can't immediately reopen it.

So I tried opening another excel file, closing the new file and then try to access the first file again. This worked, but I have no idea why opening another file would allow me to access the first again.

If you have any ideas as to why this works and if this could help find a real solution to the original problem, I would appreciate any help.