使用VBA将数据发送到R
问题描述:
首先,我是新来的excel的Visual Basic。我正在尝试编写一些代码,这些代码需要在R中使用3列用户定义的数据(用于绘图目的)。用户将在Excel中创建从R创建图形输出的按钮。我至少已经能够按照此示例与R进行交互:http://shashiasrblog.blogspot.com/2013/10/vba-front-end-for-r.html使用VBA将数据发送到R
我无法扩展它以包含多个单元格输入并形成数据帧在R这里是我的尝试:
Sub RunRscript()
Dim shell As Object
Set shell = VBA.CreateObject("WScript.Shell")
Dim waitTillComplete As Boolean: waitTillComplete = True
Dim style As Integer: style = 1
Dim errorCode As Integer
Dim Attributes() As Variant
Attributes = Sheet55.Range("A2:C68")
Dim path As String
path = "RScript S:\...\test.R " & Attributes
errorCode = shell.Run(path, style, waitTillComplete)
End Sub
我收到一个错误:“编译错误:类型不匹配”。我假设我错误地定义了Attributes()。任何帮助或指导文学/教程是非常感谢!坦率地说,我不了解路径和errorCode行。
感谢所有, Ĵ
答
此代码并获得成功写入csv文件:http://www.excel-easy.com/vba/examples/write-data-to-text-file.html
Private Sub CommandButton21_Click()
Dim myFile As String, rng As Range, cellValue As Variant, i As Integer, j As Integer
myFile = "...\test.csv"
Set rng = Selection
Open myFile For Output As #1
For i = 1 To rng.Rows.Count
For j = 1 To rng.Columns.Count
cellValue = rng.Cells(i, j).Value
If j = rng.Columns.Count Then
Write #1, cellValue
Else
Write #1, cellValue,
End If
Next j
Next i
Close #1
End Sub
'Attributes'是从指定的范围内的值的一个2-d阵列。你不能像那样在命令行上把它写成字符串。您可以考虑将范围写入文本文件,然后将文件路径传递给您的R脚本 –
的确如@TimWilliams所述,您的范围不是用于在命令行传递参数的标量值。事实上,这是一个相当长的表格式的3列,66行。考虑将txt/csv保存为在R中用'read.table()'读入df或'readLines()'到列表中。 – Parfait
非常有帮助的评论。我能够找到代码写入一个csv文件供以后在R中使用。感谢您的指导! –