Is this a bug or I lack of experience? (Excel - RB)

Luis · **Posted:** Sun Apr 21, 2013 6:57 pm

Hello,
Till last Friday, the following code was working great. No crashes, excellent performance.

dim excel As new ExcelApplication
dim book As ExcelWorkbook
dim sheet As ExcelWorksheet

'=======Add New Workbook=========
book=excel.workbooks.add

'======Risk Input Worksheet=============
excel.worksheets(1).name="Input"
excel.worksheets(1).Range ("A1").value="ID"                                      'Risk ID number, numeric
excel.worksheets(1).Range("A1").Select_
excel.worksheets(1).Range("A1").Addcomment
excel.worksheets(1).Range("A1").Comment.Text ("pippo")
excel.worksheets(1).Range("A1").Comment.Visible = False
excel.worksheets(1).Range("A2").Select_
excel.worksheets(1).Range("A2").Addcomment
excel.worksheets(1).Range("A2").Comment.Text ("Alberto")
excel.worksheets(1).Range("A2").Comment.Visible = False

excel.visible = true // Show the excel application

excel = Nil //Set the object to Nil
Exception err as OLEException // Check for an error
MsgBox err.message  // Display the error

Now, the only way I am able to see the "ID", "Comments", etc, after Excel opens is by moving the "excel.visible=True" up after "book=excel.workbooks.add". (Why?)

If I run the code as shown, Excel opens but that is all. No text in cell or comments.
If I move the "visible..." property up, it works.

For a few months I have been working in an application where the "visible" property was always placed as shown and had no problem at all. I based all my code after reading the excellent book from Mr. Eugene Dakin "Program Excel 2010 with REALStudio in Windows".

I did updated RB to the current revision 2012r2.1 last March, but it was working great since then till last week and no other changes or upgrades took place in my machine.

Is this a bug? Have anyone experienced this before? Why I need to change the location of a line of code to make it work, while it was working for many months when placed at the end of the code as shown? Am I missing anything?

The reason I placed the "visible.." property at the end is to gain efficiency by only presenting the open worksheet after it was all configured at once.

Thank you in advance for any input.
Luis.

eugenedakin · **Posted:** Sun Apr 28, 2013 10:17 pm

Hello Luis,

Hmmm.. I don't know what to say about the code. I copied your code and ran it on my Windows 8 machine with Real Software 2012 r2.1 and Office 2010 and it worked great!

If a restart of the computer does not change anything, then my only guess would be to possibly run 'Wise Registry Cleaner' (its free) to clean up the windows registry (make sure you do a backup first). Every once in a while I get an error and a reboot usually works. The reasons for this vary, and sometimes its Real Studio related, an update from Microsoft for the OS, or an update in Excel. Usually a reboot works because 'hot' updates are placed in the OS, and the reboot causes the changes to be more permanent. Although Microsoft says that a reboot is not required, it is still a good idea.

Let me know if you have any other thoughts. Happy to help,

Eugene

Luis · **Posted:** Wed May 01, 2013 5:30 pm

Thanks Eugene. I downloaded the registry cleaner you suggested. After running it, the code worked. I tried again and did not. It keeps working fine with the "Visible' command after adding a new book but as son as I move it to the end, as before, it stop working. I will keep investigating what is going on. FYI I was running RB2010r3.2 and XP SP3 with Excel XP. I updated to the latest version of RB and it worked OK as well but after that all at once stop working. What would happen if I removed Windows XP update during the last month of April? Uhmmm....I need to think about it. That is the only changed I am aware it happened.

Thank you again for your time,
Luis.

eugenedakin · **Posted:** Sat May 04, 2013 9:22 am

Hi Luis,

Yes, your right, the program shows the error when the excel.workbooks.add command is placed at the end of the code then there is an error. The reason is that the next command (putting text in the worksheet) needs to have an open worksheet page to write. If there is no open worksheet page, then there is no worksheet to name (you can't name something that doesn't exist). In this case, Real Studio is working as expected.

Do you get any other errors or is it working correctly now?

I hope this helps,

Eugene

dim excel As new ExcelApplication
dim book As ExcelWorkbook
dim sheet As ExcelWorksheet

'======Risk Input Worksheet=============
excel.worksheets(1).name="Input"
excel.worksheets(1).Range ("A1").value="ID"
excel.worksheets(1).Range("A1").Select_
excel.worksheets(1).Range("A1").Addcomment
excel.worksheets(1).Range("A1").Comment.Text ("pippo")
excel.worksheets(1).Range("A1").Comment.Visible = False
excel.worksheets(1).Range("A2").Select_
excel.worksheets(1).Range("A2").Addcomment
excel.worksheets(1).Range("A2").Comment.Text ("Alberto")
excel.worksheets(1).Range("A2").Comment.Visible = False

excel.visible = true // Show the excel application

'=======Add New Workbook=========
'THIS CAUSES AN ERROR (Should be at the beginning)
book=excel.workbooks.add


excel = Nil //Set the object to Nil
Exception err as OLEException // Check for an error
MsgBox err.message  // Display the error

Luis · **Posted:** Mon May 06, 2013 8:58 pm

Eugene,
Thanks. Yes you are correct but that was not the issued I encountered. The problem is the location of
"excel.visible = true

In my original code, this instruction was placed almost at the end of the code just before
"excel = Nil

By doing this, I was able to build the worksheet, configure all I need to format, etc. etc. and finally make Excel visible. It worked very well with no errors. Since....and I cannot tell you what or when.... it does not work anymore like that.
I have to place the "excel.visible = true right after the .....workbook.Add.... up at the beginning of the code. I agreed with you that ....workbooks.Add... must be at the beginning.
My first post shows the way it worked for moths with no problems till something happen...and cannot tell you what and I am now forced to move this instruction up under the .....workbooks.Add... with lower performance.

When placing the excel.visible=true at the end of the code the compiler crashes and the error manifests as an "exception"

Hope this helps to understand better the issue.
Regards and thanks again for your time and consideration.
Luis.

eugenedakin · **Posted:** Mon May 06, 2013 9:58 pm

Hi Luis,

My apologies for misunderstanding the issue. Although the code should work properly, and your machine is having errors, lets try an experiment. Try and place the excel.visible = false below the workbooks.add command, and then set visible = true at the bottom of the code. I have included an example below.

dim excel As new ExcelApplication
dim book As ExcelWorkbook
dim sheet As ExcelWorksheet

'=======Add New Workbook=========
book=excel.workbooks.add
excel.visible = false 'Set this to false

'======Risk Input Worksheet=============
excel.worksheets(1).name="Input"
excel.worksheets(1).Range ("A1").value="ID"                                      'Risk ID number, numeric
excel.worksheets(1).Range("A1").Select_
excel.worksheets(1).Range("A1").Addcomment
excel.worksheets(1).Range("A1").Comment.Text ("pippo")
excel.worksheets(1).Range("A1").Comment.Visible = False
excel.worksheets(1).Range("A2").Select_
excel.worksheets(1).Range("A2").Addcomment
excel.worksheets(1).Range("A2").Comment.Text ("Alberto")
excel.worksheets(1).Range("A2").Comment.Visible = False

excel.visible = true // Show the excel application

excel = Nil //Set the object to Nil
Exception err as OLEException // Check for an error
MsgBox err.message  // Display the error

Similar to you, I am taking a guess at this issue because I can't seem to make the error happen on my Windows 8, Real Studio 2012 r2.1 machine. If the above code works, then setting excel.visible = false may give the excel workbook focus. This would give you the performance increase that you would like.

Let me know if this helps.

Sincerely,

Eugene

Luis · **Posted:** Sat May 18, 2013 10:30 am

Eugene,
I apologize for delaying my reply but wanted to perform all possible tests, to the best of knowledge, about this issue of the location of the "excel.visible=true" statement. I came to realize, not fully sure yet, that the problem must be in my Excel version. It is XP running on XP SP3 as OS. I could not make it work the way it did. I also understand that the location of the statement is not incorrect neither, since you tried in your machine and everything worked fine.I have tried different RB versions, including 2010 r2.1 which was the one I had before and everything worked fine.

Regarding your suggestions in your last posting, making Excel invisible and later visible, very estrange things happened in my Excel.
It does open and the name of the first worksheet is OK, but no comments appear, no value "ID" in A1, the number "1" in the first row up in the left disappears!!! (the header of that row remains empty!!!) and the following header "2" is OK. Who knows??!!
If I locate the statement "True" at the beginning of the code it works fine.
By changing the location of the statement to the end of the code, I could noticed better efficiency by doing all the arrangements first and then presenting the screen for last.

However, I really appreciate your input and suggestions but I think I will have to stick with the way it works for now, till I am able to update to a better OS and Office Version and will follow up with some results.

Thank you very very much for all your time and consideration.
Sincerely,
Luis.

Luis · **Posted:** Tue May 28, 2013 10:23 am

Eugene and all,
As suspected the problem was Excel. Maybe my copy was corrupted but I did re-installed it a few times. However, I had the opportunity to work on machines with Excel 2003, 2007 and 2010. I all cases, placing the Excel.visible=true instruction at the end of the code, works just fine, as originally in my code.
Thank you again for your time.
Luis.

	Real Software Forums The forum for Real Studio and other Real Software products.
[ REAL Software Website \| Board Index ]

Real Software Forums

Is this a bug or I lack of experience? (Excel - RB)