iLogic, open excel from iProperty

[Anonymous]

Hi,

I seem to be getting an error when running this code, its opens excel but the code errors out and opens the iLogic window again, any ideas? It runs fine up until the excelApp line.

Dim oDefault3 As String
oDefault3 = Parameter("DR_Location")

Dim oLocation3 As String

iProperties.Value("Custom", "DR_Location") = oLocation3

oLocation3 = iProperties.Value("Custom", "DR_Location")

If Dir(oLocation3)<>""

ThenGoExcel.Open(oLocation3, "DR_Location")

excelApp = CreateObject("Excel.Application")
excelApp.Visible = True
excelWorkbook = excelApp.Workbooks.Open(oLocation3)
excelSheet = excelWorkbook.Worksheets("Config").activate

RuleParametersOutput()
iLogicVb.UpdateWhenDone = True

End If

[Anonymous]

Hi,

I seem to be getting an error when running this code, its opens excel but the code errors out and opens the iLogic window again, any ideas? It runs fine up until the excelApp line.

Dim oDefault3 As String
oDefault3 = Parameter("DR_Location")

Dim oLocation3 As String

iProperties.Value("Custom", "DR_Location") = oLocation3

oLocation3 = iProperties.Value("Custom", "DR_Location")

If Dir(oLocation3)<>""

ThenGoExcel.Open(oLocation3, "DR_Location")

excelApp = CreateObject("Excel.Application")
excelApp.Visible = True
excelWorkbook = excelApp.Workbooks.Open(oLocation3)
excelSheet = excelWorkbook.Worksheets("Config").activate

RuleParametersOutput()
iLogicVb.UpdateWhenDone = True

End If

[WCrihfield]

Could you explain exactly what you are trying to do?

I assume the Parameter named "DR_Location" is a text type UserParameter, which contains the full path and file name (with extension) of the Excel file you are trying to open.  Is this correct?

Then it looks like you may be trying to ensure that your custom iProperty named "DR_Location" also contains that same full path and file name of the Excel file.  Is this correct?

Then you want to make sure this path string is not an empty string correct?

Then you want to make sure the file exists, before trying to open it correct?

Is the name of the target Tab or sheet within that Excel file "DR_Location" or is it "Config"?

[WCrihfield]

Could you explain exactly what you are trying to do?

I assume the Parameter named "DR_Location" is a text type UserParameter, which contains the full path and file name (with extension) of the Excel file you are trying to open.  Is this correct?

Then it looks like you may be trying to ensure that your custom iProperty named "DR_Location" also contains that same full path and file name of the Excel file.  Is this correct?

Then you want to make sure this path string is not an empty string correct?

Then you want to make sure the file exists, before trying to open it correct?

Is the name of the target Tab or sheet within that Excel file "DR_Location" or is it "Config"?

[WCrihfield · Accepted solution]

Would this scenario work for you?

• First it checks to see if that Parameter contains an empty string.
  • If it is empty, it lets you know and ends the rule
  • If it is not empty, it assumes the full path and file name are in it and assigns that value to the variable
• It then assigns the value of that variable to the custom iProperty named "DR_Location".
• It then checks to see if that file exists.
  • If the file does exist, it attempts to open that Excel file, and specifies the sheet to open it to as "Config", then tells it to display the alerts.
  • If the file doesn't exist, it lets you know, then exits the rule.
• I then put a comment line in there indicating where you would normally do something with the contents of the Excel file.
• I then show the proper way to save, close, and exit the Excel document/application.
• Then the correct line to cause the document to immediately update.

Dim oXLPath As String
If Parameter("DR_Location") = "" Then
	MsgBox("The value of the Parameter named ""DR_Location"" is an empty string. Exiting.", vbOKOnly, " ")
	Return
Else
	oXLPath = Parameter("DR_Location")
End If
iProperties.Value("Custom", "DR_Location") = oXLPath
If IO.File.Exists(oXLPath) Then
	GoExcel.Open(oXLPath, "Config")
	GoExcel.DisplayAlerts = True
Else
	MsgBox("That Excel file could not be found. Exiting.", vbOKOnly, " ")
	Return
End If

'Do what you need to do with the Excel file here
'Then

GoExcel.Save 'only if you need to save any changes made to it
GoExcel.Close
GoExcel.QuitApplication
RuleParametersOutput
iLogicVb.DocumentUpdate

	

If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click 'LIKE' 👍.

If you have time, please... Vote For My IDEAS :light_bulb:and Explore My CONTRIBUTIONS

Inventor 2020 Help | Inventor Forum | Inventor Customization Forum | Inventor Ideas Forum

[WCrihfield · Accepted solution]

Would this scenario work for you?

• First it checks to see if that Parameter contains an empty string.
  • If it is empty, it lets you know and ends the rule
  • If it is not empty, it assumes the full path and file name are in it and assigns that value to the variable
• It then assigns the value of that variable to the custom iProperty named "DR_Location".
• It then checks to see if that file exists.
  • If the file does exist, it attempts to open that Excel file, and specifies the sheet to open it to as "Config", then tells it to display the alerts.
  • If the file doesn't exist, it lets you know, then exits the rule.
• I then put a comment line in there indicating where you would normally do something with the contents of the Excel file.
• I then show the proper way to save, close, and exit the Excel document/application.
• Then the correct line to cause the document to immediately update.

Dim oXLPath As String
If Parameter("DR_Location") = "" Then
	MsgBox("The value of the Parameter named ""DR_Location"" is an empty string. Exiting.", vbOKOnly, " ")
	Return
Else
	oXLPath = Parameter("DR_Location")
End If
iProperties.Value("Custom", "DR_Location") = oXLPath
If IO.File.Exists(oXLPath) Then
	GoExcel.Open(oXLPath, "Config")
	GoExcel.DisplayAlerts = True
Else
	MsgBox("That Excel file could not be found. Exiting.", vbOKOnly, " ")
	Return
End If

'Do what you need to do with the Excel file here
'Then

GoExcel.Save 'only if you need to save any changes made to it
GoExcel.Close
GoExcel.QuitApplication
RuleParametersOutput
iLogicVb.DocumentUpdate

	

If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click 'LIKE' 👍.

If you have time, please... Vote For My IDEAS :light_bulb:and Explore My CONTRIBUTIONS

Inventor 2020 Help | Inventor Forum | Inventor Customization Forum | Inventor Ideas Forum

[WCrihfield]

There are multiple ways to open an Excel file, and multiple ways to work with the contents of an Excel spreadsheet.

If you need the application to become visible, there are settings for that.

If you need the application and/or document to remain open,  or either/both of them to be closed, after the rule has finished, there are ways to make that happen to.

Just let us know, in detail, what you are trying to do.

[WCrihfield]

There are multiple ways to open an Excel file, and multiple ways to work with the contents of an Excel spreadsheet.

If you need the application to become visible, there are settings for that.

If you need the application and/or document to remain open,  or either/both of them to be closed, after the rule has finished, there are ways to make that happen to.

Just let us know, in detail, what you are trying to do.

[Anonymous]

Hi,

That's for getting back to this post so quickly. I have quite limited knowledge of code / iLogic so this rule is an amalgamation of old snippets and bits of my own knowledge, its probably not the best way to do it im sure!

This is part of a rule which, exports a PDF to a location (with file name taken from iProperties and adds revision letter on the end), opens the PDF, then asks you if you would like to open an excel sheet (DR_Location) which is a drawing register.

The way I have done this as you stated is taking the PDF location and the drawing register location from an excel sheet which has the target locations in columns, populating these in user parameters, then populating these to iProperties and opening the file locations..... Seems like a very long winded way to do it but it kinda works!

Doesn't need to necessarily look for the empty string but might be helpful! The excel sheet its opening should always exist... in theory.

Yes the sheet tab name is "Config".

I think the rule you sent through may do exactly what I need, looks a lot cleaner that what I had!!!! As you have done, I just need it to open the excel sheet from the iProperty Value - DR_Location (which is the excel file location), probably alter one cell manually then save and close. I like what you have done with the proper close and save of excel in the rule.

I would like it to auto populate some cells in the future, but that will be further down the line I think, this will be great for now, ill trial it and hopefully will fit into the workflow. Will get back to you. Much appreciated. @WCrihfield 

CG

[Anonymous]

Hi,

That's for getting back to this post so quickly. I have quite limited knowledge of code / iLogic so this rule is an amalgamation of old snippets and bits of my own knowledge, its probably not the best way to do it im sure!

This is part of a rule which, exports a PDF to a location (with file name taken from iProperties and adds revision letter on the end), opens the PDF, then asks you if you would like to open an excel sheet (DR_Location) which is a drawing register.

The way I have done this as you stated is taking the PDF location and the drawing register location from an excel sheet which has the target locations in columns, populating these in user parameters, then populating these to iProperties and opening the file locations..... Seems like a very long winded way to do it but it kinda works!

Doesn't need to necessarily look for the empty string but might be helpful! The excel sheet its opening should always exist... in theory.

Yes the sheet tab name is "Config".

I think the rule you sent through may do exactly what I need, looks a lot cleaner that what I had!!!! As you have done, I just need it to open the excel sheet from the iProperty Value - DR_Location (which is the excel file location), probably alter one cell manually then save and close. I like what you have done with the proper close and save of excel in the rule.

I would like it to auto populate some cells in the future, but that will be further down the line I think, this will be great for now, ill trial it and hopefully will fit into the workflow. Will get back to you. Much appreciated. @WCrihfield 

CG