none
Issues updating data into an excel file if it is not open, note that program has select & update using one connection which is kept open until both select & update operation completes. RRS feed

  • Question

  • Hi Guys,

    There's a lot that I have learned in recently working with excel & access using oleDb and excel app. However, I am stuck at point and could not find an easy answer to it. Here is my question:

    My program:

    1. Reads an excel file row by row, get a key to finds out data from access database table for it, return value from access database to the program, put it in a specific formula for that specific key row element and update the calculated value back to one column of that particular row.

    2. The above operation is performed for multiple rows of 3 sections in the same excel file.

    3. There are multiple operations required in the same excel file (in its different sub-sheets), making some cells formula free, formatting a sub-sheet for a report etc etc, everything is working perfect except this one:

    Select data works fine, update needs the excel sheet to be manually opened on the desktop so that it can be updated and once the program finishes, the excel asks to exclusively save the data that is updated. 

    I cannot understand if I am having one connection for update and select in the same excel file, why do I exclusively keep the file open on my system for update?

    My program doesnot throw any error out of the update query, it gives me the return value of number of rows that are required to be updated, however, the excel sheet doesnot get updated if I donot keep it open.

    Has anyone of you come across such a scenario and do you think that we cannot update and select using one connection or that we need to first complete the select and then get values in some arrays and then close connection for select and write another few lines to update the data at particular cells by opening the connection again. YOu know I am a little sceptical on whether update and select both will work using a single connection as somewhere I have read that for every command (select / update / insert etc) we would need to close the connection and then open it for another operation on the excel. But, I am seriously not sure about it.

    If you can let me know about your findings it would be of a great help, the code that I have is a little big, if you really need it to understand mu question, I will post it here. 

    • Moved by Kristin Xie Monday, July 27, 2015 6:37 AM move to better forum
    Sunday, July 26, 2015 9:35 PM

All replies

  • Hi Deepti,

    Based on your description, your case more related to Ado.Net, I moved your case to

     

    Data Platform Development > ADO.NET DataSet  forum for better support.

    Best regards,

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 27, 2015 6:40 AM