Edit R-script using Excel VBA

Question

I would like to creat a excel macro by using vba, which can edit R-scripts.

Let's call this R script starter.R and its working directory is C:/Documents. The Code I want to change is "run=3000". I want to change it to "run=2000".

Firstly I want to open the R-script by using the following code:

Sub ReplaceString()
Dim filename as string
Dim location as String
location="C:\Users\Ken\Documents"
filename="C:\Users\Ken\Documents\start.R"
'Open filename

Then I want to replace the string "run=3000" to "run=2000".

'Replace("run=3000","run=2000")
end sub

Can somebody help me?

Jagadish Dabbiru · Accepted Answer · 2014-08-07 16:00:41Z

1

Try this Code, It might help you

  Sub ReplaceString()
  Set fs = CreateObject("Scripting.FileSystemObject")
  Set Ofs =fs.OpenTextFile("C:\Users\Ken\Documents\Start.R",1,False) 
  Txt = Ofs.ReadAll
  StrToFnd = "run" & "=" & "3000"
  StrToRplc = "run" & "=" &"2000"          

  Temp=Replace(Txt,StrToFnd,StrToRplc)
  Ofs.Close
  Set Ofs = Nothing
  Set Ofs=fs.OpenTextFile("C:\Users\Ken\Documents\Start.R",2,False) 
  Ofs.WriteLine(Temp)
  Ofs.Close
  End Sub

edited Aug 7, 2014 at 16:00

answered Aug 7, 2014 at 15:41

Jagadish Dabbiru

9519 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

David Zemens Over a year ago

Why not StrToFnd = "run=3000" etc.? Also why use WriteLine method instead of Write (Temp represents the entire file which may be multiple lines, not just a single line).

Jagadish Dabbiru Over a year ago

We can use run=3000 but most of the times in my experience, special symbols fails to fit in one string. So i avoided including them as a single string. And for your second question we can use Either of them. But only difference it writes NewLine at the end of file.

Ken.T Over a year ago

Thank you, Jagadish! it works perfectly. Do you know any ebooks to learn vba? I have read a couple of them, but there are still a lot of unknown things.

Collectives™ on Stack Overflow

Edit R-script using Excel VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related