topic Re: Macro Error-End Statement in QlikView

Macro Error-End Statement

sathish_kumar — Sat, 16 Nov 2024 21:29:44 GMT

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

Re: Macro Error-End Statement

m_woolf — Thu, 31 Jan 2019 14:09:18 GMT

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

Re: Macro Error-End Statement

sathish_kumar — Thu, 31 Jan 2019 14:33:25 GMT

Tried. but not successful..

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

Re: Macro Error-End Statement

marcus_sommer — Thu, 31 Jan 2019 14:48:41 GMT

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

Re: Macro Error-End Statement

sathish_kumar — Thu, 07 Feb 2019 21:50:31 GMT

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

Re: Macro Error-End Statement

marcus_sommer — Fri, 08 Feb 2019 09:03:49 GMT

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

Re: Macro Error-End Statement

sathish_kumar — Sat, 09 Feb 2019 19:39:50 GMT

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?

Re: Macro Error-End Statement

marcus_sommer — Sun, 10 Feb 2019 11:13:02 GMT

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