1

I am trying to read the values from excel A which has the referenced data from another excel sheet B.

Code so far:

<cfspreadsheet action="read"
            src="#ExpandPath(".")#/Excel_A.xlsx"
            sheetname="Sheet1"
            columns="15-19"
            rows="6-7"
            query="qResult" />

<cfdump var="#qResult#">

Dump Result : The values in all the columns are not displayed correctly.

Reference Cell Issue

Tried accessing one of the values through spreadsheet functions; still the same issue exists.

<cfscript>
    theSheet = SpreadSheetRead("#ExpandPath(".")#/Excel_A.xlsx","Sheet1");      
    theValue=SpreadsheetGetCellValue(theSheet,10,1);
    theFormula=SpreadsheetGetCellFormula(theSheet,10,1);
    WriteOutput("The value is: " & theValue & " and formula is" & theFormula);  
</cfscript>

Output: The value is: Sheet1!$A$1 and formula is Sheet1!$A$1

It looks like spreadsheet functions does not support the reference data values.

How can I access the values from the excel cells having referenced data?

Edit 1: Steps to replicate what I want.

  1. There are two Excel sheets (Excel_A.xlsx and Excel_B.xlsx).
  2. Excel_B.xlsx : Insert a number 123 in cell A1.
  3. Excel_A.xlsx : Reference the A1 cell of Excel_B in Cell A10. So formula in A10 will be

='Path to [Excel_B.xlsx]Sheet1'!$A$1

  1. Now I need to see the value of cell A10 in excel_A programmatically.

How can I achieve this since the <cfscript> code is not returning the value and shows just the half-formula?

Gaurav S
  • 999
  • 8
  • 16
  • I'm pretty confused by your question. Are you saying that the value is a reference or a formula? If it is a reference to a cell/col, then you need to read *both* sheets in so you can dynamically fetch the second value. – Raymond Camden Oct 14 '14 at 10:27
  • I'm with Ray - this looks like a format to a reference. MTD! is a call for "Month to date" I think (not sure). So you need the value specified by the reference - sheet number 3 or 4 I'd wager. – Mark A Kruger Oct 14 '14 at 13:11
  • @RaymondCamden More details added as part of Edit 1. – Gaurav S Oct 14 '14 at 22:52
  • @MarkAKruger : MTD is not the formula, its a sheetname. Details to replicate the issue added as part of Edit 1. – Gaurav S Oct 14 '14 at 22:53

0 Answers0