Macro Error-End Statement

[sathish_kumar]

Hello Users

Below macro works absolutely fine when i use it in Excel  but when i copy the same macro and paste in Qlikview macro module, i keep getting error "Expected End Statement", "Macro PARSE failed,functionality  was lost".

Can you please let me know how to enable this below macro in qlikview? Let me know the changes required for the below macro inorder to be compatible with Qlikview 

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 path As String
    path = "C:\Users\username\Documents\R\R-3.5.1\bin\x64\R.exe CMD BATCH --vanilla --slave C:\Users\username\Desktop\Test.R"
    errorCode = shell.Run(path, style, waitTillComplete)
      
End Sub

[m_woolf]

Try:

Sub RunRscript
Set shell = VBA.CreateObject("WScript.Shell")
waitTillComplete = True
style = 1
path = "C:\Users\username\Documents\R\R-3.5.1\bin\x64\R.exe CMD BATCH --vanilla --slave C:\Users\username\Desktop\Test.R"
errorCode = shell.Run(path, style, waitTillComplete)
End Sub

[sathish_kumar]

Tried. but not successful..

it is not executing. Just macro module opens up with no actions.

[marcus_sommer · Accepted solution]

I think the reference to VBA is just too much in the example from Mwoolf:

Sub RunRscript
Set shell = VBA.CreateObject("WScript.Shell")
waitTillComplete = True
style = 1
path = "C:\Users\username\Documents\R\R-3.5.1\bin\x64\R.exe CMD BATCH --vanilla --slave C:\Users\username\Desktop\Test.R"
errorCode = shell.Run(path, style, waitTillComplete)
End Sub

- Marcus

[sathish_kumar]

Hello Marcus

I added new folder in the file path but this is giving error. When i try with NewFolder with no spaces in between ,it works fine but when i use New Folder with spaces in between it is not working.

My .R file is located in Desktop/New Folder/New Folder 1/Test.R

My qvw file is located in Desktop/New Folder/.qvw

Also how to make relative paths for the above files? and how to create relative path for R 

"..\..\Documents\R\R-3.5.1\bin\X64\R.exe" CMD Batch "New Folder1\Test.R"

i checked this link https://stackoverflow.com/questions/15621395/vbscript-relative-path but i was not successful

Can you please help here?

Sub RunRscript
Set shell = CreateObject("WScript.Shell")
waitTillComplete = True
style = 1
path = "C:\Users\username\Documents\R\R-3.5.1\bin\x64\R.exe CMD BATCH --vanilla --slave C:\Users\username\Desktop\New Folder\New Folder 1\Test.R"
errorCode = shell.Run(path, style, waitTillComplete)
End Sub

@Anonymous

@m_woolf

[marcus_sommer]

If your full-path contained any space (or other special chars) the path needs to be wrapped with double-quotes. By using variables for the path the quotes needs to be included within the variable. This means something like:

path = "any path"

isn't enough because the variable path contained just: any path and no wrapping quotes around it. This means you need to specify the path-variable with something like:

path = """any path"""

or maybe

path = chr(34) & "any path" & chr(34)

By relative paths I'm not sure if they could applied here - if I look on your link it seems that they suggest to build a fullpath by using the fullpath of the batch and then adjusting it appropriate.

- Marcus

[sathish_kumar]

Hello Marcus, 

I tried enclosing below path but  I keep getting error. tried with chr(34) as well but in vain

path =""C:\Users\username\Documents\R\R-3.5.1\bin\x64\R.exe CMD BATCH --vanilla --slave C:\Users\username\Desktop\New Folder\New Folder 1\Test.R""

Let me know what is the issue in the above code?

[marcus_sommer]

Just doubling the quotes isn't enough - depending on the used tool and/or the kind of definition/call are 3 or 4 quotes necessary. Most often is the easiest way to a solution to skip the variable in the first step and to write the path directly in the call to check if the path and the other parameter are valid. If it worked you could replace it with a variable and maybe comment the real call and using a msgbox to check if the variable contained exactly the same content as the manual writing.

- Marcus