lrbehmer
Contributor
Joined 15 years ago
User Profile
User Widgets
Contributions
Re: Fun with Excel
Thanks so much guys, this was exactly the issue. Normally, we have been using Excel.Cells(i,j) and that worked - so that's what we used. For some reason, the loop I added changed the context and it started returning the Cell object within that IF statement, and it would always pass because the Cell object would never be "Null". Now, we will be explicitly using .Text to avoid issues, (at least, that's what I'll be pushing for ;-p). But this also points out more functionality to me and opens new avenues. Up until this point, I've been working with someone else's code and my brain doesn't question what someone else has written because I presume it was the correct/best/only solution. But issues like this and others I have been posting about have made me go back and really question what's already been written. Thanks guys!1.5KViews0likes0CommentsRe: Fun with Excel
Yeah - I'm looking at the method he posted now. At the least, this will help me troubleshoot, but your suggestion looks like the best solution. I'm keeping in mind that "empty" doesn't necessarily mean "Null". We are using the string "Null" as a value in some of these expected result cells. That's what the If statement is checking for. But the entire script stops running when it gets to the "var numOne" point. I'll also need to look at what .ClearContents leaves. Looks like those cells have data type of "9" when I log: Log.Message( i + ", " + j + " = " + GetVarType(Excel.Cells(i, j+24))) ; What does it mean when the cell is typed "VarDispatch"? For my own edification - how are you making those code blocks?? ;-p1.5KViews0likes0CommentsFun with Excel
Frankly, this is starting to look like a bug but I didn't want to jump the gun. I'm using a spreadsheet that has input and expected outputs in their respective columns. I'm taking the results from our application and writing them to another set of columns. The logic in the test compares the actual results with the expected results that are already in the spreadsheet. If they don't match, I change the color of the cell for the value in question. So, I have this bit of logic: if(Excel.Cells(i, j+24) == "Null") { Log.Message("Accecptable Error, test passed"); } else { var numOne = (aqConvert.StrToFloat(Excel.Cells(i, j+24))) ; } (i and j are defined beforehand. this code exists in a loop.) Everything goes fine until I add a loop beforehand that clears the contents of the cells for the new results: j = 2 ; // j = column for (i = 3; i<=17; i++) // i = row { columnCounter = 23 ; while(columnCounter<=26) { Excel.Cells(i, j+columnCounter).Interior.ColorIndex = 0 ; // Reset Cell background color to WHITE Excel.Cells(i, j+columnCounter).ClearContents ; // Clear Cell data columnCounter++ ; } } After this new loop is added to the beginning of the script, I get an error: "An exception occurred...The argument is not a number." right at the "numOne = aqConvert.StrToFloat()" spot. My question is - "Why am I getting this exception?" TIA.19KViews0likes7CommentsRe: Runtime Error opening Excel Spreadsheet
Ah. Yes, the F drive is a partition we're all using locally so our SVN mapping is the same. Thing is, we've been using this for several months and my machine is the only one experiencing the issue. I'll look into it. We've always closed Excel using the quit() or close() methods and haven't had any problems until now. I'll try out your suggestions and see what happens. I'd be willing to bet that the Excel process just hasn't fully closed before the next script runs. Thanks. -=[EDIT]=- Bingo. I added this bit of code before starting Excel again and it works like a charm. Might even solve an issue putting Excel operations into a Runtime Object not closing Excel. // Check for pre-existing Excel and kill it if it exists: var old_excel = Sys.WaitProcess("excel", 100) ; if(old_excel.Exists) { if (!old_excel.Close()) { Log.Warning("Excel was not closed successfully and will be terminated.") ; // Terminate Excel: old_excel.Terminate() ; } }6.4KViews1like1CommentRe: Runtime Error opening Excel Spreadsheet
Hi Jared, The exact error is: "An exception occurred in the 'Table_A_15C_Even' unit at line 6: The remote procedure call failed" Of course, "Table_A_15C_Even" is the script name. What's strange is that the error never occurs on the first try. When I start TestComplete, it will run through the entire project without issue. Subsequent runs will produce this error randomly. Each script in the project opens and closes Excel using the same code as in the first post. When I restart TestComplete, the problem goes away until the second run. When I run each script individually, and in order, the error does not occur. Another error I receive is this: "An exception occurred in the 'Table_A_15C_Even' unit at line 6: Microsoft JScript runtime error The remote server machine does not exist or is unavailable" And, again, stops at exactly the same spot.6.4KViews0likes0CommentsRuntime Error opening Excel Spreadsheet
When I run this code, I get intermittent runtime errors: var Excel ; Excel = Sys.OleObject("Excel.Application") ; Excel.Workbooks.Open("F:/my_spreadsheet.xlsx").Worksheets("Even Table").Activate ; I am guessing that it's because the Excel executable hasn't finished loading into memory yet. How can I wait for that? I've tried playing with sys.waitprocess, but I'm not getting anywhere.27KViews0likes12CommentsRe: Script Extensions and Scope
Yeah, there's at least one example of using multiple extensions I saw. BUT, I have switched gears a bit. I took the original global class and added an "Excel" member. I created the set and get routines and it works fine. It works ALMOST exactly as you described. I simply have to add "global." in front of my current Excel manipulations, whatever they may be. Everything is working fine, but now my problem is that I can't shut off the Excel process. So, I have this: var Excel ; function excel_state(state, path, worksheet) { if(state=='open') { Excel = Sys.OleObject("Excel.Application") ; Excel.Workbooks.Open(path).Worksheets(worksheet).Activate; } if(state=='close') { Excel.displayalerts = false; Excel.SaveWorkspace(); Excel.displayalerts = true ; Excel.quit(); //Sys.OleObject("Excel.Application").quit() ; } } // excel_state() Excel simply refuses to shut off whenever I perform the following in the main script OR in the excel_state() method: global.excel_state('close') ; Excel.quit() ; Sys.OleObject("Excel.Application").quit() ; Inside the excel_state() method, the first three statements DO execute. Is there anything else I should do? Should I write a loop to wait for it to die? Is there a project setting I have to change?1.7KViews0likes0Comments