[R] spreadsheet addiction

davidr@rhotrading.com davidr at rhotrading.com
Fri Jan 14 16:00:24 CET 2005


I get around the dependence on how things are displayed in Excel by
using 
VBA to write my data files. Then you get full precision and you can use 
white space as a separator if you like.

Sub WriteRangeToFile(rData as Excel.Range, fName as String)
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Application.StatusBar = "Writing Data File " & fName

  Dim ofs As Object, ots As Object
  Dim vData As Variant
  Dim r As Long, rCount As Long
  Dim c As Long, cCount As Long

  Set ofs = CreateObject("Scripting.FileSystemObject")
  vData = rData.Value
  Set ots = ofs.CreateTextFile(fName, True)
  cCount = UBound(vData, 2)
  rCount = UBound(vData, 1)
  For r = 1 To rCount
    str = ""
    For c = 1 To cCount - 1
      str = str & vData(r, c) & " "
    Next c
    str = str & vData(r, cCount)
    ots.WriteLine (str)
  Next r
  ots.Close
  Set ots = Nothing

  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
  Application.StatusBar = False
End Sub

You can call this from another macro to set the range to a selection,
e.g., and the filename to the contents of some cell.

HTH,
David Reiner

-----Original Message-----
From: Henrik Andersson [mailto:h.andersson at nioo.knaw.nl] 
Sent: Friday, January 14, 2005 5:38 AM
To: r-help at stat.math.ethz.ch
Subject: Re: [R] spreadsheet addiction

I am moving to using R more and more as a computational platform and
I use Microsoft Excel at the moment in exactly the way you describe in 
WRITING ASCII FILES as a staging area for data, to gather data and make 
simple calculations. I've have experienced problems with saving to .csv 
files, and not only using scientific notation. The problem occurs also 
in normal notation if you choose to show only some decimals.

That is a serious problem, what is the remedy?

To always avoid showing only some decimals and avoid scientific notation

or does someone have a  better solution, like a global option, Save with

full represention to .csv.

I'm open for all sorts of suggestions, including ditching Excel...and 
use ???

Cheers, Henrik

Patrick Burns wrote:
> There's a new page on the Burns Statistics website
> http://www.burns-stat.com/pages/Tutor/spreadsheet_addiction.html
> that looks at spreadsheets from a quality assurance perspective. It
> presents R as a suitable alternative to spreadsheets.  Also there are
> several specific problems with Excel that are highlighted, including
> the status of statistical functionality in Excel.
> 
> Patrick Burns
> 
> Burns Statistics
> patrick at burns-stat.com
> +44 (0)20 8525 0696
> http://www.burns-stat.com
> (home of S Poetry and "A Guide for the Unwilling S User")
> 
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! 
> http://www.R-project.org/posting-guide.html
>

______________________________________________
R-help at stat.math.ethz.ch mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide!
http://www.R-project.org/posting-guide.html




More information about the R-help mailing list