Error writing to excel

[Thomas.Long]

I have a program that opens an excel file with a number of parameters and iterates through the list, opening a database and checking if the database has a part that matches that description. If it does, it checks if the C drive contains that part. Parts that don't exist get added to a list for the user to make. Parts that do exist but aren't on the c drive get added to a "getter" string to copy into vault.

If the part exists, I'd like to write to the excel file the path. A second rule can then be run that takes the orientation and location and inputs the part and auto constrains it into my assembly.

Everything works except the writing portion. Originally this was all one rule and I wasn't checking the c drive for the part. This meant that you'd have to run the program once, it would tell you all parts missing from your c drive, you'd have to delete everything you just added out and do the get, then run it again.

By adding the writing portion, it meant I wouldn't have to re- search for every part, I could just run down the list and add them in. Unfortunately, while the program has no issue reading from 2 separate files, it has issue writing to a file, as it keeps telling me the excel sheet is already open and if I open it again it will lose all my changes for every time I write. How do I tell it to write without reopening the file each time?

(Let me know if you need to see the individual functions. Errors are occurring on lines 64 & 68)

Thank you

Sub Main
	Dim FileName As String = "C:\TEMP\Coverplate Information.xlsx"
	Dim LookupFile As String = "N:\Mechpart\TLONG\Parts Lists\INVENTOR COVER PLATES.xlsx"
	
	Dim row As Integer = 1
	
	Dim coverPlateType As String
	Dim length As Double
	Dim width As Double
	Dim overhang As Double
	
	Dim material As String
	Dim plateName As String
	Dim plateNumber As Integer = 1
	
	Dim path As String
	Dim partNumber As String
	Dim coverPlateList As New ArrayList
	Dim unFoundPlates As New ArrayList
	Dim getPlates As New ArrayList
	
	Dim coverPlatesFound As Boolean = True
	Dim allFilesFound As Boolean = True
	Dim unfoundPlateAdded As Boolean
	Dim getPlateAdded As Boolean
	
	GoExcel.Open(FileName, "Sheet2")

	While Not GoExcel.CellValue(FileName, "Sheet2", "A" & row) = ""
	
		'Set plate parameters
		coverPlateType = GoExcel.CellValue(FileName, "Sheet2", "A" & row)
		path = ""
		partNumber = ""
		
		If coverPlateType = "SURFACE" Then
			overhang = .75
			material = Parameter("CoverPlateThickness")
		Else
			material = "1/4 AL"
			overhang = .9375
		End If
		
		length = GoExcel.CellValue(FileName, "Sheet2", "B" & row) + 2 * overhang
		width = GoExcel.CellValue(FileName, "Sheet2", "C" & row) + 2 * overhang
		
		SortLengthWidth(length, width)

		'See if new plate has already been added
		path = CheckFoundPlatesAddedForPath(coverPlateList, material, length, width, coverPlateType)
		
		If path = "" Then
		
			i = GoExcel.FindRow(LookupFile, "Sheet1", "Length", "=", length, "Width", "=", width, "Material", "=", material, "Type", "=", coverPlateType)

			If Not i = -1 Then
				 path = GoExcel.CurrentRowValue("Location")
				 partNumber = GoExcel.CurrentRowValue("Part Number")
				coverPlateList.Add(New CoverPlate(material, coverPlateType, length, width, path, partNumber ))
			End If
		End If
		
		'File has been created and is on C drive. Write location to excel sheet so program may go pick it up for insertion
		If Not path = "" And System.IO.File.Exists(path) Then 
			GoExcel.CellValue(FileName, "Sheet2", "G" & row) = path
			
		'File has been created but not on C drive. Check if in getter array and if not add it
		Else If Not path = "" And Not System.IO.File.Exists(path)
			GoExcel.CellValue(FileName, "Sheet2", "G" & row) = path
			getPlateAdded = CheckGetPlatesAdded(getPlates, partNumber)
			
			If getPlateAdded = False Then getPlates.Add(partNumber)
			allFilesFound = False
			
		'File has not been created. Check if plate is in the unfound plates array and if not add it
		Else
			unfoundPlateAdded = CheckUnfoundPlatesAdded(unFoundPlates, material, length, width, coverPlateType)
		
			If unfoundPlateAdded = False Then unFoundPlates.Add(New CoverPlate(material, coverPlateType, length, width, path, GoExcel.CurrentRowValue("Part Number")))
			coverPlatesFound = False
		End If
		
		plateNumber = plateNumber + 1
		row = row + 1
	End While
	
	'Show cover plates that need to be made
	If coverPlatesFound = False Then ShowCoverPlatesNeededMsg(unFoundPlates)
	
	'Show cover plates that need a getter
	If allFilesFound = False Then ShowGetPlatesMsg(getPlates)
	
	GoExcel.Save()
	GoExcel.Close()

End Sub

[Thomas.Long]

Reply;

So I was able to resolve this with a workaround, though I'd rather not have to. 

I closed and saved the file every time I wrote to it and that resolved the issue. In terms of performance not horrible but also not optimal. I'd still prefer a real resolution, if anyone could help.

Thank you,

[WCrihfield]

Hi @Thomas.Long.  Any time we use the uniquely iLogic 'GoExcel' tools to interact with Excel, we have few options as to how we do those things, but not much control over creating new Excel documents to start working within.  Since your code is already using the GoExcel.Open() method, where you included the excel file, and sheet name, you should not need to keep including those in the following 'GoExcel' lines of code.  Doing so may be part of the problem.

So for example, after that point, instead of using the CellValue(String, String, String) property like this:

GoExcel.CellValue(FileName, "Sheet2", "A" & row)

...you could just be using the CellValue(String) property, like this:

GoExcel.CellValue("A" & row)

...instead.  That may it will seek that information from the already opened file and sheet, without attempting to re-open them.

However, any 'edits' or changes you make within the Excel file using GoExcel will not actually effect that file until you use the GoExcel.Save() method.  Doing that writes the temporary working data that is being held in memory to the file.  After that you may or may not want to use the GoExcel.Close method, but certainly would not need to use the GoExcel.QuitApplication method (which you are not doing right now).

If you skip using the Close method, then you would likely also want to skip using the Open method, and just keep including the file and sheet specification within every GoExcel line of code.  Doing that may increase processing time though, not sure.

[Thomas.Long]

My issue is on line 54 -58 you can see that I'm referencing a different file (the database file). If I stop specifying the filename and sheet name will it accidentally pull from there instead?

Usually I don't use the open or close at all, I skip them and just specify GoExcel.CellValue. I wish the open had the capability to return the excel file so that I could pass it into a variable and tell it to reference that variable until I specifically closed that instance, rather than having to hope that inventor was using the correct version in the background! Then I could simply have a variable specifically for the lookup file and one for the database file.