Change Excel link for all .ipt and .iam in folder

[Anonymous]

Hi everyone!

Can anyone guide me how to change the Excel link in every .ipt and .iam file in a folder?

I need to change the Excel in a lot of files, and I know I can do it using

changeOK = GoExcel.ChangeSourceOfLinked(oldFileName, newFileName)

But how do i loop through all of the files in a folder? I can't seem to find the right line of code to do it. 

Thanks in advance!

P.S.: if anyone also knows how to obtain the current Excel link in order for me to create a condition that if oldFileName = newFileName, it ignores and moves on to next folder would be greatly appreciated as well!

[Sergio.D.Suárez]

Hi, this is an ilogic rule, I have not tried it, but I guess it could be useful, you should specify the correct names of your excel template above.

Imports System.Windows.Forms

Dim oldFileName As String = ""
Dim newFileName As String = ""

Dim DocDir As String = ThisDoc.Path
Dim Dialog = New FolderBrowserDialog()

Dialog.SelectedPath = DocDir
Dialog.ShowNewFolderButton = True
Dialog.Description = "Select Folder"

If DialogResult.OK = Dialog.ShowDialog() Then
	
	DocDir = Dialog.SelectedPath & "\"

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object


'Create an instance of the FileSystemObject
objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
objFolder = objFSO.GetFolder(DocDir)

	For Each objFile In objFolder.Files
		oFullFilename = DocDir  & objFile.Name
		Dim oDoc As Inventor.Document
		oDoc = ThisApplication.Documents.Open(oFullFilename, False)
		Try
		changeOK = GoExcel.ChangeSourceOfLinked(oldFileName, newFileName)
		oDoc.Save
		Catch
		End Try
		oDoc.Close
	Next
End If

[Anonymous]

Hola, Sérgio

Appreciate you taking the time, and thank you for the rule. Unfortunately, it fails with 2 errors.

1st:

Error in rule: Test, in document: Unknown Document

Unspecified error (Exception from HRESULT: 0x80004005 (E_FAIL))

And

2nd:

Unhandled exception has occurred in a component in your application. If you click Continue, the application will ignore this error and attempt to continue.

Catastrophic failure (Exception from HRESULT: 0x8000FFFF (E_UNEXPECTED))

[Sergio.D.Suárez]

Imports System.Windows.Forms

Dim oldFileName As String = ""
Dim newFileName As String = ""

Dim DocDir As String = ThisDoc.Path

Dim Dialog = New FolderBrowserDialog()

Dialog.SelectedPath = DocDir
Dialog.ShowNewFolderButton = True
Dialog.Description = "Select Folder"

If DialogResult.OK = Dialog.ShowDialog() Then

	DocDir = Dialog.SelectedPath & "\"

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object

'Create an instance of the FileSystemObject
objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
objFolder = objFSO.GetFolder(DocDir)

	For Each objFile In objFolder.Files

		Dim oExtension As String = Right(objFile.Name, Len(objFile.Name) - InStrRev(objFile.Name, "."))

		If oExtension = "ipt" Or oExtension = "iam" Then

			oFullFilename = DocDir & objFile.Name
			Dim oDoc As Document
			oDoc = ThisApplication.Documents.Open(oFullFilename, False)
			Try
			changeOK = GoExcel.ChangeSourceOfLinked(oldFileName, newFileName)
			oDoc.Save
			Catch
			End Try
			If oDoc.DisplayName <> ThisDoc.Document.DisplayName Then
				oDoc.Close
			End If
		End If
	Next
End If

 Sorry, I tweak the code a bit more, surely in the folder there are other files that are not inventor, so it gives an error.
Here I make sure that the files are iam and ipt, and I close all those that open in false mode with exception of the active document, I hope I can help your problem. regards

[Anonymous]

Hi Sergio!

Appreciate the help. This time, it does not give an error, but when I open the files they are still with the old Excel file.

A little context:

These were files that were copied from another folder using the "iLogic Design Copy". If I open the files, it opens that pop-up to change the Excel file. Do you think that is what's causing the error? When I open the files in the background, it may cause a conflict?

[Sergio.D.Suárez]

I have not tried the command to change the excel template for another. However, in order for the program to make these changes, you must have the template defined at the beginning, otherwise I suppose you can not replace something that is missing by default. It would be good to try to do a test and comment on the results, or even to put the file in true to see what the routine does. regards

[Anonymous]

Hi Sergio

I think I probably explained myself wrong.

The logic of what I'm trying to do is automate a template that will work for several projects. Even though the parts will all be the same, with the same parameters, they will have different parameters values.

So, in order to change the values, I want to replace with a different excel file, that's just the same as the "oldFile" but with different values. And I want to change the Excel link of all parts.

Right now, what I'm doing is copying all the files from a folder A to folder B.

In folder A they are linked to A.xlsm

In folder B, I want to replace the link from "A.xlsm" to "B.xlsm" in all files, automatically.

The problem with this, if doing one by one, is that appears the "resolve link" window. I think this window is what is keeping the code from working. Meaning, I have to find an ilogic code that skips this window AND THEN runs the rest of the code.

[Sergio.D.Suárez]

Try to do the following, copy your original excel file and paste it into the folder that you generated the design copy (do not change the name to the excel file). When you open each part you do not have to ask for the link to the excel file that you can not find, because it is in the same folder where the part or assembly file is.

Now copy the excel file and change the name, in the design copy folder, you should have two files, the copy of the original with the same name as the original that is in another place, and the new copy of excel file with a new name
Now you can run the rule and wait for the path of the files to change.
If it works, now you should delete the excel copy file of the same name as the original. regards

[Anonymous]

Hi Sergio!

I did as you suggested AND if I keep the name of the Excel file, I don't have a problem, it keeps working just fine.

However, I have to change the name, since its name will depend on the contracts we're working.

I also tried again running the rule, after changing the name, but to no avail. I'm convinced it is because of the message "Resolve link" that appears when he doesn't find the 3rd party file it's linked to.

[Sergio.D.Suárez]

Hi, as I never try that ilogic replacement tool I give you my code version.
Notice that add some lines in replacement of the ilogic snippet (red).
In order for it to work, the names of the excel files must be correctly located, otherwise it will return error.
I have tried the code with an example and it has worked well for me.
I hope you find the code useful. Best regards!

Imports System.Windows.Forms
Dim OldExcelFile As String = "C:\Users\Sergio\Desktop\LibroA.xlsx"
Dim NewExcelFile As String = "C:\Users\Sergio\Desktop\LibroB.xlsx"

Dim DocDir As String = ThisDoc.Path

Dim Dialog = New FolderBrowserDialog()

Dialog.SelectedPath = DocDir
Dialog.ShowNewFolderButton = True
Dialog.Description = "Select Folder"

If DialogResult.OK = Dialog.ShowDialog() Then

	DocDir = Dialog.SelectedPath & "\"

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object

'Create an instance of the FileSystemObject
objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
objFolder = objFSO.GetFolder(DocDir)

	For Each objFile In objFolder.Files

		Dim oExtension As String = Right(objFile.Name, Len(objFile.Name) - InStrRev(objFile.Name, "."))

		If oExtension = "ipt" Or oExtension = "iam" Then

			oFullFilename = DocDir & objFile.Name
			Dim oDoc As Document
			oDoc = ThisApplication.Documents.Open(oFullFilename, False)
			
				Dim oReferencedFile As Inventor.ReferencedOLEFileDescriptor = Nothing
				If oDoc.ReferencedOLEFileDescriptors.Count > 0 Then
					For Each oReferencedFile In oDoc.ReferencedOLEFileDescriptors
						If oReferencedFile.FullFileName = OldExcelFile Then
						    oReferencedFile.FileDescriptor.ReplaceReference(NewExcelFile)
						End If
					Next
				End If
				oDoc.Update2(True)
				oDoc.Save
				
			If oDoc.DisplayName <> ThisDoc.Document.DisplayName Then
				oDoc.Close
			End If
		End If
	Next
End If

[Anonymous]

Hi Sergio!

Thanks again!

This time, there's another error:

"Object reference not set to an instance of an object."

"System.NullReferenceException: Object reference not set to an instance of an object.
at LmiRuleScript.Main()
at Autodesk.iLogic.Exec.AppDomExec.ExecRuleInAssembly(Assembly assem)
at iLogic.RuleEvalContainer.ExecRuleEval(String execRule)"

I have about 20 files (between ipt and iam) and not all of them are linked to a file and don't need to be (but if it makes it easier, they can be). 

I noticed something strange, however! In Tools>links, in all of the files I noticed that it created a link for the file I want to update to! The new file is called "teste.xlsx" and in the links it appears "teste1.xlsx" and are obviously unresolved. I don't know why the "1" was added. Does it make sense to you?

[Anonymous]

Hi Sergio

I noticed after the 1st reply that you changed the snippet and aren't using the "changeOK" variable. So I had the "oldExcelFile" poorly written.

After getting that right I still have an error though:

"The parameter is incorrect (Exception from HRESULT: 0x80070057 (E_INVALIDARG))"

"System.ArgumentException: O parâmetro está incorreto. (Exception from HRESULT: 0x80070057 (E_INVALIDARG))
at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Inventor.FileDescriptor.ReplaceReference(String FullFileName)
at LmiRuleScript.Main()
at Autodesk.iLogic.Exec.AppDomExec.ExecRuleInAssembly(Assembly assem)
at iLogic.RuleEvalContainer.ExecRuleEval(String execRule)"