0 votes
in VBA by Beginner (13 points)
Sub RunRscript()
'runs an external R code through Shell
'The location of the RScript is 'C:\R_code'
'The script name is 'hello.R'

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 path As String
path = "RScript C:\Users\User\Desktop\Intern\FinviztableScrap.R"
errorCode = shell.Run(path, style, waitTillComplete)
End Sub

The code above doesn't have any errors and I'm able to open the command prompt and get the result in the command prompt. However, I want it to be displayed in excel instead of just command prompt, please help! Thanks!

1 Answer

0 votes
by Super Expert (2.8k points)
selected by
 
Best answer

You'll need to redirect the standard output (StdOut) to a cell in Excel with VBA using a macro like this:

Private Sub ExecuteScript()
    Dim oShell As Object
    Dim oExec As Object
    Dim oOutput As Object
    Dim s As String
    Dim sLine As String
    Dim i As Long

    'Run a shell command, returning the output as a string
    Set oShell = CreateObject("WScript.Shell")

    'run command
    Set oExec = oShell.Exec("RScript ""C:\Users\User\Desktop\Intern\FinviztableScrap.R""")
    Set oOutput = oExec.StdOut

    'handle the results as they are written to and read from the StdOut object
    While Not oOutput.AtEndOfStream
        i = i + 1
        Range("A" & i) = oOutput.ReadLine
    Wend
End Sub

This macro takes the standard output and enters it in Column A of the active worksheet. I don't have an R script to test this with at the moment, but I know it works with VBscript .vbs files. 

by Beginner (13 points)
Hi, thanks for your reply, but it still doesn't work for me.

The code below is my R script which use to scrap the data table from finviz.com. Please help me as this is my important assignment, thanks!

library(rvest)
library(dplyr)
library(purrr)
library(openxlsx)

page <- append(0,seq(21,100,by=20))

url_base <- paste0("https://finviz.com/screener.ashx?v=152&f=cap_midover&o=ticker&r=", page)

purrr::map_df(url_base, ~{ Sys.sleep(3); .x %>%
         read_html() %>%
         html_table(fill = TRUE) %>%
         .[[10]] %>%
         setNames(as.character(.[1,])) %>%
         slice(-1)})
by Super Expert (2.8k points)

I'm not able to install R on this machine to test, but I don't see where your script is writing the output to the stdout (standard output). You'll need to write your output to the stdout for the code above to reroute it to Excel. Perhaps you can use a bit of R code like this:

write("prints to stdout", stdout())

Admittedly, I'm not an R expert. Can you describe what happens when you execute the VBA macro in the original answer?

by Beginner (13 points)

Hi, thanks for the explanation! It works for me know!

However, there is another issue for me which is the data printed in the excel is not in the table form. The photo below (1st photo) is the desired format for the table to be appeared in the excel, but the actual table in excel is as what the 2nd photo shows.

By right, what I want should be only 41 row. Further, all the information seems like stick together in the column A, instead of NO. in Column A,  TIcker in column B, Company in column C and so on. 

I have chang the data in R to a data frame already but it is still the same.

Really appreciated if you can help me for this assignment! Thanks! :) 

by Super Expert (2.8k points)

It looks like the data is either fixed-width or tab-delimited in the R output. You'll need to find out how the data is delimited, and then use the text-to-columns feature of Excel to properly format it. If it's tab delimited, add the following line right after the Wend statement (right before the End Sub)

    Range("A1:A" & i).TextToColumns DataType:=xlDelimited, _
            ConsecutiveDelimiter:=True, Tab:=True, Space:=False, semicolon:=False, comma:=False

If it's fixed width, you'll need to find out how many characters each column is and split it using a line of code like this right after the Wend statement:

    Range("A1:A" & i).TextToColumns DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(6, 1), Array(11, 1), Array(49, 1)), _
        TrailingMinusNumbers:=True

This example splits the text after the 3rd, 6th, 11th, and 49th characters. You'll need to manually adjust these breakpoints to match your data.

Ideally, you'd be able to print the output in R with a known delimiter (like a comma) and modify the first code snippet above to split the text by a comma (Comma:=True).

by Beginner (13 points)

I tried the 2nd code given by you which used to split the character, however it is not completely split correctly as I don't understand how to manually adjust the breakpoints, please advise me how should I do that. Further, actually I want the row start from 42th continue from column F, column G and so on instead of continuing from 42nd row (shown in the photo below, the one below blue line), I hope I can get help on this. Thanks for your kindness! :)

by Super Expert (2.8k points)

You'll need change the 1st digit in the Array() fields to define your fixed-width breakpoints. For example, the code:

Array(0, 1), Array(3, 1), Array(6, 1), Array(11, 1), Array(49, 1)

Puts the 0th to 3rd character in Column A, the 3rd to 6th character in column B, the 6th through 11th character in column C and the 11th through 49th character in Column D and everything beyond that in Column E. Simply change those integers to move the breakpoints. My guess is you'll need to change your array definition to this, but to be able to help you more, you'll need to send a snippet of your output:

Array(Array(0, 1), Array(3, 1), Array(6, 1), Array(14, 1), Array(52, 1))

We're starting to deviate from the original question, but here's a code that should get you started splitting your string after the 42nd row:

Private Sub ExecuteScript()
    Dim oShell As Object
    Dim oExec As Object
    Dim oOutput As Object
    Dim s As String
    Dim sLine As String
    Dim i As Long
    Dim iBreakPoint As Long
    
    iBreakPoint = 41 '1st 41 rows go in column A, the rest goes in Column F
    'Run a shell command, returning the output as a string
    Set oShell = CreateObject("WScript.Shell")

    'run command
    Set oExec = oShell.Exec("RScript ""C:\Users\User\Desktop\Intern\FinviztableScrap.R""")
    Set oOutput = oExec.StdOut

    'handle the results as they are written to and read from the StdOut object
    While Not oOutput.AtEndOfStream
        i = i + 1
        If i <= iBreakPoint Then
            '1st group of rows goes in Column A
            Range("A" & i) = oOutput.ReadLine
        ElseIf i > iBreakPoint Then
            Range("F" & i - iBreakPoint) = oOutput.ReadLine
        End If
        If i = iBreakPoint Then
            'manually adjust the 1st digit of the arrays to split at different characters (column A)
            Range("A1:A" & i).TextToColumns DataType:=xlFixedWidth, _
                FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(6, 1), Array(14, 1), Array(52, 1)), _
                TrailingMinusNumbers:=True
        End If
    Wend
    'manually adjust the 1st digit of the arrays to split at different characters (column F)
    Range("F1:F" & i - iBreakPoint).TextToColumns DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(38, 1), Array(50, 1), Array(60, 1), Array(66, 1)), _
        TrailingMinusNumbers:=True
End Sub

Again, you'll have to play with the 1st digit in the array of arrays to split the characters at the correct point.

 

by Beginner (13 points)

Thanks for your patience with me all the way to explain the question, however, I still don't really understand the array. What is your definition for 3rd character or 6th character? It is the same as the 3rd word which is the "Company" (based on the head of table?)? I just start to learn the VBA coding and really appreciate your helps this few weeks! 

Further, I run the latest code from you, however, it showed some bug which I have no idea how to solve it as well :(

I attached the screenshot below for your reference about the error. Really thanks for your helps!

  

by Super Expert (2.8k points)

Are you sure you copied the new code in its entirety? That error would usually appear if the variable i or iBreakPoint in your Range declaration wasn't defined.

Here's an example explaining the 3rd and 6th charactes to get you pointed in the right direction. Let's say you have the following string entered in Cell A1.

abcdefghijklm

In the string above, "c" is the 3rd character and "f" is the 6th character, so the FieldInfo argument below will split the string so "abc" is in the first column, "def" is in the second column and "ghijklm" is in the third column:

Range("A1").TextToColumns DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(6, 1)), _
        TrailingMinusNumbers:=True

I've made the 3 and 6 larger in the example above so you can tell what numbers I'm referring to. You need to see what character number you want to split your string at and update these digits in the Array functions to correspond to your specific characters. Spaces count as characters, so don't forget to count them, as well.

by Beginner (13 points)

Once again, thanks for your explanation! It was cleared for me know! :) I'm curious how you counted the number of character so fast as I realised each row having different space and different length of text which make me hard to split them, if I have 100 row then it takes huge amount of time to decide which number to put in the Array. So does it means that I have to trial an error until I can get a number for the Array until it can fit in all the row for the table?

Further, I have successfully run the latest code given by you! However, there are still 2 more column below row 41 in excel (attached diagram below) which I don't know how to adjust that, please help me. Thanks for your helps! :)

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...