Change current iLogic searching for Excel file

[BK-MTFNE]

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")

[WCrihfield]

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

[BK-MTFNE]

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.

[Curtis_Waguespack]

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")

[Curtis_Waguespack]

---

@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!

---

[BK-MTFNE]

@Curtis_Waguespack 

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

[WCrihfield · Accepted solution]

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)

[Curtis_Waguespack · Accepted solution]

@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

[BK-MTFNE]

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

[BK-MTFNE]

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