Feature request - Paste Special(s) script command

Post Reply
americanaustralian
Posts: 21
Joined: Sat Jul 13, 2013 7:50 pm
Location: Manteca, CA USA

Feature request - Paste Special(s) script command

Post by americanaustralian » Fri Jun 05, 2020 3:04 am

Hmm...apparently there is no way to do a scripted Paste Special. I'm replicating a trailing stop tracking system for my stock portfolio (I have it set up and operating in MS Excel with VBA), and it absolutely requires the functionality of copying a column of cells and then pasting ONLY the values. In my opinion, not having this is a serious deficiency in BasicMaker. It would be nice to have all the Paste options available in BasicMaker scripts.

I can work around it by writing the values into a text file, then reading them back into my spreadsheet, but it would be much better if I could just do it "inside" BasicMaker without reference to any external file.

SuperTech
SoftMaker Team
SoftMaker Team
Posts: 1428
Joined: Wed Mar 11, 2020 5:31 pm

Re: Feature request - Paste Special(s) script command

Post by SuperTech » Fri Jun 05, 2020 6:42 pm

Thanks for this suggestion. I have forwarded this request to our development team.

Meanwhile, there is a way to do it in BasicMaker. For this you have to follow these steps:

1. Open PlanMaker, Ribbon command File | Customize drop-down | Customize Ribbon.

2. In Quick Search type Paste unformatted text.

3. Select Paste unformatted text > Click on Shortcut keys.

4. Click in Text field Press new shortcut key > Press any new shortcut key, for example, Alt+V > Click on Add >Close this dialog.

5. Now you can use SendKey after selecting the desired destination to paste the copies range.

Try this below-mentioned test code after performing above steps:

Code: Select all

Sub Main

Set pm = CreateObject("PlanMaker.Application")

pm.Visible = True
pm.Activate

pm.Range("B5:E17").Copy 
pm.Range("N5").select
SendKeys "%v"
pm.Range("N5:Q17").PasteSpecial

Set pm = Nothing

End Sub

americanaustralian
Posts: 21
Joined: Sat Jul 13, 2013 7:50 pm
Location: Manteca, CA USA

Re: Feature request - Paste Special(s) script command

Post by americanaustralian » Fri Jun 05, 2020 8:58 pm

Thanks for the help, SuperTech. So, I tried exactly what you suggested. Here's the result:

If I step through the code (with the F8 key), execution stops on the "pm.Range("G2:G52").PasteSpecial" line, and gives "Error in line: 36 - Object doesn't support this property or method"

If I run the script with the F9 key, the actual Paste Special operation works as expected, but execution stops on the "pm.Range("G2:G52").PasteSpecial" line and gives the same error code

If comment out the "pm.Range("G2:G52").PasteSpecial" line and step through with the F8 key, nothing happens except the "G" column heading gets the focus...and if I use the F9 key, everything works fine, and the Paste Special succeeds.

SuperTech
SoftMaker Team
SoftMaker Team
Posts: 1428
Joined: Wed Mar 11, 2020 5:31 pm

Re: Feature request - Paste Special(s) script command

Post by SuperTech » Sat Jun 06, 2020 2:24 pm

Just remove this line, this was pasted by mistake. #-o I was doing some more testing and pasted this also. Just remove it and run the code.

Code: Select all

Sub Main
Set pm = CreateObject("PlanMaker.Application")

pm.Visible = True
pm.Activate

pm.Range("B5:E17").Copy 
pm.Range("N5").select
SendKeys "%v"

Set pm = Nothing

End Sub

americanaustralian
Posts: 21
Joined: Sat Jul 13, 2013 7:50 pm
Location: Manteca, CA USA

Re: Feature request - Paste Special(s) script command

Post by americanaustralian » Mon Jun 08, 2020 7:01 pm

Checked the results of running this code (modified with my cell addresses, of course), and it doesn't work. Specifically, the %v command does nothing when the script runs, but using the command manually works as expected.

SuperTech
SoftMaker Team
SoftMaker Team
Posts: 1428
Joined: Wed Mar 11, 2020 5:31 pm

Re: Feature request - Paste Special(s) script command

Post by SuperTech » Mon Jun 08, 2020 10:43 pm

Strange, It's working fine here without any problem. Did you follow all steps? What shortcut key you created for "Paste unformatted text"?

When you are running this code, are you getting any error?

americanaustralian
Posts: 21
Joined: Sat Jul 13, 2013 7:50 pm
Location: Manteca, CA USA

Re: Feature request - Paste Special(s) script command

Post by americanaustralian » Tue Jun 09, 2020 12:19 am

Yes, it is strange. I don't get any errors at any time. If I run the code from PlanMaker with the Run Script ribbon command, the spreadsheet blinks momentarily as if something happened, but nothing gets updated. I tried the Run Script command both from the ribbon and the quick launch toolbar.

If I open BasicMaker and run the script using the F8 key to step through it, nothing works, no momentary blink, no update, no errors. If I run the script using the F9 key, the script runs as expected, but BasicMaker has to be open...I can see the cells being processed, and the update works. As you said, strange. Here's the code:

Dim pm As Object

Sub Main

Set pm = CreateObject("PlanMaker.Application")

pm.Visible = True
pm.Activate

' ***** Update the Trailing Stops worksheet *****

pm.Range("F3:F53").Copy
pm.Range("G3").Select
SendKeys "%v"


pm.Range("A1").Select
Set pm = Nothing
MsgBox "Trailing Stops Updated"

End Sub

SuperTech
SoftMaker Team
SoftMaker Team
Posts: 1428
Joined: Wed Mar 11, 2020 5:31 pm

Re: Feature request - Paste Special(s) script command

Post by SuperTech » Tue Jun 09, 2020 2:23 am

Yes, when I ran this script from PlamMaker now, Sendkeys is not working. I reported this problem to our development team. Meanwhile, you can use this particular script from BasicMaker if required.

Thanks

Post Reply

Return to “BasicMaker 2021 for Windows”