Find and Open Excel File in Folder

[eric.frissell26WKQ]

I'm curious if anyone knows a method to open an excel document from a partial string in a given directory?  

Example say you have all of your project folders on a drive, inside your project folders you have project documents, and you're specifically looking for Project File - (JobNumber) where JobNumber is simply the job number (except it varies from job to job, of course).  If you didn't know the job number but knew the "Project File" keyword do not change, any idea how you would open it?

[A.Acheson]

Do you have a sample file path string that shows the project file and job number?  Also indicate if the location will always be different. If the excel document is relative to an assembly/part file then that can be used as a location marker. 

[Curtis_Waguespack]

Hi @eric.frissell26WKQ 

Here is an example that might work for you.

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

Sub Main
	Dim oWord As String = "123"
	SearchThroughFolders("C:\Temp\", oWord)
End Sub

Sub SearchThroughFolders( oFolder As String, oSearchWord As String)
	Dim oSubDirectories As String() = System.IO.Directory.GetDirectories(oFolder)
	Dim oFiles As String() = System.IO.Directory.GetFiles(oFolder,"*.xls")

	For i = 0 To oSubDirectories.Length - 1
		SearchThroughFolders(oSubDirectories(i), oSearchWord)
	Next
	
	For i = 0 To oFiles.Length - 1
		If oFiles(i).ToUpper().Contains(oSearchWord.ToUpper()) Then
			ThisDoc.Launch(oFiles(i))
		End If
	Next
End Sub

[Michael.Navara]

This is a shorter version mentioned by @Curtis_Waguespack 

Dim topDirectory = "C:\Temp"
Dim searchPattern = "Project File - (*).*"

Dim files = System.IO.Directory.EnumerateFiles(topDirectory, searchPattern, System.IO.SearchOption.AllDirectories)
For Each file In files
	Logger.Debug(File)
Next

[eric.frissell26WKQ]

Thanks guys!  Really appreciating the help!  Granted I was hoping I wouldn't have to do this but... tried both methods and struggling to get either to work.  Perhaps you guys might be able to see if I made an error in implementing your methods so here's the code in it's entirety so far.

Currently intent is to;

set a path to the project folder (DocPath) as a parameter so that it can be called at a later date, and do the same to the engineering path which is what holds the engineering documents. 

Have this iLogic rule find the document containing the word Sizing, open it (silent, not silent - both are okay), and adjust some values based on what's contained in the file.

Currently the parameters are being set however using either of the two methods are not returning anything - the SearchThroughFolders isn't opening the file and using the directory nothing is printing to the logger 

Were these set up right?  Been going through and trying a few different ways of getting them to work but haven't been able to return anything

Sub Main()
Dim DocPath As String

On Error Resume Next
DocPath = Parameter("ProjectFolder")

If DocPath = "" Then Search(DocPath)
	'DocParam = InputBox("Set Project Folder Directory.  If there are sub-directories prior to project information then use sub directory")
	'AddPathParam = ParamSet.AddByValue("ProjectFolder", DocParam, UnitsTypeEnum.kTextUnits)
EngineeringPath = Parameter(EngineeringPath)
Dim topDirectory = DocPath
Dim searchPattern = "*Sizing*"

Dim files = System.IO.Directory.EnumerateFiles(topDirectory, searchPattern, System.IO.SearchOption.AllDirectories)
For Each file In files
	Logger.Debug(File)
Next

End Sub	

' Set document paths and create parameters
Sub Search(DocPath As String)
	' Create param
	ParamSet = ThisApplication.ActiveDocument.ComponentDefinition.Parameters.UserParameters
	DocParam = InputBox("Set Project Folder Directory")
	AddPathParam = ParamSet.AddByValue("ProjectFolder", DocParam, UnitsTypeEnum.kTextUnits)
	' Set Path
	DocPath = Parameter("ProjectFolder")
	If Right(DocPath, 0) <> "\" Then
		DocPath = DocPath & "\"
	End If
	
	' Create engineering path
	EngineeringPath = DocPath & "Engineering\"
		'MsgBox(EngineeringPath)
	AddPathParam = ParamSet.AddByValue("EngineeringPath", EngineeringPath, UnitsTypeEnum.kTextUnits)
		
	Dim oWord As String = "Sizing"
	'Search2(EngineeringPath, oWord)
	'SearchThroughFolders(EngineeringPath, oWord)
End Sub

'
' Find the files

Sub SearchThroughFolders( DocPath As String, oWord As String)
	Dim oSubDirectories As String() = System.IO.Directory.GetDirectories(EngineeringPath)
	Dim oFiles As String() = System.IO.Directory.GetFiles(DocPath, "*.xlsx")
	

	For i = 0 To oSubDirectories.Length - 1
		SearchThroughFolders(oSubDirectories(i), oWord)
	Next
	
	For i = 0 To oFiles.Length - 1
		If oFiles(i).ToUpper().Contains(oWord.ToUpper()) Then
			ThisDoc.Launch(oFiles(i))
		End If
	Next
End Sub

Sub Search2(EngineeringPath As String, oWord As String)
	Dim topDirectory = EngineeringPath
Dim searchPattern = "*" & oWord & "*.*"

Dim files = System.IO.Directory.EnumerateFiles(topDirectory, searchPattern, System.IO.SearchOption.AllDirectories)
For Each file In files
	Logger.Debug(File)
Next

End Sub

[Michael.Navara]

I look at your code later, but first thing.

NEVER use On Error Resume Next in iLogic rules. This is an obsolete method for error handling. Use Try-Catch blocks instead.

[eric.frissell26WKQ]

Just circling back to this in case anyone can see the error in the code above

Try -catch block seems like a difficult way of implementing the on error resume next.  Mind if I ask why try-catch is better?  Was using On Error to get through the initial object variable not being set and tried to switch it to try-catch but it kept failing, though I'll admit that may be the first try-catch I tried to implement.

DocPath = Parameter("ProjectFolder")

Would it be appropriate to do something like

DocPath = Parameter("ProjectFolder")
If Err then GoTo ... 

[Michael.Navara]

Unstructured error handling is not recommended by Microsoft.

How to implement Try-Catch-Finally block is described here.

Usually you don't want to continue, when some unexpected exception occurs. You need to fix them first and then you can continue. Otherwise you can get unexpected behavior of your code.

[Curtis_Waguespack · Accepted solution]

Hi @eric.frissell26WKQ,

As currently written you only enter the sub called "Search" if DocPath is nothing. So if DocPath is not nothing, it never steps into that sub, and therefore never searches for the file or opens it is found.

That might be the intent, but I wanted to point that out in case it was not.

In any case, here is an example that should be closer to what you are wanting.

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

Sub Main

	' get or create param	
	oParamName = "ProjectFolder"
	Call CheckOrCreateParam(oParamName)

	'get user input
	DocPath = InputBox("Set Project Folder Directory", "iLogic", Parameter(oParamName))
	If DocPath = "" Then Return
		
	'set param value
	Parameter(oParamName) = DocPath

	If Right(DocPath, 0) <> "\" Then
		DocPath = DocPath & "\"
	End If

	' get or create param	
	oParamName = "EngineeringPath"
	Call CheckOrCreateParam(oParamName)

	'set param value
	Parameter(oParamName) = DocPath & "Engineering\"

	oFile = SearchThroughFolders(Parameter(oParamName), "Sizing")
	If oFile = "" Then : MsgBox("No file found.", , "iLogic") : Exit Sub : End If

	Try : GoExcel.Open(oFile, "Sheet1") : _
	Catch : MsgBox("could not open Excel file.", , "iLogic") : Exit Sub : End Try
	
	oCellValue = GoExcel.CellValue("A1")
	MsgBox("Cell value is: " & oCellValue,, oFile)

End Sub


Function CheckOrCreateParam(oName)

	ParamSet = ThisApplication.ActiveDocument.ComponentDefinition.Parameters.UserParameters
	Try
		'get param value
		oTest = Parameter(oName)
	Catch
		'create if not found
		ParamSet.AddByValue(oName, "", UnitsTypeEnum.kTextUnits)
	End Try
	InventorVb.DocumentUpdate()
End Function

' Find the files
Function SearchThroughFolders(oFolder As String, oWord As String)
	Dim oFiles As String() = System.IO.Directory.GetFiles(oFolder, "*.xlsx")

	For i = 0 To oFiles.Length - 1
		If oFiles(i).ToUpper().Contains(oWord.ToUpper()) Then
			Return oFiles(i)
		End If
	Next
End Function

[eric.frissell26WKQ]

Thanks Curtis!  You're right about the file search function - I would like to run the file location function once and have it store the values, then be able to run the entire sub again and and have it update so it wouldn't have done that the way it was written.

I'll be trying this soon and thanks a ton for writing this out, gonna have a lot to learn from it.

[eric.frissell26WKQ]

While I've got you here, hopefully you wouldn't mind a quick question?  I'm not very familiar with try catch methods and I'm trying to understand some behavior...  Your code worked however the I made a few edits in the first few lines and was trying to get the code to skip the inputbox if it found the parameter.  The On Error Resume Next I thought was a really simple way to get through this however I understand that it's not the greatest way to handle errors.  If that was converted to a try catch method I'd think it'd look somewhat similar to the code below, except it's not running that way

Is the right way to think about a try-catch method like;

Do X  <- the try

If you can do X go on
If you can't do X then do Y <- the catch

Sub Main()
	
	Dim DocPath As String
	
	' Check for parameter
	Try : DocPath = Parameter(ProjectFolder)
		If DocPath <> ""
			MsgBox(DocPath)
		End If
	Catch : ' didn't find parameter - go to create parameter
		oParamName = "ProjectFolder"
		Call CheckOrCreateParam(oParamName) : End Try

	' Get user input for parameter
	DocPath = InputBox("Set Project Folder Directory.  Leave blank to exit", "iLogic", Parameter(oParamName))
	If DocPath = "" Then Exit Sub
	' Set param value
	Parameter(oParamName) = DocPath

	If Right(DocPath, 0) <> "\" Then
		DocPath = DocPath & "\"
	End If

[Curtis_Waguespack]

Hi @eric.frissell26WKQ,

The problem with using On Error Resume Next (OERN) on it's own, is that is can hide errors and make debugging a terror when the code just doesn't work, but it doesn't tell us it didn't work because the OERN is silencing the errors.

I still do it from time to time, but it's generally considered poor practice. 

If you are going to use it, it should be used with a proper error catch. You can Google "VBA error handlers" and find a bunch of info on that.

The other thing to know is that you can not use OERN in the same code as Try/Catch statements. And Try/Catch statements are really useful.

With all of that said. I did mix a lot of syntactical try/catch stuff in my previous reply, so let me see if I can clear things up. 

These 4 do the same thing:

1) This one catches and handles the error separately from the remedy action, which is in the Finally.

Try 'try to get the value
	otest = Parameter(oParamName)
Catch 'catch the error when the param isn't found
	MsgBox("Param not found")
Finally
	Call CheckOrCreateParam(oParamName)
End Try

2) This one catches and handles the error in step with the remedy action.

Try 'try to get the value
	otest = Parameter(oParamName)
Catch 'catch the error when the param isn't found
	MsgBox("Param not found")
	Call CheckOrCreateParam(oParamName)
End Try

3) This one catches and handles the error in step with the remedy action, but all in one line.

Try: otest = Parameter(oParamName): Catch: MsgBox("Param not found"): Call CheckOrCreateParam(oParamName): End Try

4) this one is doing the same thing, but is just using the colons to combine the Catch and End Try lines

Try: otest = Parameter(oParamName)
Catch: 	MsgBox("Param not found"): Call CheckOrCreateParam(oParamName):End Try

Or if no message box is needed, we could do this:

Try 'try to get the value
	otest = Parameter(oParamName)
Catch 'catch the error when the param isn't found
	Call CheckOrCreateParam(oParamName)
End Try

or this one liner does the same as the last one:

Try: otest = Parameter(oParamName): Catch: Call CheckOrCreateParam(oParamName): End Try

Typically though you'll see this:

Try
	' do something here
Catch 
	'catch the error when the something didn't work
End Try

Note too that you can catch the error message and hand that back to the user like this:

Try
	' do something here
Catch ex As Exception
	'catch the error when the something didn't work
	Messagebox(ex.Message) 
Finally
	'do the remedy for the something that didn't work
End Try
	

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