 |
Seeker
2019-11-26 02:15:23
|
|
I would be extremely grateful if someone can help me out with this. I have the following Visual Basic code. Idea is when I double click on an Excel cell which has symbol name, the chart of that symbol opens in Quantshare.
The procedure to be followed inside Quantshare is implemented as explained in the Quantshare post:
https://www.quantshare.com/sa-515-how-to-send-commands-to-quantshare-from-excel-or-other-applications
Vb code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Shell "C:\Program Files\CTrading\QuantShare\QuantShare.exe TaskManager ChangeSymbol newsymbol: " & ActiveCell, 1
Cancel = True
End Sub
But I am getting the error, "run-time error '5' invalid procedure call or argument"
|
|
|
 |
QuantShare
2019-11-26 10:46:08
0
|
|
I do not have a lot of experience regarding this but have you tried something like (as described in the article)
res = Shell ("c:\......" & ActiveCell, 1)
|
|
|
|
|
Seeker
2019-11-26 13:40:04
0
|
|
Yes. Tried the code as it is also, but gets the same error.
|
|
|
|
|
Seeker
2019-11-26 14:06:33
0
|
|
Is there any OLE object with Quantshare for Excel automation? So that the shell command can be avoided by calling that OLE object.
|
|
|
|
|
Seeker
2019-11-27 00:04:18
0
|
|
To summarize what I have tried:
1. Initially, I tried vba code as it was given in the article which was a button click activating the macro. This was supposed to open in QS the chart of the symbol which is in Excel cell A2.
Sub Button1_Click()
res = Shell("C:\Program Files\CTrading\QuantShare\QuantShare.exe TaskManager ChangeSymbol newsymbol:" + Range("A2"), 1)
End Sub
Here, it was giving 'Run-time error '13' Type mismatch. When I replaced the + sign with &, that error changed to Run-time error '5': Invalid procedure call or argument.
This error continued in every attempt. Lastly, I used it this way where the macro is fired when there is cell selection change in Excel.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A2000")) Is Nothing Then
Shell "C:\Program Files\CTrading\QuantShare\QuantShare.exe TaskManager ChangeSymbol newsymbol:" & ActiveCell.Value, 1
Cancel = True
End If
End Sub
If this could get to work, it would be beautiful as I just need to move down or up in the Excel column which has the symbol name and the chart of that symbol will be opened in the currently active Quantshare chart window. Here the macro will be fired whenever there is a selection change anywhere in the range A1:A2000.
Main objective of this Excel to QS interest is to paste a Screener output into this Excel sheet and just keep browsing through the column which has the symbol name. In Excel, I can use secondary sort which has added benefit. Also if I have to run through a client's portfolio which i have in Excel, i just need to browse through the column which has symbols. If I can get it to work, I will do a detailed article on this for everyone's consumption.
|
|
|
|
|
Seeker
2019-11-27 09:32:51
0
|
|
I have tried with the simplistic version as follows but same error:
res = Shell("C:\Program Files\CTrading\QuantShare\QuantShare64.exe TaskManager ChangeSymbol newsymbol:70001247", 1)
I also tried it in Windows Run as follows:
C:\Program Files\CTrading\QuantShare\QuantShare64.exe TaskManager ChangeSymbol newsymbol:70001247
This works.
So Shell is not able to send commands to Quantshare somehow.
|
|
|
|
|
|
|
Seeker
2019-11-28 04:21:13
0
|
|
Thanks. But still not getting solved.
Shell call works with some programs that i have but does not work with others.
Shell "C:\Program Files\qBittorrent\qbittorrent.exe", 1 'works
Shell "C:\Program Files\AmiBroker\Broker.exe", 1 'works
Shell "C:\Program Files\CTrading\QuantShare\QuantShare.exe", 1 'Run-time error '5': Invalid procedure call or argument
Shell "C:\Program Files (x86)\Equis\MetaStock\MsWin.exe", 1 'same error
|
|
|
|
|
QuantShare
2019-11-28 10:27:28
0
|
|
In this post, a lot of people reported that the Shell used to work but does not work anymore or that it works for some programs and not for others.
https://bytes.com/topic/visual-basic/answers/695711-shell-run-time-error-5-invalid-procedure-call-argument
Here are some suggestions:
1/ Shell """C:\Program Files\CTrading\QuantShare\QuantShare.exe""", 1
2/
QS = "C:\Program Files\CTrading\QuantShare\QuantShare.exe"
Shell QS, 1
3/ Try using only one Shell command in all your code
4/ If this still doesn't work then the problem may come from your Antivirus who is blocking the call. Try to disable it to see.
|
|
|
|
|
Seeker
2019-11-28 20:23:36
0
|
|
Thanks for your solution search. Unfortunately, same error. Removed Kaspersky completely and disabled Windows Defender. Still same error. Mostly, something to do with Windows whereby something in some programs no longer lets Shell calls to proceed . i rolled back recent updates to no avail. Tried other machines. Same issue.
Only solution in future is if Quantshare can have OLE object so that Shell is not needed at all.
|
|
|
|
|
Seeker
2019-12-01 09:40:16
0
|
|
Its a problem with Windows 10 whereby Shell call does not proceed with some applications. I could mange the job (probably, not the best way) with ShellExecute. Here is the VBA code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A2000")) Is Nothing Then
Set objShell = CreateObject("Shell.Application")
param = "TaskManager ChangeSymbol newsymbol:" & ActiveCell.Value
objShell.ShellExecute "C:\Program Files\CTrading\QuantShare\QuantShare.exe", param, "", "open", 1
Set objShell = Nothing
Cancel = True
End If
End Sub
When we move through the Excel cells (within the Range A1 to A2000) which has symbol name, the chart of that symbol is opened in QS. One issue is every time the QS opening prompt (UAC) appears. So, I keep the UAC to 'Never Notify'. Shell call was the best for this as it will just activate the window of the currently opened Quantshare and pass the commands.
|
|
|
|
|
QuantShare
2019-12-02 09:51:58
0
|
|
Please note that there is a way to disable UAC for an application. Please Google this.
|
|
|
|
|
Gregg Egregg
2020-06-29 12:01:57
0
|
|
Hi...I am wondering if anyone has found a way to do the same thing with Google Sheets Vs. Excel?
IOW, double click on a Google Sheets cell which has symbol name, the chart of that symbol opens in Quantshare.
|
|
|
|
|
QuantShare
2020-06-29 12:21:51
0
|
|
Google sheets is web based and thus it is not possible to launch a program locally. There may be a solution for this but it would probably requires developing a program using Google APIs
|
|
|
|
|
Send Symbol from Excel cell to active Quantshare chart window
0
