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
Solved! Go to Solution.
Solved by rossano_praderi. Go to Solution.
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
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?
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!
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.
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!