topic Macro in qlikview in QlikView

Macro in qlikview

bharatkishore — Wed, 08 Aug 2018 09:44:43 GMT

Hi All,

I have the below VBA macro which is working fine in excel.

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 = "C:\Users\test\Documents\R\R-3.4.3\bin\x64\Rscript C:\Users\test\Documents\text_mst.R"

errorCode = shell.Run(path, style, waitTillComplete)

End Sub

Now i want this macro to work in qlikview, because the macro is not working in qlikview, getting an error like Expected end of statement

Can you please tell me how to do,

Thanks,

Bharat

Re: Macro in qlikview

marcus_sommer — Wed, 08 Aug 2018 11:28:33 GMT

You need to change the syntax because VBS is a bit different to VBA. You could try it in this way:

Sub RunRscript()

Dim shell, waitTillComplete, style, path

waitTillComplete = True

style = 1

path = "C:\Users\test\Documents\R\R-3.4.3\bin\x64\Rscript C:\Users\test\Documents\text_mst.R"

Set shell = VBA.CreateObject("WScript.Shell")

shell.Run(path, style, waitTillComplete)

End Sub

- Marcus

Re: Macro in qlikview

bharatkishore — Thu, 09 Aug 2018 04:45:47 GMT

Hi Marcus,

Thank you so much for your reply.

I am getting an error like Cannot use parentheses when calling a Sub.

Can you please tell me what i need to do.

Thanks,

Bharat

Re: Macro in qlikview

marcus_sommer — Thu, 09 Aug 2018 05:33:43 GMT

Just use: Sub RunRscript instead of: Sub RunRscript()

- Marcus

Re: Macro in qlikview

bharatkishore — Thu, 09 Aug 2018 10:00:38 GMT

Hi Marcus,

Getting same error. Below code i have used.

Sub RunRscript

Dim shell, waitTillComplete, style, path

waitTillComplete = True

style = 1

path = "C:\Users\test\Documents\R\R-3.4.3\bin\x64\Rscript C:\Users\test\Documents\text_mst.R"

Set shell = VBA.CreateObject("WScript.Shell")

shell.Run(path, style, waitTillComplete)

End Sub

Please let me know if anything i am missing

Re: Macro in qlikview

marcus_sommer — Thu, 09 Aug 2018 10:06:36 GMT

Ok. Try it with removing the brackets on the run-statement to:

shell.Run path, style, waitTillComplete

- Marcus

Re: Macro in qlikview

bharatkishore — Thu, 09 Aug 2018 10:12:55 GMT

Thank you Marcus.

But when i click the button then instead of running the macro it is going to macro edit module.

Can you please tell me what i need to do.

Re: Macro in qlikview

marcus_sommer — Thu, 09 Aug 2018 11:01:29 GMT

Your code accessed external resources and needs therefore system-access (about mid within the left panel in the macro-editor). Further I'm not sure if run could handle your second/third parameter - for testing I would comment them. Also placing some msgbox within the code will show where the exections breaks.

- Marcus

Re: Macro in qlikview

bharatkishore — Thu, 09 Aug 2018 12:17:46 GMT

Sure.. thanks .. will try and update you.