Naveen's Weblog

Bridge to future

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

37 Responses to “Need a post?”

  1. 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

  2. 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.

  3. 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

  4. 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

  5. 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

      • 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

  6. 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

  7. 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.

  8. 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.

  9. 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)

  10. 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?

    • Andy said

      Hi rashmi,

      I want try to help, i have the same problem with you recently, but i try to rebuild the solution. it’s work.
      maybe you could try too.

  11. andrew said

    hi naveen,
    i’m andy.

    it’s working πŸ™‚

    right now, i have a problem to save that datagrid value.
    could you teach me ? i already tried with datarowview, but it is not working.

    tq

    • codingsense said

      Hi Andy,

      Surely it would be my pleasure to teach you. Can i know where you want to save the value of the datagrid. Do you want to save in the database or xml or anywhere else?

      Regards,
      Codingsense

      • Andy said

        tq bro,
        Finally I can save the datagrid.
        I was tried with ToArray() function from the class.

        Nice help.
        Btw i want to ask you about the prospect with WPF, what do you think about the prospect ?
        Could be WPF become booming in Programming language ?

      • codingsense said

        Hi Andy,

        WPF has been developed to boom, The concept of XAML, 2D, 3D and many more features have made all the users to develop lots of creative and interactive applications. So just stick to it and learn it well. Surely one day you will be happy that you took the right decision. πŸ™‚

        For any help or clarification on WPF please feel free to contact me.

        Good Luck.
        Codingsense

  12. Andrew said

    Hi Naveen,

    Could you help me with rounddown 1000 ?
    If I have a number
    1,800,000,900.00,
    1,800,005,500.00
    i want to have a result like this
    1,800,000,000.00,
    1,800,005,000.00

    tq for your help naveen πŸ™‚

    • codingsense said

      Hi Andrew,

      Check the below code,

      double Amount = 1800005500.00;
      double ExcessAmount = Amount % 1000;
      Amount -= ExcessAmount;

      Change to amount to any no and it will rounddown to 1000, if you want rounddown to some other no then you can replace the 1000 with the specific no.

      Regards,
      Codingsense

      • Andrew said

        tq bro, for your help πŸ™‚

        im so glad to know your blog for my knowledge πŸ™‚
        keep posting nice info naveen.

        God bless

      • codingsense said

        Thanks Andrew,

        Good luck for learning WPF. For any clarifications please feel free to comment.

        Thanks,
        Codingsense

  13. Andy said

    Hi Naveen,

    could you teach me about how to using radiobutton in datagrid ?
    Thank You

  14. I was needed to thank you for an excellent read!! I’m undoubtedly relishing every small bit of this. I have you bookmarked to find out new stuff you post.

  15. manoj said

    Hi naveen,
    I am new to linq,Pls help me on the below sql query into Linq.My requirement is to retrieve all the records from the session table if either subjectid or testid can be null
    I tried with the linq ,but the output is not retreiving all the records from the session table for the passing student id.
    Thanks in advance

    SQL:
    select su.subjectid,su.subjectname,t.testid,t.testname,p.personname
    from session as se inner join tutor as tu on se.tutorid=tu.tutorid inner join person as p on tu.personid = p.personid left join subjects as su on se.subjectid = su.subjectid left join test as t on se.testid =t.testid where se.studentid =selectedStudID

    Linq:
    var getdata = (from se in studentdetails.session join tu in studentdetails.tutor on se.tutorid equals tu.tutorid join
    p in studentdetails.person on tu.personid=p.personid join
    su in studentdetails.subject on se.subjectid equals su.subjectid into sub
    join t in studentdetails on se.testid equals t.testid into tes
    from su in sub.defaultifempty()
    from t in tes.defaultifempty()
    select new
    {
    p.personname,
    subjectname = su.subjectname,
    testname = t.testname,
    se.sessionid
    });

  16. raju said

    hi naveen,
    myself
    my name raju,am working as software trainee in choice solutions limited in hyd.
    am joined this company since 2 months
    company provided .net traning and sql server 2008 training
    i need real time examples on stored procedures an fuctions
    if we have please help me

  17. Vikas Verma said

    hi naveen i pasted same code
    but i m getting error transport connecting with server.,
    all things are OK, mail id & pwds.
    pls reply ASAP

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: