0

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?

Community
  • 1
  • 1
Ken.T
  • 3
  • 2

1 Answers1

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
Jagadish Dabbiru
  • 930
  • 9
  • 22
  • 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). – David Zemens Aug 07 '14 at 16:05
  • 1
    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. – Jagadish Dabbiru Aug 07 '14 at 17:42
  • 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. – Ken.T Aug 08 '14 at 08:37