Community
Inventor Programming - iLogic, Macros, AddIns & Apprentice
Inventor iLogic, Macros, AddIns & Apprentice Forum. Share your knowledge, ask questions, and explore popular Inventor topics related to programming, creating add-ins, macros, working with the API or creating iLogic tools.
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Excel sheet not found: "Sheet1"

6 REPLIES 6
SOLVED
Reply
Message 1 of 7
andrewb
1284 Views, 6 Replies

Excel sheet not found: "Sheet1"

I recently finished up a rather long rule in order to export my iProperty values into an excel spreadsheet. However, the first time that I run the rule in every new ".itp", I get the following error:

 

Error in rule: RuleName, in document: FileName.ipt

Excel sheet not found: "Sheet1"

 

However, when I run the rule a second time, it works exactly like it's supposed to. It only seems to give this error on every odd number of attempts to run the rule, then works fine on the even number of attempts.
What's really killing me is the fact that nowhere in the lines of code is there a single instance of anything referring to "Sheet1"
I would like nothing more than to get this running without a hitch, but I cannot for the life of me figure out why it is giving me this error.

Is there anyone that could help me out on this?

 

(I'm only posting a portion of the code, seeing as how the majority is copy/paste with a name change for different pages in the spreadsheet)

 

Sub Main
iLogicForm.Show("iProperty Update")
'Open iProperty form
Call CallData
'Run sub to find correct sheet for data input
End Sub

Sub CallData
GoExcel.Open("K:\Riley Vault\New Riley Part Log.xlsx")
'Open Excel file
MySheet = (Left(iProperties.Value("Project", "Part Number"), 2))
'Locate correct sheet for data unput
If MySheet = ("AN") Then
Call AN_Value
ElseIf MySheet = ("BM") Then 
Call BM_Value
ElseIf MySheet = ("CH") Then
Call CH_Value
ElseIf MySheet = ("EM") Then
Call EM_Value
ElseIf MySheet = ("FB") Then
Call EM_Value
ElseIf MySheet = ("GS") Then
Call GS_Value
ElseIf MySheet = ("PL") Then
Call PL_Value
ElseIf MySheet = ("PG") Then
Call PG_Value
ElseIf MySheet = ("SH") Then
Call SH_Value
ElseIf MySheet = ("RT") Then
Call RT_Value
ElseIf MySheet = ("SP") Then
Call SP_Value
ElseIf MySheet = ("ST") Then
Call ST_Value
ElseIf MySheet = ("UH") Then
Call UH_Value
ElseIf MySheet = ("UR") Then
Call UR_Value
End If
End Sub

Sub AN_Value
SheetNo = ("Angle")
PartNo = iProperties.Value("Project", "Part Number")
GoExcel.Open("K:\Riley Vault\New Riley Part Log.xlsx", SheetNo)
GoExcel.TitleRow = 1
GoExcel.FindRowStart = 3
i = GoExcel.FindRow("New Riley Part Log.xlsx", SheetNo, "New DWG #", "=", iProperties.Value("Project", "Part Number"))
If i >= 3 Then
MsgBox("Inserting Data on Row " & i)
GoExcel.CellValue("D" & i) = iProperties.Value("Summary", "Title") 
GoExcel.CellValue("E" & i) = iProperties.Value("Summary", "Subject")
GoExcel.CellValue("F" & i) = iProperties.Value("Summary", "Category")
GoExcel.CellValue("G" & i) = iProperties.Value("Summary", "Keywords")
GoExcel.CellValue("A" & i) = iProperties.Value("Project", "Part Number")
GoExcel.CellValue("C" & i) = iProperties.Value("Project", "Stock Number")
GoExcel.CellValue("H" & i) = iProperties.Value("Project", "Description")
GoExcel.CellValue("K" & i) = iProperties.Value("Project", "Revision Number")
GoExcel.CellValue("J" & i) = iProperties.Value("Project", "Creation Date")
GoExcel.CellValue("I" & i) = iProperties.Value("Summary", "Author")
GoExcel.CellValue("B" & i) = iProperties.Value("Custom", "Old DWG #")
GoExcel.Save
ElseIf MsgBox("No DWG Number Found. Click 'OK' to proceed, and please wait.") Then
Call AN_NewRow
End If
 End Sub

Sub AN_NewRow
 Worksheet = ("K:\Riley Vault\New Riley Part Log.xlsx")
 SheetNo = ("Angle")
 GoExcel.Open(Worksheet, SheetNo)
 RowStart = 3
 RowEnd = 10000
 For count = RowStart To RowEnd
 If String.IsNullOrEmpty(GoExcel.CellValue("A" & count)) Then
 i = i + 1
 End If
 Next
 row = RowEnd - i + 1
MsgBox("Inserting Data on Row " &row)
GoExcel.CellValue("D" & row) = iProperties.Value("Summary", "Title") 
GoExcel.CellValue("E" & row) = iProperties.Value("Summary", "Subject")
GoExcel.CellValue("F" & row) = iProperties.Value("Summary", "Category")
GoExcel.CellValue("G" & row) = iProperties.Value("Summary", "Keywords")
GoExcel.CellValue("A" & row) = iProperties.Value("Project", "Part Number")
GoExcel.CellValue("C" & row) = iProperties.Value("Project", "Stock Number")
GoExcel.CellValue("H" & row) = iProperties.Value("Project", "Description")
GoExcel.CellValue("K" & i) = iProperties.Value("Project", "Revision Number")
GoExcel.CellValue("J" & i) = iProperties.Value("Project", "Creation Date")
GoExcel.CellValue("I" & i) = iProperties.Value("Summary", "Author")
GoExcel.CellValue("B" & i) = iProperties.Value("Custom", "Old DWG #")
GoExcel.Save
End Sub 

 

6 REPLIES 6
Message 2 of 7
rossano_praderi
in reply to: andrewb

Hi Andrew,

I'm supposing that the rest of your code will be right, the only visible error is in your subroutine "CallData".
By using the "GoExcel.Open" routine without the sheet name, it will look for the first default sheet ("Sheet1").

 

Sub CallData
GoExcel.Open("K:\Riley Vault\New Riley Part Log.xlsx")
'Open Excel file

 



--------------------------------------
If my post answers your question, please click the "Accept as Solution"
button. This helps everyone find answers more quickly!
---------------
Message 3 of 7
andrewb
in reply to: rossano_praderi

Thanks for the response!
I have been looking into what you've said, and I'm trying to find a way that it would automatically look up the correct sheet based off of the other subs. Is that even a thing that I could do, or would I need to write an entirely new series into the "CallData" sub in order to do this?

Message 4 of 7
andrewb
in reply to: andrewb

I took your idea of the Sheet1 thing to the point where I simply added a new sheet by the same name to the spreadsheet. It seemed to fix all of the issues. Apparently it's just looking to see if the sheet exists before running the rest of the rule. Thanks again!

Message 5 of 7
rossano_praderi
in reply to: andrewb

Hi Andrew,

sorry me for the late response, now I try to be more clear.
In your code you try to open the Excel without the sheet name, this will take the default sheet name such as "Sheet1".
But...you don't need to open the Excel document in the "CallData" routine this because you open the same Excel workbook in each SubRoutine...look the following remarks on your code...

 

Sub Main
iLogicForm.Show("iProperty Update")
'Open iProperty form
Call CallData
'Run sub to find correct sheet for data input
End Sub

Sub CallData
GoExcel.Open("K:\Riley Vault\New Riley Part Log.xlsx") 'what you want to do with this line?
'Open Excel file 'Why?

...with this part of your code you are assigning variables and calling subroutines
...you never use the excel document directly or indirectly
...then in this routine you don't need to open the document
End Sub Sub AN_Value 'now you open the excel document SheetNo = ("Angle") PartNo = iProperties.Value("Project", "Part Number") GoExcel.Open("K:\Riley Vault\New Riley Part Log.xlsx", SheetNo)
....blablabla....
GoExcel.Save ElseIf MsgBox("No DWG Number Found. Click 'OK' to proceed, and please wait.") Then Call AN_NewRow 'now you call this subroutine and you open the excal document again End If End Sub Sub AN_NewRow 'now you open the excel document Worksheet = ("K:\Riley Vault\New Riley Part Log.xlsx") SheetNo = ("Angle") GoExcel.Open(Worksheet, SheetNo)
....blablabla....
GoExcel.Save End Sub

 

 

If you want to check if the Excel document exist this is not the rigth way.

 



--------------------------------------
If my post answers your question, please click the "Accept as Solution"
button. This helps everyone find answers more quickly!
---------------
Message 6 of 7
andrewb
in reply to: rossano_praderi

I'm fairly new to the world of VB (I actually started learning it for this very code) so I haven't learned all the nuances of the coding quite yet.
That being said: Your reccomendation/notes got me to a place where the rule runs flawlessly.

Thanks again!

Message 7 of 7
rossano_praderi
in reply to: andrewb

You are welcome!
If you need help with your code or if you need suggestions, don't hesitate to ask on this community or send me a private message with your code.


--------------------------------------
If my post answers your question, please click the "Accept as Solution"
button. This helps everyone find answers more quickly!
---------------

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Autodesk Design & Make Report