iLogic Links to Excel 2016

[Anonymous]

HI All,

I have been using Excel data links between Excel and Inventor for over the past year on a version of Excel 2010.

I have just installed the new office 365 with Excel 2016 and now the script is running into a few issues just opening the Excel file.

The script has much more code to it than this but it can't seem to get past this initial step.

It gives back the error "PN.xlsx is already open...... Do you want to reopen it?"

I have already checked to make sure no Excel32.exe is running in the background..  Any ideas?

Section = Left(Parameter("SECTION"),1) 'Type (mechanical, Fluid)
Struct = Left(Parameter("STRUCTURE"),1) 'Assembly or Component

'define the file to create/open
Worksheet = "Z:\CAD\DESIGN ENVIRONMENT\iLogic\PN.xlsx"
Sheetno = ("Sheet1")


'check for existing file 
If Dir(Worksheet) <> "" Then
'workbook exists, open it
GoExcel.Open(Worksheet, Sheetno)

' Define Range
RowStart = 2
RowEnd = 10000

'Find the first empty cell
For count = RowStart To RowEnd
	' If it's blank count it
	If String.IsNullOrEmpty(GoExcel.CellValue("C" & count)) Then 
	' Row Number is equal to the current row
		rowN = count
		'Exit For loop
		Exit For
	End If
Next

[Owner2229]

Hi, you can try this code below. In your code you're basically looking for first empty row betwen rows 2 and 10000. This code will look for first row in unlimited amount of rows.

Sub Main()
Section = Left(Parameter("SECTION"),1) 'Type (mechanical, Fluid)
Struct = Left(Parameter("STRUCTURE"),1) 'Assembly or Component

'define the file to create/open
Worksheet = "Z:\CAD\DESIGN ENVIRONMENT\iLogic\PN.xlsx"
Sheetno = ("Sheet1")

'check for existing file 
If Dir(Worksheet) <> "" Then
	'if workbook exists, open it
	GoExcel.Open(Worksheet, Sheetno)
Else
	Exit Sub
End If

RowEnd = 100
For countA = 2 To (RowEnd * 0.01) + 2
    If Not String.IsNullOrEmpty(GoExcel.CellValue("C" & RowEnd)) Then
        RowEnd = RowEnd + 100
    Else
        Exit For
    End If
Next

For rowPN = (RowEnd - 100) To RowEnd
    If String.IsNullOrEmpty(GoExcel.CellValue("C" & RowPN)) Then
	rowN = rowPN
	Exit For
    End If
Next
End Sub

[Anonymous]

Hi there,

Thanks for the response, that is a better method!
However, the problem I think is coming out of the GoExcel.Open method.

Could this be anything to do with the new version of excel, it seems to work perfectly with excel 2010?

[Owner2229 · Accepted solution]

Hi, it could be problem of the new Excel. I have Inventor 2016 and Excel 2013 and "GoExcel.Open" works well. However you can try if this code below would work. It should visibly open your Excel table.

Worksheet = "Z:\CAD\DESIGN ENVIRONMENT\iLogic\PN.xlsx"
excelApp = CreateObject("Excel.Application")
excelApp.Visible = True
excelWorkbook = excelApp.Workbooks.Open(Worksheet)
excelSheet = excelWorkbook.Worksheets(1).activate

The other problem may be in the rest of your code. After you found first empty row, then you're writing something in there, right? The point is that your rule must look like this:

1) Open Excel table with "GoExcel.Open..."

2) Find empty row

3) Write something in it e.g.:

GoExcel.CellValue("C" & rowN) = iProperties.Value("Project", "Part Number")

4) Save and close Excel table:

GoExcel.Save
GoExcel.Close

[Anonymous]

Hi Owner,

I tried this through the code, ensuring the GoExcel.Save & Close methods were used wherever a refernce to excel is made.

This seems to have made the fix! Funny this didn't show in the Excel 2010 version!

[Owner2229]

You're welcomed.

Btw. you dont need to save and close after each write, just at the end of the rule, like below. (Just in case you didnt know. Because saving, closing and re-opening the table for each write will make your rule quite slow).

GoExcel.Open("Z:\CAD\DESIGN ENVIRONMENT\iLogic\PN.xlsx", "Sheet1")
GoExcel.CellValue("C1") = iProperties.Value("Project", "Part Number")
GoExcel.CellValue("C2") = iProperties.Value("Project", "Part Number")
'...
GoExcel.Save
GoExcel.Close

You can also add this "first empty row finder" to your code, so it would look like below. It's not limited by 10000 rows like your original rule and it will jump through filled rows a bit faster (by hundrets exactly).

Sub Main()
    Section = Left(Parameter("SECTION"),1) 'Type (mechanical, Fluid)
    Struct = Left(Parameter("STRUCTURE"),1) 'Assembly or Component
    Worksheet = "Z:\CAD\DESIGN ENVIRONMENT\iLogic\PN.xlsx"
    Sheetno = ("Sheet1")

    If Dir(Worksheet) <> "" Then
        GoExcel.Open(Worksheet, Sheetno)
    Else
        Exit Sub
    End If

    RowStart = 2
    RowEnd = 100
    For countA = RowStart To (RowEnd * 0.01) + RowStart
        If Not String.IsNullOrEmpty(GoExcel.CellValue("C" & RowEnd)) Then
            RowEnd = RowEnd + 100
        Else
            Exit For
        End If
    Next

    If RowStart > (RowEnd - 100) Then RowS2 = RowStart Else RowS2 = (RowEnd - 100)
    For rowPN = RowS2 To RowEnd
        If String.IsNullOrEmpty(GoExcel.CellValue("C" & RowPN)) Then
	    rowN = rowPN
	    Exit For
        End If
    Next

    GoExcel.CellValue("C" & rowN) = iProperties.Value("Project", "Part Number")
    '...

    GoExcel.Save
    GoExcel.Close
End Sub 

[Anonymous]

Hi,

Thanks again for your help.

I have inserted the code (see below). I was getting confused as the code references between Inventor and Excel as it reads/writes information into excel I thought I would need to reopen it every time it was referenced.

However, I have noticed that it now keeps two versions of the EXCEL.EXE*32 open in the task manager. Should the Goexcel.Close command prevent this?

Section = Left(Parameter("SECTION"),1) 'Type (mechanical, Fluid)
Struct = Left(Parameter("STRUCTURE"),1) 'Assembly or Component

'define the file to create/open
Worksheet = "Z:\CAD\DESIGN ENVIRONMENT\iLogic\PN.xlsx"
Sheetno = ("Sheet1")

'define Excel Application object
excelApp = CreateObject("Excel.Application")
'set Excel to run visibly, change to false if you want to run it invisibly
excelApp.Visible = False
'suppress prompts (such as the compatibility checker)
excelApp.DisplayAlerts = False


'check for existing file 
If Dir(Worksheet) <> "" Then
'workbook exists, open it
GoExcel.Open(Worksheet, Sheetno)


' Define Range
RowStart = 2
RowEnd = 10000

'Find the first empty cell
For count = RowStart To RowEnd
	' If it's blank count it
	If String.IsNullOrEmpty(GoExcel.CellValue("C" & count)) Then 
	' Row Number is equal to the current row
		rowN = count
		'Exit For loop
		Exit For
	End If
Next

'Check whether B1 in Excel has been updated
'ExcelCheck = GoExcel.CellValue("B1")
GoExcel.CellValue("A1") = Section & Struct  
'If GoExcel.CellValue("B1") <> ExcelCheck Then

iProperties.Value("Project", "Revision Number") = "A"
OldPartNumber = iProperties.Value("Project", "Part Number")
	   
'Show user the next row number which is blank
MessageBox.Show("Inserting Data on Row " & rowN, "Updating Database...")
'Store Old part Number
NewPartNumber = GoExcel.CellValue("B1")
'Update iProperties
iProperties.Value("Project", "Part Number") = NewPartNumber
Filename = GoExcel.CellValue("B1") & iProperties.Value("Project", "Revision Number")& "_" & iProperties.Value("Project", "Description")
iProperties.Value("Summary", "Title") = Filename
GoExcel.CellValue("C" & RowN) = GoExcel.CellValue("B1")
GoExcel.CellValue("D" & RowN) = iProperties.Value("Project", "Revision Number")
GoExcel.CellValue("E" & RowN) = iProperties.Value("Project", "Description")
GoExcel.CellValue("F" & RowN) = Filename
iProperties.Value("Project", "Designer") = iProperties.Value("Summary", "Author")

MessageBox.Show("Your Unique Part Number is " & Filename, "Updating Database...")
'GoExcel.Save
'GoExcel.Close

'define the active document
oDoc = ThisDoc.Document
'create a file dialog box
Dim oFileDlg2 As inventor.FileDialog = Nothing
InventorVb.Application.CreateFileDialog(oFileDlg2)


'check file type and set dialog filter
If oDoc.DocumentType = kPartDocumentObject Then
oFileDlg2.Filter = "Autodesk Inventor Part Files (*.ipt)|*.ipt"
Else If oDoc.DocumentType = kAssemblyDocumentObject Then
oFileDlg2.Filter = "Autodesk Inventor Assembly Files (*.iam)|*.iam"
Else If oDoc.DocumentType = kDrawingDocumentObject Then
oFileDlg2.Filter = "Autodesk Inventor Drawing Files (*.idw)|*.idw"
End If


'set the directory to open the dialog at
oFileDlg2.InitialDirectory = ThisDoc.WorkspacePath()
'set the file name string to use in the input box
oFileDlg2.FileName = Filename


'work with an error created by the user backing out of the save 
oFileDlg2.CancelError = True
On Error Resume Next
'specify the file dialog as a save dialog (rather than a open dialog)
oFileDlg2.ShowSave()


'Catch an empty String in the imput
If Err.Number <> 0 Then
MessageBox.Show("No File Saved.", "iLogic: Dialog Canceled")

'Enter Code which removes last entry from Spreadsheet
'GoExcel.Open(Worksheet, Sheetno)
For count = RowStart To RowEnd
	' If it's blank count it
	If String.IsNullOrEmpty(GoExcel.CellValue("C" & count)) Then 
	' Last entry was on bottom row minus 1
		rowN = count -1
		
		'Exit For loop
		Exit For
	End If
Next

iProperties.Value("Project", "Part Number") = OldPartNumber
iProperties.Value("Summary", "Title") = iProperties.Value("Project", "Description")
MessageBox.Show("No File Saved." &vbLf & "Removing Last Entry From Database...")
GoExcel.CellValue("C" & RowN) = ""
GoExcel.CellValue("D" & RowN) = ""
GoExcel.CellValue("E" & RowN) = ""
GoExcel.CellValue("F" & RowN) = ""


ElseIf oFileDlg2.FileName <> "" Then
MyFile = oFileDlg2.FileName
'save the file 
oDoc.SaveAs(MyFile, False) 'True = Save As Copy & False = Save As
End If

            
Else 'Else statement for if excel dir(worksheet) = ""
MessageBox.Show("Excel Failed to Open." &vbLf & "Ensure Engineering Root is mapped to Z:/", "Error Handler")
End If

GoExcel.Save
GoExcel.Close


iLogicVb.UpdateWhenDone = True

[Anonymous]

Ah! This is because of the excelapp object.

I need to set the object to Nothing. I will work on this code and see if I can get a result.

[Anonymous]

Aha!

This may be possibly two solutions.

I noticed on another post another user with the same issue. (See link below) He noticed that the model requires updating before the excel object would close correctly. Rearrange the iLogicupdatewhendone function to above the Goexcel.Close seems to have fixed the problem!

http://forums.autodesk.com/t5/inventor-general-discussion/linked-spreadsheet-and-ilogic/td-p/5774280

I have however left in the excelapp.quit and excelapp = Nothing additions.

This seems to work! I will let you know if everything continues to run  smoothly.

I have yet to implement the code which finds the last row regardless of count, the above code didnt work the first time, maybe there is a slight mistake??

iLogicVb.UpdateWhenDone = True

GoExcel.Save
GoExcel.Close

excelapp.quit
excelapp = Nothing

[Owner2229]

Hi, here you go. There is no need to use "excelApp" when you're using "GoExcel". Read the descriptions in code and let me know if you'll want me to code the numbering for you.

Section = Left(Parameter("SECTION"),1) 'Type (mechanical, Fluid)
Struct = Left(Parameter("STRUCTURE"),1) 'Assembly or Component

'define the file to create/open
'No need to have it maped as "Z:", you can use the absolute server adress to server, e.g. "\\dc\...\PN.xlsx"
'Ask your IT if you don't know the server adress
Worksheet = "Z:\CAD\DESIGN ENVIRONMENT\iLogic\PN.xlsx"
Sheetno = ("Sheet1")

'check for existing file 
If Dir(Worksheet) <> "" Then
'workbook exists, open it
GoExcel.Open(Worksheet, Sheetno)

' Define Range. It doesn't matter now, it will fill the rows up to infinity now
RowStart = 2
RowEnd = 100

'Find the first empty cell
For countA = RowStart To (RowEnd * 0.01) + RowStart
    'If it's not blank jump to cell + 100. E.g. search cell C2 and then C102 etc.
    If Not String.IsNullOrEmpty(GoExcel.CellValue("C" & RowEnd)) Then
        'Add another 100 to searched cells
        RowEnd = RowEnd + 100
    Else
        'Else if it's empty exit loop
        Exit For
    End If
Next

'We want to search for empty rows just in last 100 rows, but we don't want to search in rows under "RowStart"
If RowStart > (RowEnd - 100) Then RowS2 = RowStart Else RowS2 = (RowEnd - 100)

'Find the first empty cell
For RowPN = RowS2 To RowEnd
    'If it's blank count it
    If String.IsNullOrEmpty(GoExcel.CellValue("C" & RowPN)) Then 
        'Row Number is equal to the current row
        rowN = RowPN
        'Exit loop
        Exit For
    End If
Next

GoExcel.CellValue("A1") = Section & Struct  

iProperties.Value("Project", "Revision Number") = "A"
OldPartNumber = iProperties.Value("Project", "Part Number")
	   
'Show user the next row number which is blank
MessageBox.Show("Inserting Data on Row " & rowN, "Updating Database...")
'Store Old part Number
NewPartNumber = GoExcel.CellValue("B1")
'Update iProperties
iProperties.Value("Project", "Part Number") = NewPartNumber
Filename = GoExcel.CellValue("B1") & iProperties.Value("Project", "Revision Number")& "_" & iProperties.Value("Project", "Description")
iProperties.Value("Summary", "Title") = Filename
GoExcel.CellValue("C" & RowN) = GoExcel.CellValue("B1")
GoExcel.CellValue("D" & RowN) = iProperties.Value("Project", "Revision Number")
GoExcel.CellValue("E" & RowN) = iProperties.Value("Project", "Description")
GoExcel.CellValue("F" & RowN) = Filename
iProperties.Value("Project", "Designer") = iProperties.Value("Summary", "Author")

MessageBox.Show("Your Unique Part Number is " & Filename, "Updating Database...")

'define the active document
oDoc = ThisDoc.Document
'create a file dialog box
Dim oFileDlg2 As inventor.FileDialog = Nothing
InventorVb.Application.CreateFileDialog(oFileDlg2)

'check file type and set dialog filter
If oDoc.DocumentType = kPartDocumentObject Then
    oFileDlg2.Filter = "Autodesk Inventor Part Files (*.ipt)|*.ipt"
Else If oDoc.DocumentType = kAssemblyDocumentObject Then
    oFileDlg2.Filter = "Autodesk Inventor Assembly Files (*.iam)|*.iam"
Else If oDoc.DocumentType = kDrawingDocumentObject Then
    oFileDlg2.Filter = "Autodesk Inventor Drawing Files (*.idw)|*.idw"
End If

'set the directory to open the dialog at
oFileDlg2.InitialDirectory = ThisDoc.WorkspacePath()
'set the file name string to use in the input box
oFileDlg2.FileName = Filename

'work with an error created by the user backing out of the save 
oFileDlg2.CancelError = True
On Error Resume Next
'specify the file dialog as a save dialog (rather than a open dialog)
oFileDlg2.ShowSave()

'Catch an empty String in the imput
If Err.Number <> 0 Then
    MessageBox.Show("No File Saved.", "iLogic: Dialog Canceled")
    'Enter Code which removes last entry from Spreadsheet
    'We already have the "RowN" so there is no need to look for it again
    iProperties.Value("Project", "Part Number") = OldPartNumber
    iProperties.Value("Summary", "Title") = iProperties.Value("Project", "Description")
    MessageBox.Show("No File Saved." &vbLf & "Removing Last Entry From Database...")
    GoExcel.CellValue("C" & RowN) = ""
    GoExcel.CellValue("D" & RowN) = ""
    GoExcel.CellValue("E" & RowN) = ""
    GoExcel.CellValue("F" & RowN) = ""
ElseIf oFileDlg2.FileName <> "" Then
    MyFile = oFileDlg2.FileName
    'save the file 
    oDoc.SaveAs(MyFile, False) 'True = Save As Copy & False = Save As
	
    'I dont know what does your part numbering look like, so you have to modify it here...
    'I can give you some other numbering examples if you'll want me to, or make it exactly for your own
    Dim NewB1 As String = GoExcel.CellValue("B1")
    Dim LenNr As Integer = 4 'sets how many symbols are defining the numbering, e.g. PAX0001 = last 4 symbols
    Dim NewNr As String = Val(Right(NewB1, LenNr)) + 1 'New number will be highter by 1 so e.g. PAX0001 > PAX0002
    'as we extracted the number from the numbering symbols, there are probably missing all the zeros we had there, so we have to place them back
    If Len(NewNr) < 2 Then
        NewNr = "000" & NewNr
    Else If Len(NewNr) < 3 Then
        NewNr = "00" & NewNr
    Else If Len(NewNr) < 4 Then
        NewNr = "0" & NewNr
    End If
    GoExcel.CellValue("B1") = Left(NewB1, Len(NewB1) - LenNr) & NewNr
End If

GoExcel.Save
GoExcel.Close

Else
    MessageBox.Show("Excel Failed to Open." &vbLf & "Ensure Engineering Root is mapped to Z:/", "Error Handler")
End If

iLogicVb.UpdateWhenDone = True