Open excel which is in the same folder as an assembly

[tomislav.peran]

Hello,

I would like to open the excell file, via rule of course,  but without specifying the path. 

I will keep an excell sheet in the same folder as my assembly. 

I think this is possible so if anyone knows please let me know.

Tom

[basautomationservices]

Will you know the filename of the excel? Then it is possible. For example:

Dim filename As String = "test.xlsx"
Dim thisFilename As String = ThisDoc.Document.FullFileName
Dim folder As String = System.IO.Path.GetDirectoryName(thisFilename)
Dim files As List(Of String) = System.IO.Directory.GetFiles(folder).ToList()
Dim excelFile = files.FirstOrDefault(Function(x) System.IO.Path.GetFileName(x) = filename)

If excelFile <> Nothing Then
	GoExcel.Open(excelFile)
End If 

You could also look for all files with the extension .xlsx, but in this case I assumed you would know the name of the excel, here "test.xlsx". I noticed that in inventor 2021, using the GoExcel errors if there is not a sheet called Sheet1 in the workbook.

[tomislav.peran]

Hi basautomationservices,

Thanks for your reply. I tried the code but it does not open it. The only thing that works for me now is if I enter location like here:

Dim objExcel = CreateObject("Excel.Application")
Dim objWorkbook = objExcel.Workbooks.Open("Location\Tom_Excel.xlsx")
objExcel.Application.Visible = True

 Anything else does not seem to work,

Tom

[basautomationservices]

The application probably opens but is not visible:

Dim filename As String = "test.xlsx"
Dim thisFilename As String = ThisDoc.Document.FullFileName
Dim folder As String = System.IO.Path.GetDirectoryName(thisFilename)
Dim files As List(Of String) = System.IO.Directory.GetFiles(folder).ToList()
Dim excelFile = files.FirstOrDefault(Function(x) System.IO.Path.GetFileName(x) = filename)

If excelFile <> Nothing Then
	GoExcel.Open(excelFile, "Sheet1")
	excelApp = GoExcel.Application
	excelApp.Visible =True
	GoExcel.CellValue("A1") = "Test"
	GoExcel.Save
	' GoExcel.Close
End If 

[tomislav.peran]

Hello,

I have entered the new code but then I get error on line 10.

De objectvariabele of With block-variabele is niet ingesteld. 

Translated: 

The object variable or With block variable is not set.

Unfortunatelly no luck as well,

Tom

[basautomationservices · Accepted solution]

If you just want to open the file and combine yours and mine you can use this. 

Dim filename As String = "test.xlsx"
Dim thisFilename As String = ThisDoc.Document.FullFileName
Dim folder As String = System.IO.Path.GetDirectoryName(thisFilename)
Dim files As List(Of String) = System.IO.Directory.GetFiles(folder).ToList()
Dim excelFile = files.FirstOrDefault(Function(x) System.IO.Path.GetFileName(x) = filename)

If excelFile <> Nothing Then
	Dim objExcel = CreateObject("Excel.Application")
	Dim objWorkbook = objExcel.Workbooks.Open(excelFile)
	objExcel.Application.Visible = True	
End If 

Do you also want to write/read cells from this sheet?

[tomislav.peran]

Hi Basautomation,

Yes, this works. Thank you a lot! 

Tom

[tomislav.peran]

"Do you also want to write/read cells from this sheet?"

Sorry, I did not see this question.  I write some parameters into it, yes, via my rules . So far that is going oke. 

But sometimes I have issues with excell document because it turns into "read only" by itself. I am not sure why but I think it has something to do with Inventor itself.

Tom