4

I'd like to read the numbers in an Excel spreadsheet using the XLConnect package. My impression is that it fails to read the numbers which are computed inside Excel using a formula with a dollar sign. Is there a solution to this problem ?

PAC
  • 5,178
  • 8
  • 38
  • 62
  • 2
    Although it's possible to read from Excel files, my advice is always to save to CSV and read from that. Only if you have so many Excel files that this is not feasible anymore I would use other methods. – Roland Jul 11 '13 at 11:31
  • Yes, usually I prefer to export data in a csv but in this case I have a big Excel file with a lot of sheets so I would prefer to work directly with the excel file – PAC Jul 11 '13 at 11:32
  • I understand. However, I propably would still employ other methods (e.g., a VBA script that exports all worksheets to CSV). But that's just me. – Roland Jul 11 '13 at 11:36
  • Do you have a reproducible example where I could have a look? – Martin Studer Jul 11 '13 at 12:30

1 Answers1

5

I ran into the same problem.

Have you looked at the parameter 'useCachedValues' for the readWorksheet function? Here is the help doc on the parameter:

useCachedValues logical specifying whether to read cached formula results from the workbook instead of re-evaluating them. This is particularly helpful in cases for reading data produced by Excel features not supported in XLConnect like references to external workbooks. Defaults to FALSE, which means that formulas will be evaluated by XLConnect.

I specified useCachedValues=TRUE. While the formulas in my case did not involve references to external links, this worked for me and I was able to read in the values.

Jim T
  • 61
  • 1
  • 4