topic Re: Change current iLogic searching for Excel file in Inventor Programming Forum

Change current iLogic searching for Excel file

BK-MTFNE — Fri, 16 Jun 2023 17:26:23 GMT

I currently have an iLogic that searches for an Excel file and pulls information from it to place in custom properties. This iLogic was created when we used a seperate .ipj file for every project so it would search for that specific .ipj and find the associated Excel file. Now we have one .ipj for multiple projects and the excel file is in each project folder (basically we have main project folders in the same directory as the overall .ipj file). I am trying to change the search part of this iLogic to search the current folder and keep searching in subsequent parent folders until the excel file is found.

Dim IPJ As String Dim IPJ_Name As String Dim IPJ_Path As String Dim FNamePos As Long 'set a reference to the FileLocations object. IPJ = ThisApplication.FileLocations.FileLocationsFile 'get the location of the last backslash seperator FNamePos = InStrRev(IPJ, "\", -1) 'get the project file name with the file extension IPJ_Name = Right(IPJ, Len(IPJ) - FNamePos) 'get the project name (without extension) IPJ_ShortName = Left(IPJ_Name, Len(IPJ_Name) - 4) iProperties.Value("Project", "Project") = IPJ_ShortName iProperties.Value("Custom", "PROJECT NUMBER") = GoExcel.CellValue("Project Information.xlsx", "Sheet1", "C8")

Re: Change current iLogic searching for Excel file

WCrihfield — Fri, 16 Jun 2023 17:56:56 GMT

What version of Inventor are you using? I do not recognize that "ThisApplication.FileLocations.FileLocationsFile" reference you are using. When I paste that into an iLogic rule, it has no Intellisense information for it, but it does return the full file name of my current project file.

Edit: This is what we normally use now:

IPJ = ThisApplication.DesignProjectManager.ActiveDesignProject.FullFileName

Re: Change current iLogic searching for Excel file

BK-MTFNE — Fri, 16 Jun 2023 17:59:12 GMT

I'm using inventor 2023, the iLogic was written in Inventor 2021. Honestly I am working on learning to write iLogics better so this was kind of piecmealed from other iLogics.

Re: Change current iLogic searching for Excel file

Curtis_W — Tue, 20 Jun 2023 13:06:06 GMT

Hi @BK-MTFNE ,

Give this a try. ( edited on 6.20 to include header Imports line)

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

Imports System.IO sTargetFileName = "Project Information.xlsx" sPath = IO.Path.GetDirectoryName(ThisApplication.DesignProjectManager.ActiveDesignProject.FullFileName) Dim oAllXecelFiles As IEnumerable(Of String) = Directory.EnumerateFiles(sPath, "*.xlsx?", SearchOption.AllDirectories) 'get the first excel file that matches the target file namae Dim oTargetExcelFile As String = oAllXecelFiles.FirstOrDefault(Function(f) f.IndexOf(sTargetFileName, StringComparison.OrdinalIgnoreCase) <> -1) iProperties.Value("Custom", "PROJECT NUMBER") = GoExcel.CellValue(oTargetExcelFile, "Sheet1", "C8")

Re: Change current iLogic searching for Excel file

Curtis_W — Fri, 16 Jun 2023 21:43:48 GMT

@WCrihfield wrote:

I do not recognize that "ThisApplication.FileLocations.FileLocationsFile" reference you are using.

I recognize that code 😬

http://inventortrenches.blogspot.com/2012/07/ilogic-and-inventor-project-file.html

I was definitely just trying to figure things out back then in 2012, and often doing things the hard way 🙄 🙂

Not sure why I used that line to get the project file... I probably found an old Inventor API post on here and just used what I found... there were almost no iLogic examples online back then, and the API posts were often pretty cryptic. This forum has come along way in the last decade or so!

Re: Change current iLogic searching for Excel file

BK-MTFNE — Tue, 20 Jun 2023 11:06:31 GMT

@Curtis_W

These are the errors I get when I put this in as a rule...

I get the directory is not declared often when doing iLogic

Re: Change current iLogic searching for Excel file

WCrihfield — Tue, 20 Jun 2023 11:22:48 GMT

If you have not included "Imports System.IO" in the 'Header' of the rule, it will want you to include "System.IO" before both Directory and SeachOption. (System.IO.Directory.EnumerateFiles()) & (System.IO.SearchOption.AllDirectories)

Re: Change current iLogic searching for Excel file

Curtis_W — Tue, 20 Jun 2023 13:17:04 GMT

@BK-MTFNE

oops! I forgot to include the header line in my example. ( I'll update my example above in case it helps someone in the future).

Give this update a try, and post back if it is not doing what you expect.

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

As WCrihfield mentioned you can edit the code and add an Imports line to line 1 as shown:

and/or you can add Sytem.IO in two places in line 4:

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

Re: Change current iLogic searching for Excel file

BK-MTFNE — Tue, 20 Jun 2023 13:04:54 GMT

Thank you @Curtis_W for the code and thank you @WCrihfield for helping with that little correction to make it work right

Re: Change current iLogic searching for Excel file

BK-MTFNE — Tue, 25 Jul 2023 16:48:38 GMT

So I thought this was good to go but now that its been implimented I see that its searching through directories in the "wrong" direction. Ill do my best to show our folder structure here to help better understand

Master Folder (holds every project folder and the main .ipj file)
- Project 1 folder
  - Project Information Excel file (there is an excel file in each project folder)
  - Assembly folder 1
    - all inventor items for assembly 1 (these need to reference the excel file in the main project folder)
  - Assembly folder 2
    - all inventor items for assembly 2 (these need to reference the excel file in the main project folder)
  - there may be some inventor files that are in this folder as well that need to reference the same excel file.
- Project 2 folder
  - Project Information Excel file (there is an excel file in each project folder)
  - Assembly folder 1
    - all inventor items for assembly 1 (these need to reference the excel file in the main project folder)
  - Assembly folder 2
    - all inventor items for assembly 2 (these need to reference the excel file in the main project folder)
  - there may be some inventor files that are in this folder as well that need to reference the same excel file.
- Project 3 folder
  - Project Information Excel file (there is an excel file in each project folder)
  - Assembly folder 1
    - all inventor items for assembly 1 (these need to reference the excel file in the main project folder)
  - Assembly folder 2
    - all inventor items for assembly 2 (these need to reference the excel file in the main project folder)
  - there may be some inventor files that are in this folder as well that need to reference the same excel file.
- .ipj file

Currently the iLogic above is searching in the "Master Folder" starting with the first project it finds and using that excel sheet where it actually needs to start in the current assembly folder its in and work backwards from there.

Basically the file with the iLogic in it is like a child looking for candy (excel sheet), it first ask its parents (folder file is saved in) if they have candy. They dont have it so then it goes to the grand parents (folder that contains the folder that the child is saved in). it keeps going until someone gives it the candy (excel file) its looking for. sorry for the weird analogy but it was the best way i could think to word it