Need a post?
Hi Friends,
If you need a post on any subject, then you can post me a comment on this page. I will try my best to explain you with a simpler sample.
Thanks,
Naveen Prabhu
Naveen's Weblog
Bridge to future
Stats
-
Recent Comments
blake on Multiple List Left Join in… codingsense on Search word or phrase within a… vj on Search word or phrase within a… Eliezer Reis on Refresh UI elements on collect… codingsense on Sending Email through Gmail us…
Rashmi K.N said
Is it possible to read formulas given for the cells in excel sheet. There will be an excel sheet template containing some definition (with formulas and values calculated accordingly). I need to use those formulas when user enters numeric value in the UI which i am going to design to incorporate that definition from the excel sheet
codingsense said
Hi Rashmi,
You can access a formula of the cell in excel in your application by running a macro.
for Eg:-
if you have entered a formula in C3 as = A3 + B3 , then Range(“C3″).Formula will give you “=A3 + B3″.
Here I have one post that you can refer
Get Formula from excel cell
If you want anything more please comment.
Thanks,
Naveen Prabhu.
Rashmi K.N said
We can acheive the excel formula requirement with the following code
xlbook = GetObject(Server.MapPath(“../Pages/Book111.xls”))
xlbook.Application.Visible = True
xlbook.Windows(1).Visible = True
xlbook.Application.WindowState = Excel.XlWindowState.xlMinimized
Dim irow As Integer
Dim icol As Integer
Dim strcelltext As String
‘There r 3 rows X 3 columns in excel sheet
Dim usedrange As Excel.Range = xlbook.Worksheets(1).UsedRange
For irow = 1 To usedrange.Rows.Count
For icol = 1 To usedrange.Columns.Count
strcelltext = Convert.ToString(xlbook.worksheets(1).cells(irow, icol).value)
If (strcelltext.StartsWith(“=”)) Then
MsgBox(xlbook.Worksheets(1).cells(irow, icol).Formula)
End If
‘ ”MsgBox(xlBook.Worksheets(1).Cells(irow, icol).Value)
‘ ”MsgBox(xlBook.Worksheets(1).cells(irow, icol).Formula)
” xlBook.Worksheets(1).cells(irow, icol).value)
Next
Next
codingsense said
Hi Rashmi,
This works fine, but only if specific version of office is installed in the client machine.
If you are working on a project for one client then it will surely work fine, since you can ask customer to install the version of Office that is required. But for products this solution will give problem, if other versions of Office is installed.
So its better to use late binding if your software is to be deployed in many places.
Thanks,
Naveen Prabhu
Rashmi said
Hi Naveen,
I want to know how to block editing of excel worksheet cells.
Requirement is-
I have to create an excel dynamically(done) and make it non editable. This excel sheet will be sent to my client’s vendor where he can only add comments to cells but should not be allowed to edit cell values.
I tried with the following codes-
xlsSheet.Protect(, True, True, True, True)
Dim oRange = xlsSheet.Range(“A1:Z100″)
oRange.locked = False
&
excel application.screenupdating=false
(This will block viewing also.)
Awaiting for the solution
codingsense said
Hi Rashmi,
Yes you can do it,
Follow the steps to do manually,
1) Select a cell
2) Enter some data ( that is to be locked for modification)
3) select Data Menu -> Validation
4) In Allow select custom
5) In formula enter “” and click ok
6) Now check altering the data in that cell, it gives a message and does not let the user change the value.
THe macro for cell A1 with text “I m locked” looks like
Range(“A1″).Select
Range(“A1″) = “I m locked”
With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=”"”"”"
End With
with the above method the user can add comments to the cell but cannot change the value.
I think this should solve your purpose. For clarification please revert back.
Cheers,
Naveen Prabhu
Rashmi said
I dont want to do it manually Naveen,
My client will generate the excel through the system which i am developing. This excel has some details about the product. My client will send this excel to their vendors. All vendords can do is to view the excel data and add comments to cell if required. They should be restricted from editing the cell values.
I tried with the code block sent by u. Its giving error.
Moreover, want to know what is Selection is . I am using ASP.Net with VB.Net
Thanks & Regards,
Rashmi
Vikram Rajaram said
Dear Naveen,
I am using an image file as a wallpaper on a VB 2008 form. There are three forms in the project. I find that the image file is reloaded each time the form is opened. For example, if I open frm3 as a new instance of a Form 3 class, the image in Form 3 gets “reread” again, which slows things down. In VB 6 if you Loaded a form which had an image as background, it stayed in memory and when you called the form using the “Show” method the form appeared instantly. This does not happen in VB 2008. Any idea why? I am based in Bangalore and would be happy to discuss this with you over the phone.
Vikram Rajaram
codingsense said
Hi Vikram,
VB 2008 is object oriented, here all the class level varaibles get initialized when a new instance of a class is created. So if you have the image declared in class level then whenver you create a new object it will be fetched. If your image is being used in all the forms then try declaring it as static. Static variables will remain in memory till the program ends so it has some pros and cons.
I am not clear with your statement “ReRead again”? are you reading any information and creating a image or just loading a image from a location?
If your doubt is not clear then please feel free to reply with more details.
Thanks,
Naveen Prabhu
Rashmi said
Hey Naveen, i am using ur code only to generate excel with cells locked. But my doubt is y the form is not getting hidded/closed. I don’t want the form to be shown because i need only excel to get generated where user can save it in desired location. I gave me.hide, me.visible=false its not working. Can u help me out.
codingsense said
Hi Rashmi,
When you dont want a form to be showed then its of no use adding a windows form, instead you can copy all the code into a class and make a DLL instead of EXE.
If you still need a exe then you can shift the code into a class and remove the windows form, then in Program.cs in Main function you can create a instance of that class and execute the function.
Hope the idea is clear. If not please brief more on the requirement.
Thanks,
Naveen Prabhu
Rashmi said
Hi Naveen,
Thanks for the update.
Problem is all those properties and some of the methods in ur code for creating and deleting macro to acheive the desired thing, is not working in web application environment. Works very well in windows application environment. Hence just made a call in my web application to win application exe to get the thing.
Rashmi said
Hi Naveen,
I am using the Modal windows to send and receive the parameters from and to the parent window.
Problem i am facing is, when i open model window which shows details of a product, its not showing the previously updated one.
ie. i created a product and later updated. when i go to see the updated product, the modal window still shows the very old values. In the backend (database), values are updating properly but this is the problem only with modal window. And to my surprise, i observed that, when i run the application (thru Visual Studion) and browse the application then only i am getting the updated product details. So this means i need to run each time the application to view the updated data(not the old data).
Any solution for this?
codingsense said
Hi Rashmi,
What method are you using to notify the parent page to refesh once the modal window is closed.
Rashmi said
The code used to close the model window and refresh the parent page.
window.returnValue=true;window.close();
My problem is, model window is not getting the recent values when opened.
Rashmi said
Hi Naveen,
I got the solution for modal window problem. Its because of the reference to temporary files. We need to use
the following line of code with modal windows.
HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache)
codingsense said
Hi Rashmi,
Thanks for sharing the info.
Rashmi said
Hi Naveen,
I am facing a new problem with windows application. Esp with a particular application related to handling microsoft excel ( code got from you) After 1st or 2nd build, application build will start showing the error “Unable to copy file “…..exe” to “…..exe”. The process cannot access the file “….exe” because its being used by another process”.
I searched in net and came to know that this error is because of the more than 1 instance of the process being still running. I tried to kill those. But its not letting me to do so says “Cannot be opened access is denied”.
Do u have any solution for this?
codingsense said
Hi Rashmi,
Here is the code using Excel.Interop to make cell read only in excel.
Make cell read only in Excel
In the above application i m creating a macro in excel and executing it to make a cell read only.
For other clarifications please reply back.
Cheers
Naveen Prabhu