I have two workbooks both with 1 table having 4 columns and 60 rows, I take one table and copy the style and then copy the value to another workbook. then I reference this copied over table entirely in another sheet of that workbook. Now I have a logic written to refresh the data which will take the latest value of the table that was in workbook 1 then copies it over to the other workbook where the table which is referenced is present and replaces it with the latest values. what ends up happening is the cells which refer this copied over table throw a ref error. I believe it’s because the old cells get copied over with the new values and that ends up breaking the formula for some reason.
this is my copy logic-
destinationRange.CopyStyle(sourceRange);
destinationRange.CopyValue(sourceRange);
what can I do differently to not get that error?
Copying a range across workbook which is being referenced within that workbook is causing #ref error
Thank you for reaching out with your inquiry related to Aspose.Cells for .NET. Below is a detailed and structured approach to address your issue where copying a range is causing a #REF error.
Understanding the Issue
You’re experiencing a #REF error when copying and updating table values within the same workbook. This error typically occurs due to broken references within formulas when cell addresses change.
Steps to Resolve the Issue
To address the issue, we’ll follow these steps:
- Ensure the Correct Use of Copy Methods
- Refresh the References
- Apply the Updated Values without Breaking References
Below is a sample implementation of how you can handle this in Aspose.Cells for .NET:
Sample Code Snippet
usingAspose.Cells;
// Load the workbooks
Workbook sourceWorkbook = new Workbook("Workbook1.xlsx");
Workbook destinationWorkbook = new Workbook("Workbook2.xlsx");
// Access the worksheets
Worksheet sourceWorksheet = sourceWorkbook.Worksheets[0];
Worksheet destinationWorksheet = destinationWorkbook.Worksheets[0];
// Define the source and destination ranges
string sourceRange = "A1:D60";
string destinationRange = "A1:D60";
// Refresh the data from the source workbook
шатьCopyRange(sourceWorksheet.Cells, sourceRange, destinationWorksheet.Cells, destinationRange);
// Save the updated destination workbook
destinationWorkbook.Save("Updated_Workbook2.xlsx");
void CopyRange(Cells sourceCells, string sourceRange, Cells destinationCells, string destinationRange)
{
// Copy style and value from source to destination
int[] sourceIndices = sourceCells.GetColumnArray(sourceRange, sourceCells.GetRangeByName(sourceRange).FirstRow,
sourceCells.GetRangeByName(sourceRange).FirstColumn);
int[] destinationIndices = sourceCells.GetRowArray(sourceRange, sourceCells.GetRangeByName(sourceRange).FirstRow,
sourceCells.GetRangeByName(sourceRange).FirstColumn);
foreach (int row in sourceIndices)
{
foreach (int col in destinationIndices)
{
// Copy the style
destinationCells[row, col].SetStyle(sourceCells[row, col].GetStyle());
// Copy the value
destinationCells[row, col].PutValue(sourceCells[row, col].Value);
}
}
}
Key Points
- Using Appropriate Copy Methods: Aspose.Cells
CopyStyleandCopyValuemethods are accurate for copying range properties. Ensure values and formulas are preserved. - Refreshing Data: Ensure logic refreshes values correctly to avoid breaking formula dependencies.
- Efficient Range Management: Handle specific rows and columns accurately to prevent reference errors.
Additional Resources
For a detailed guide on managing references and cells, refer to the Aspose.Cells [documentation](https://apose.github.io Speech/Aspose.Cells-NET). The documentation provides comprehensive examples and explanations on effectively using Aspose.Cells for .NET.
Conclusion
Utilizing the structured approach ensures copying the range across workbooks without incurring #REF errors. Follow the outlined steps and sample implementation to manage your data refresh logically.
If you need further technical assistance, please feel free to reach out. We are here to help!
Always ensure the necessary details and context related to your scenario for tailored support.
I got the issue, when I am doing range.CopyValues() it ends up deleting the table in the paste location and then pasting the values causing the ref error as the referred cells formula requires the table. How do I paste the values without deleting the table.
@Adhirath
Could you share a template file and sample codes ? It will help us quickly locate the issue .
Sample file-
Before Copy-
Cec1.zip (35.2 KB)
After copy-
Cec2.zip (34.6 KB)
Code for copy-
copy code.zip (2.4 KB)
From what I have observed the formula gives a ref error right after copyvalues happens.
the referenced table is in sheet referenced(A3:D61) and the referred table in sheet inserts(Table2)
@Adhirath
It seems the we forgot to remove formulas when converting table to normal range.
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-58749
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
is there a workaround for this?
@Adhirath
Now, we only can remove them as the following :
Cells cells = workbook.Worksheets["referenced"].Cells;
for (IEnumerator ie = cells.GetEnumerator(); ie.MoveNext();)
{
Cell cell = (Cell)ie.Current;
if(cell.IsFormula && cell.Formula == "#REF!")
{
cell.Value = cell.Value;
}
}
Just to be clear the issue I am facing here is that when I use range.copyvalues() while pasting the range it ends up deleting the table which is what is causing the #ref error for me. So the issue logged, once fixed will only paste the values and not delete the table right? My use case is that I have a table say table 1 in workbook1 and i want to copy it to workbook 2 sheet 1, in workbook2 sheet 2 i refer the entire table 1 pasted in sheet 1. Now if I add/remove a column/row from workbook 1 table 1 and I copy to workbook 2 table it should not break the reference formula. How should I go about it?
@Adhirath
Could you share a simple console project to show your issue?
Do you mean you want to copy value with range.copyvalues() to a table? In codes in the previous post, table.ConvertToRange(); will be called.
Hi here is the console app, it should give you some insight into what I am trying to do. The idea is that I have a table for which the data/row number/column number can change. I want to copy this into another workbook where it will be used as a reference. When any change happens to the original table it should be reflected accurately in the reference without the reference breaking.
Console app link- Filebin | rsvrhklekq95hmsc
Thanks for the sample project and template files.
I did test your scenario/case using your sample files and app. I found the issue you have mentioned. Then, I manually performed the same task in MS Excel. I found the same issue, i.e., I got #REF! error in the “referenced” sheet cells. So, apparently Aspose.Cells works the same way as MS Excel does when copying table named ranges b/w spreadsheets. Anyways, let us evaluate your issue thoroughly and we will get back to you soon.
@Adhirath
If I copied “originalRange” range from OriginalTableWkbk.xlsx to “copiedRange” in CopiedTableWkbk.xlsx with MS Excel, a simple “#REF!” only left in the “referenced” sheet.
Is it your excepted result?
Nope, I want to the reference values to the original tables values, that means if the original tables values update/ row/column is added/removed it should all reflect in the reference and I want the reference to refer to the copiedRange.
@Adhirath
In MS Excel, if copying a range to other range with table, the table should be removed. So Aspose.Cells works as MS Excel, but there is an bug about updating array formulas which contains refer to table.
If you do not want to remove the table, it’s better that you can copy the range by yourself:
destinationRange.CopyStyle(originalRange);
for (IEnumerator ie = destinationRange.GetEnumerator(); ie.MoveNext();)
{
Cell cell = (Cell)ie.Current;
cell.Value = null;
}
for (IEnumerator ie = originalRange.GetEnumerator(); ie.MoveNext();)
{
Cell cell = (Cell)ie.Current;
destinationRange[cell.Row - originalRange.FirstRow, cell.Column - originalRange.FirstColumn].Value = cell.Value;
}
makes sense, but will it work correctly if I increase/decrease the number of rows/columns as I won’t be modifying the table only the cell values?
@Adhirath
When you increase or decrease rows and columns, the reference area of the range does not change. The deleted rows and columns will be replaced by other rows and columns, which will result in the entire range of values being different from before.
@Adhirath
Please refer to the following example code to observe the changes in the reference area and values within the range after deleting rows and columns.
Aspose.Cells.Workbook workbook = new Workbook();
//Get Cells
Aspose.Cells.Cells cells = workbook.Worksheets[0].Cells;
//Put value
cells[0, 0].PutValue("A1");
cells[0, 1].PutValue("B1");
cells[0, 2].PutValue("C1");
cells[1, 0].PutValue("A2");
cells[1, 1].PutValue("B2");
cells[1, 2].PutValue("C2");
cells[2, 0].PutValue("A3");
cells[2, 1].PutValue("B3");
cells[2, 2].PutValue("3");
cells[3, 0].PutValue("A4");
cells[3, 1].PutValue("B4");
cells[3, 2].PutValue("C4");
//Create Range
Aspose.Cells.Range range = cells.CreateRange("A1:B3");
Console.WriteLine(range.ToString());
Console.WriteLine(cells[range.FirstRow + range.RowCount - 1, range.FirstColumn + range.ColumnCount - 1].StringValue);
cells.DeleteRow(1);
cells.DeleteColumn(1);
Console.WriteLine(range.ToString());
Console.WriteLine(cells[range.FirstRow + range.RowCount - 1, range.FirstColumn + range.ColumnCount - 1].StringValue);
The output:
Aspose.Cells.Range [ Sheet1!A1:B3 ]
B3
Aspose.Cells.Range [ Sheet1!A1:B3 ]
C4
from what I have observed when I paste a range into a table, the table remains.
@Adhirath
Your observation is correct. If you perform the same operation in Excel, you will find that when copying data to a table, the table will expand its scope as the data increases, including new data into the table.