Hi.

I have a spreadsheet which has one worksheet (Analysis) where the rows in
one column reference the rows on a second worksheet using a simple worksheet
reference notation, for example:
='Retail Price List'!A1

I use ADO.NET (using ASP.NET / C#) to populate the values of the 'Retail
Price List' worksheet and when I do so, the values from the 'Retail Price
List' worksheet show up correctly on the 'Analysis' worksheet.

However if I add a custom ribbon to the excel document, the ='Retail Price
List'!A1 type references no longer reflect the data on the 'Retail Price
List' worksheet. Instead it shows '0' in the cell that contains the ='Retail
Price List'!A1 reference, even though the A1 cell on the Retail Price List
worksheet contains a value. If I edit the cell that contains the ='Retail
Price List'!A1 reference (i.e. hit F2 and then Enter without making any
changes), the value shows up.

The only difference between this working correctly or not is that it stops
working correctly after I add a custom ribbon using the Office Custom UI
Editor tool.

I am using Excel 2007. The excel file format is .xlsm.
In C# I use the 'Microsoft.ACE.OLEDB.12.0' ADO.NET driver for Excel.

Does anyone have an idea for why the cell references are not correctly
reflected?

Thanks

I have had this issue for years when importing data from Oracle into XL using
ADO (even before 2007 and the CustomUI tool). It's like the formats are not
updated when the data is written into the sheet.

You can get around/fix this by setting your code to run the following after
an update:

ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

This is the equivalent to pressing "F2" and enter at each cell only it does
the whole sheet at once. It's annoying, but the above fix is quick enough.

Hope this helps.


"Base64" wrote:

Hi.

I have a spreadsheet which has one worksheet (Analysis) where the rows in
one column reference the rows on a second worksheet using a simple worksheet
reference notation, for example:
='Retail Price List'!A1

I use ADO.NET (using ASP.NET / C#) to populate the values of the 'Retail
Price List' worksheet and when I do so, the values from the 'Retail Price
List' worksheet show up correctly on the 'Analysis' worksheet.

However if I add a custom ribbon to the excel document, the ='Retail Price
List'!A1 type references no longer reflect the data on the 'Retail Price
List' worksheet. Instead it shows '0' in the cell that contains the ='Retail
Price List'!A1 reference, even though the A1 cell on the Retail Price List
worksheet contains a value. If I edit the cell that contains the ='Retail
Price List'!A1 reference (i.e. hit F2 and then Enter without making any
changes), the value shows up.

The only difference between this working correctly or not is that it stops
working correctly after I add a custom ribbon using the Office Custom UI
Editor tool.

I am using Excel 2007. The excel file format is .xlsm.
In C# I use the 'Microsoft.ACE.OLEDB.12.0' ADO.NET driver for Excel.

Does anyone have an idea for why the cell references are not correctly
reflected?

Thanks

Thanks for the suggestion. It works well in the sense that it does update the
cell contents.

I agree it is annoying to have to do this though, it would seem Excel should
do an automatic refresh of all cells when the workbook is opened.

Perhaps it is the ADO.NET drivers that are broken for certain file formats.
The same workbook works fine when the template is populated in .xls format,
it exhibits problems only in the .xlsx, .xlsb and .xlsm formats...

Thanks

"xp" wrote:

I have had this issue for years when importing data from Oracle into XL using
ADO (even before 2007 and the CustomUI tool). It's like the formats are not
updated when the data is written into the sheet.

You can get around/fix this by setting your code to run the following after
an update:

ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

This is the equivalent to pressing "F2" and enter at each cell only it does
the whole sheet at once. It's annoying, but the above fix is quick enough.

Hope this helps.


"Base64" wrote:

Hi.

I have a spreadsheet which has one worksheet (Analysis) where the rows in
one column reference the rows on a second worksheet using a simple worksheet
reference notation, for example:
='Retail Price List'!A1

I use ADO.NET (using ASP.NET / C#) to populate the values of the 'Retail
Price List' worksheet and when I do so, the values from the 'Retail Price
List' worksheet show up correctly on the 'Analysis' worksheet.

However if I add a custom ribbon to the excel document, the ='Retail Price
List'!A1 type references no longer reflect the data on the 'Retail Price
List' worksheet. Instead it shows '0' in the cell that contains the ='Retail
Price List'!A1 reference, even though the A1 cell on the Retail Price List
worksheet contains a value. If I edit the cell that contains the ='Retail
Price List'!A1 reference (i.e. hit F2 and then Enter without making any
changes), the value shows up.

The only difference between this working correctly or not is that it stops
working correctly after I add a custom ribbon using the Office Custom UI
Editor tool.

I am using Excel 2007. The excel file format is .xlsm.
In C# I use the 'Microsoft.ACE.OLEDB.12.0' ADO.NET driver for Excel.

Does anyone have an idea for why the cell references are not correctly
reflected?

Thanks