none
Why isn't my code to convert all cells in all sheets to static values not working? Why can't I use it to insert a value on all sheets? RRS feed

  • Question

  • foreach (Excel.Worksheet sheet in oWB.Worksheets) { Microsoft.Office.Interop.Excel.Range excelRange = oSheet.UsedRange;

    excelRange.Value = oSheet.UsedRange.Value; } for (int i = 0; i < (oWB.Sheets.Count); i++) { ((Excel.Worksheet)oXL.ActiveWorkbook.Sheets[i]).Select(); Microsoft.Office.Interop.Excel.Range excelRange = oSheet.UsedRange;

    excelRange.Value = oSheet.UsedRange. }

    I've tried this and a few other things. I also cannot use this to say insert a value in whatever cell on each sheet
    • Edited by _Sniffles_ Saturday, July 28, 2018 7:52 PM correction
    Saturday, July 28, 2018 7:42 PM

Answers

  • Hello _sniffles_,

    Thanks for your detail explanation and sorry for my careless. The reason for your issue is that you are using sheet to loop your whole workbook. However, in the loop, we are still suing oSheet object. The oSheet object is fixed and will not change while looping. So what we done is always on the oSheet object.

    So your code should be 

    foreach (Excel.Worksheet sheet in oWB.Worksheets)
                {
                    Microsoft.Office.Interop.Excel.Range excelRange = sheet.UsedRange;
                    excelRange.Value = sheet.UsedRange.Value;
                }

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by _Sniffles_ Wednesday, August 1, 2018 12:46 PM
    Monday, July 30, 2018 5:09 AM

All replies

  • Hello _sniffles_,

    What do you want to do now? If you want to use a value to all the worksheets, the value is source and each sheet is a target. You should keep source fixed and set target one by one dynamically so the value could be set to all the targets.

    However, in your code, you are keeping source dynamically too with looping worksheets and excelRange is oSheet.UsedRange are total same thing, so why did you set oSheet.UsedRange.Value to excelRange.Value? You are setting value of a range to the range itself. Why did you do this? Could you detail us the purpose of this code?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 30, 2018 2:25 AM
  • Hello and thank you, I apologize.

    When I used this code without the foreach part;

    Microsoft.Office.Interop.Excel.Range excelRange = oSheet.UsedRange;

    excelRange.Value = oSheet.UsedRange.Value;

    It made all the cells with formulas static values and got rid of all the formulas which is what I want to do but this only worked on the first sheet. So I tried this foreach code but it does not run my code on all the worksheets but it works on a single worksheet.

    I was trying to write something that would do this on all sheets.

    I tried to make it loop through all the worksheets another way using that bottom code there. Sorry if I wasn't too clear. Well I did it that way because I don't know what I am doing really but I am trying hard to learn and figure things out a little.

    I thought using the loop in either example that I came up with would work for say setting a value in a range but it doesn't work on all sheets for that either it only sets a value I define on the first sheet. Neither using the foreach part nor the counter seems to loop through all the sheets and I did not understand why.

    I will take your hints at what to do to fix the issue and see if I can get it working. Thank you again for your continued support.

    Monday, July 30, 2018 4:52 AM
  • Hello _sniffles_,

    Thanks for your detail explanation and sorry for my careless. The reason for your issue is that you are using sheet to loop your whole workbook. However, in the loop, we are still suing oSheet object. The oSheet object is fixed and will not change while looping. So what we done is always on the oSheet object.

    So your code should be 

    foreach (Excel.Worksheet sheet in oWB.Worksheets)
                {
                    Microsoft.Office.Interop.Excel.Range excelRange = sheet.UsedRange;
                    excelRange.Value = sheet.UsedRange.Value;
                }

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by _Sniffles_ Wednesday, August 1, 2018 12:46 PM
    Monday, July 30, 2018 5:09 AM