February 12, 2020

Setting cell functions in Excel, plus a sneak peak!

Filed under: Announcements,Automation — Marcus Tettmar @ 12:30 pm

Something that often gets asks is ‘Can I insert a function into Excel’ using Macro Scheduler‘s native XL functions?’.

Yes, you can. It may not be obvious from the documentation but with XLSetCell you can output any expression accepted by Excel. It doesn’t have to be a literal value.

You might set a literal value with something like this:

XLSetCell>xlH,Sheet1,2,2,560,result

In Excel you insert a function by starting the input with the ‘=’ sign, so we can do the same with XLSetCell:

XLSetCell>xlH,Sheet1,20,2,=SUM(B1:B19),result

In the same way we can force the cell to text format by preceding the input with an apostrophe:

XLSetCell>xlH,Sheet1,2,2,'560,result

These formats are standard Excel features. All we’re doing here is passing a value to Excel that it understands. But you may not have realised you can do this with Macro Scheduler’s native functions.

Coming Soon – More functions & run ANY VBA code!

A few people have requested more native XL functions. We’re working on it.

For example, a new function to set the cell colour:

RGB>50,150,50,color1
XLSetCellColor>xlH,Sheet1,13,2,color1

But how about being able to run ANY valid Excel VBA you like?

XLRunVBA>xlH,ActiveSheet.Range("B12").Interior.Color = vbRed

It could be a whole block of code:

LabelToVar>vba_code,theCode
XLRunVBA>xlH,theCode

/*
vba_code:
  Range("A1:B11").Select
  ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
  ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$11")
  ActiveSheet.Shapes("Chart 1").IncrementLeft +50
  ActiveSheet.Shapes("Chart 1").IncrementTop -30
*/

These features are in development. Watch them in action here:

The sky is the limit!

Note: At time of writing these functions do NOT exist in the current version. These are in development. Their names and syntax may change. Keep an eye out for updates.

Be the first to get these new features by making sure your maintenance or subscription us up to date ūüôā

February 5, 2020

Macro Scheduler 14.5.7 Update

Filed under: Announcements — Marcus Tettmar @ 6:44 pm

We have today released Macro Scheduler 14.5.7 maintenance update.

Version History | Licensed Downloads

You will note that the user area no longer requests a password. Passwords will no longer be sent. Instead, on entering your email address a ‘magic link’ will be sent to you immediately in an email. Emails are usually pretty instantaneous. Once received, click on the link within and you will be logged straight into the user area.

December 21, 2019

Happy Holidays Everyone!

Filed under: Uncategorized — Marcus Tettmar @ 3:58 pm

Hi all. Just a short note to say Happy Christmas/Hanukkah/interdenominational festive holiday time to everyone.

Here’s a fun post from a few years ago:

Case Study: Macro Scheduler Saves 3600 Elf-Hours and Gets Presents Delivered On Time

All the best from Marcus, Dorian and all at MJT.

October 17, 2019

Version 14.5.5 update. Improved text capture, improved OCR, and more.

Filed under: General — Dorian @ 1:52 pm

It’s been a while in the making, but we just released Macro Scheduler Version 14.5.5.
Registered users can download it here, and the trial version is here.

Version 14.5.5 15/10/2019

– Upgraded Python engine to Python 3.7.4 with ability to run Python 2.7 (PYTHON_DLL variable)
– Updated OCR engine to latest version of tesseract
– Updated native text capture engine
– Fixed: ArrayFind crashes if null array suffix provided
– Fixed: non existent subroutine cause script to end after error even if ignoring error
– Fixed: OCRArea incorrectly giving error on negative coordinates
– Fixed: Access Violation in OCRScreen for large screens (scaling has been removed for OCRScreen).

You should notice an improvement in OCR engine speed. Just this morning we tested OCRImage on a 1700 x 900 BMP image packed with text, and it extracted every character flawlessly in under three seconds.

The native text capture engine has been updated, improving the previous text capture capabilities.

One or two of you were asking about running Python 3.7, so we upgraded the engine to Python 3.7.4.

January 9, 2019

PrettifyScp – Code Clean Up

Filed under: General,Scripting — Marcus Tettmar @ 2:10 pm

I often end up working on Macro Scheduler code that is hard to read because of a lack of indentation. Sometimes this is simply because the code has been chopped and changed so many times it’s become a bit messy and needs a bit of a clean up. And I’m not pointing fingers here either – some of this code is my own!

Indenting code inside code blocks makes it much easier to read. But cleaning up code and sorting out the indentation manually is tedious. So I wrote a little script to do it!

So here is my first PrettifyScp script. Just run it and browse to the .scp file you want to fix up. Wait a few seconds and it will pop up a message when it’s done.

//tokens to identify start and end of indented code
Let>incTokens=^IF.*|^SRT\>.*|^REPEAT\>.*|^WHILE\>.*|^ELSE|^ELSE>
Let>decTokens=^ENDIF|^END\>.*|^UNTIL\>.*|^ENDWHILE.*|^ELSE|^ELSE>

//tokens to identify blocks to ignore
Let>inIgnores=^DIALOG\>.*|^VBSTART|^/\*.*
Let>outIgnores=^ENDDIALOG\>.*|^VBEND|\*/.*

//set tab chars
Let>tabs={"  "}

Let>numTabs=0
Let>inIgnoreSection=0
Let>outText={""}

//ask user for input script file
Input>scp_file,Select script file

If>scp_file<>{""}

  //create backup file name
  ExtractFilePath>scp_file,path
  ExtractFileName>scp_file,scp_name
  ExtractFileName>scp_file,scp_name_no_ext,1
  Let>backupfile=%path%\%scp_name_no_ext%.BAK

  //read in script lines to loop through
  ReadFile>scp_file,scp_data
  //temporarily remove percent symbols to avoid unwanted variable resolution
  StringReplace>scp_data,%,~^PERCENT^~,scp_data
  //split to array of lines
  Separate>scp_data,CRLF,scp_lines

  //if there are lines, loop through them
  If>scp_lines_count>0

    Let>k=0
    Repeat>k

      Let>k=k+1
      Let>this_line=scp_lines_%k%

      //Message>To do: %this_line%

      //left trim the line
      LTrim>this_line,trimmed_line

      //are we going into a section to ignore?
      RegEx>inIgnores,trimmed_line,0,matches,pIgnore,0
      If>pIgnore>0
        Let>inIgnoreSection=1
      Endif

      If>inIgnoreSection=1
        //if we are in a section to ignore, are we coming out of it?
        RegEx>outIgnores,trimmed_line,0,matches,pIgnore,0
        If>pIgnore>0
          Let>inIgnoreSection=0
        Endif
      Else
        //otherwise we are not in an ignore section so see if we need to do anything

        //should we unindent?
        RegEx>decTokens,trimmed_line,0,matches,dec_nm,0
        If>dec_nm=1
          Let>numTabs=numTabs-1
          //capitalise first letter while we're here
          MidStr>trimmed_line,1,1,fChar
          Uppercase>fChar,fChar
          RegEx>(^.{1}),trimmed_line,0,matches,nm,1,fChar,trimmed_line
        Endif

        //create the indent chars
        Let>indent={""}
        If>numTabs>0
          Let>t=0
          Repeat>t
            Let>t=t+1
            Let>indent=%indent%%tabs%
          Until>t=numTabs
        Endif

        //insert indent chars to line to output
        Let>this_line=%indent%%trimmed_line%

        //should we indent further lines?
        RegEx>incTokens,trimmed_line,0,matches,inc_nm,0
        If>inc_nm=1
          Let>numTabs=numTabs+1
          //capitalise first letter
          MidStr>trimmed_line,1,1,fChar
          Uppercase>fChar,fChar
          RegEx>(^.{1}),trimmed_line,0,matches,nm,1,fChar,trimed_line
        Endif

      Endif

      //output the new line
      Let>outText=%outText%%this_line%
      If>k<scp_lines_count
        Let>outText=%outText%%CRLF%
      Endif

    Until>k=scp_lines_count

    //swap percent symbols back in ...
    StringReplace>outText,~^PERCENT^~,%,outText

    //backup the file
    DeleteFile>backupfile
    CopyFile>scp_file,backupfile

    //write output to original file name
    DeleteFile>scp_file
    Let>WLN_NOCRLF=1
    WriteLn>scp_file,res,outText

    MessageModal>Done! Backup saved to %backupfile%

Endif


The old script is renamed to a .BAK file so that your original script is backed up. So if there’s something I haven’t thought of in this first stab and something gets messed up, you can’t lose anything, but hopefully you are backing up your code already!

I’m ignoring code inside Dialogs and VBScript blocks as well as comment blocks. Dialogs shouldn’t be messed with and the indentation is handled by the dialog designer. VBScript could be cleaned up but would need a different parser and there are already VBScript code beautifiers out there so you could copy and paste those. So in this version I haven’t touched VBS blocks.

Hope this is useful. Any feedback or suggestions, or if you notice a bug, please post in the comments below.

December 20, 2018

Season’s Greetings!

Filed under: Announcements,General — Marcus Tettmar @ 1:01 pm

From all of us at MJT we wish you season’s greetings and a wonderful start to 2019.

Here’s a fun little script with a message from Macro Scheduler. Open Macro Scheduler (download the trial if you don’t already have it), click New to create a new macro and paste this code in and hit run.

OnEvent>key_down,VK27,0,Quit
SRT>Quit
  SetControlText>Sparkler,TEdit,1,Complete
  WaitWindowClosed>Sparkler
  Wait>1
  DeleteFile>%temp_dir%\Sparkler.scp
  Exit>0
END>Quit
 
DeleteFile>%temp_dir%\Sparkler.scp
LabelToVar>SparkleScript,vScrData
WriteLn>%temp_dir%\Sparkler.scp,wres,vScrData
 
Dialog>Dialog1
object Dialog1: TForm
  BorderStyle = bsNone
  Caption = 'Happy Holidays'
  ClientHeight = 330
  ClientWidth = 780
  Color = 111111
  Position = poScreenCenter
  object Panel3: TPanel
    Left = 0
    Top = 0
    Width = 780
    Height = 330
    BevelEdges = []
    BevelOuter = bvNone
    Caption = 'And a Happy New Year'
    Color = 111111
    Font.Charset = ANSI_CHARSET
    Font.Color = clRed
    Font.Height = -80
    Font.Name = 'Vladimir Script'
    Font.Style = []
    ParentFont = False
    Visible = False
  end
  object Panel2: TPanel
    Left = 0
    Top = 0
    Width = 780
    Height = 330
    BevelEdges = []
    BevelOuter = bvNone
    Caption = 'Merry Christmas'
    Color = 111111
    Font.Charset = ANSI_CHARSET
    Font.Color = clRed
    Font.Height = -96
    Font.Name = 'Old English Text MT'
    Font.Style = []
    ParentFont = False
  end
  object Panel1: TPanel
    Left = 0
    Top = 0
    Width = 780
    Height = 330
    BevelEdges = []
    BevelOuter = bvNone
    Caption = ''
    Color = 111111
  end
end
EndDialog>Dialog1
 
Show>Dialog1
 
ExecuteFile>%temp_dir%\Sparkler.scp
 
Let>WIN_USEHANDLE=1
  GetWindowPos>Dialog1.handle,Dia1X,Dia1Y
Let>WIN_USEHANDLE=0
Add>Dia1Y,165
Let>StartY=Dia1Y
Let>YFlag=1
Wait>0.3
 
Let>kk=0
Repeat>kk
  Add>kk,4
  If>Dia1Y>{%StartY%+20}
    Let>YFlag=0
  EndIf
  If>Dia1Y<{%StartY%-20}
    Let>YFlag=1
  EndIf
  Add>Dia1X,4
  If>YFlag=1
    Add>Dia1Y,8
  Else
    Sub>Dia1Y,8
  EndIf
  Wait>0.025
  If>Dia1X>40
    SetControlText>Sparkler,TEdit,1,%Dia1X%;%Dia1Y%
  EndIf
  SetDialogProperty>Dialog1,Panel1,Left,kk
Until>kk>750
 
Timer>Begin
 
GetWindowPos>Sparkler,SparkX,SparkY
 
Label>Loop
If>{%Dia1X%<%SparkX%+150}
  Add>Dia1X,1
EndIf
If>Dia1Y>StartY
  Sub>Dia1Y,1
EndIf
If>Dia1YDia1Y,1
EndIf
SetControlText>Sparkler,TEdit,1,%Dia1X%;%Dia1Y%
Wait>0.01
Timer>Stop
If>{%Stop%-%Begin%>5000}
  GoSub>Fade
EndIf
Goto>Loop
 
SRT>Fade
  SetDialogProperty>Dialog1,,AlphaBlend,True
  Let>Fader=255
  Repeat>Fader
    Sub>Fader,5
    SetDialogProperty>Dialog1,,AlphaBlendValue,Fader
    Wait>0.1
  Until>Fader<0
  Let>Fader=255
  SetDialogProperty>Dialog1,Panel3,Visible,True
  SetDialogProperty>Dialog1,Panel2,Visible,False
  SetDialogProperty>Dialog1,,AlphaBlendValue,Fader
  Wait>3
  Timer>Begin
  GetScreenRes>ScreenX,ScreenY
  While>{%Stop%-%Begin%<10000}
    Timer>Stop
    Random>100,Pct
    Add>pct,1
    Let>Dia1X={round(%ScreenX%*(%pct%/100))}
    Random>100,Pct
    Add>pct,1
    Let>Dia1Y={round(%ScreenY%*(%pct%/100))}
    SetControlText>Sparkler,TEdit,1,%Dia1X%;%Dia1Y%
    Sub>Fader,5
    SetDialogProperty>Dialog1,,AlphaBlendValue,Fader
    Wait>0.2
  EndWhile
  SetControlText>Sparkler,TEdit,1,Complete
  WaitWindowClosed>Sparkler
  Wait>1
  DeleteFile>%temp_dir%\Sparkler.scp
   
  Exit>0
END>Fade
 
/*
SparkleScript:
Let>size=200
 
OnEvent>key_down,vk27,0,Quit
 
SRT>Quit
  Exit>0
END>Quit
 
Dialog>Dialog2
object Dialog2: TForm
  BorderStyle = bsNone
  Caption = 'Sparkler'
  Color = 1
  TransparentColor = True
  TransparentColorValue = 1
  object Panel1: TPanel
    Left = 0
    Top = 0
    BevelOuter = bvNone
    Caption = 'Panel1'
    Color = 1
    TabOrder = 0
  end
  object Edit1: TEdit
    Text = '-1000;-1000'
    Visible = False
  end
end
EndDialog>Dialog2
 
Let>WIN_USEHANDLE=1
  MoveWindow>Dialog2.handle,-1000,-1000
Let>WIN_USEHANDLE=0
AddDialogHandler>Dialog2,,OnClose,Quit
SetDialogProperty>Dialog2,,ClientHeight,size
SetDialogProperty>Dialog2,,ClientWidth,size
SetDialogProperty>Dialog2,Panel1,Height,size
SetDialogProperty>Dialog2,Panel1,Width,size
SetDialogProperty>Dialog2,,AlphaBlend,True
SetDialogProperty>Dialog2,,AlphaBlendValue,0
Show>Dialog2
 
Let>halfSize={round(%size%/2)}
Let>85Per={round(%halfSize%*0.85)}
Let>15Per={round(%halfSize%*0.15)}
Let>ang2=0
Let>kk=0
SetDialogProperty>Dialog2,,AlphaBlendValue,255
Repeat>kk
  GetDialogProperty>Dialog2,Edit1,Text,vPos
  If>vPos=Complete
    Let>kk=-100
    Goto>Done
  EndIf
  Separate>vPos,;,Cur
  Sub>Cur_1,%halfSize%
  Sub>Cur_2,%halfSize%
  MoveWindow>Sparkler,Cur_1,Cur_2
  Add>kk,1
  Random>85Per,res
  Add>res,%15Per%
  Random>50,color
  Add>Color,45500
  Random>90,ang2
  Let>ang2=%ang2%*4
  GoSub>Angle,Dialog2,Panel1,ang2,%halfSize%,%halfSize%,%halfSize%,4,1
  Random>90,ang3
  Let>ang3=%ang3%*4
  GoSub>Angle,Dialog2,Panel1,ang3,%halfSize%,%halfSize%,%halfSize%,4,1
  Random>90,ang
  Let>ang=%ang%*4
  SetDialogProperty>Dialog2,Panel1,caption,space
  GoSub>Angle,Dialog2,Panel1,ang,%halfSize%,%halfSize%,res,2,color
  Label>Done
Until>kk<0
 
//Angle Usage:
//GoSub>Angle,Dialog,Object,Angle(in degrees),XStart,YStart,Length,PenSize,PenColor
//Requires Drawline subroutine
SRT>Angle
  Let>DegreeAngle=Angle_var_3
  Let>XStart=Angle_var_4
  Let>Ystart=Angle_var_5
  Let>LineLength=Angle_var_6
  Let>RadAngle={%DegreeAngle%*(pi/180)}
  Let>XEnd={trunc((cos(%RadAngle%))*%LineLength%)}
  Let>YEnd={trunc((sin(%RadAngle%))*%LineLength%)}
  Let>XEnd=%XEnd%+%XStart%
  Let>YEnd=%YEnd%+%YStart%
  GoSub>DrawLine,%Angle_var_1%.%Angle_var_2%.Handle,Angle_var_7,Angle_var_8,XStart,YStart,XEnd,YEnd
END>Angle
 
SRT>DrawLine
  LibFunc>user32,GetDC,HDC,%DrawLine_var_1%
  LibFunc>gdi32,CreatePen,Penres,0,%DrawLine_var_2%,%DrawLine_var_3%
  LibFunc>gdi32,SelectObject,SOPres,hdc,Penres
  Libfunc>gdi32,MoveToEx,mtres,HDC,%DrawLine_var_4%,%DrawLine_var_5%,0
  LibFunc>gdi32,LineTo,ltres,hdc,%DrawLine_var_6%,%DrawLine_var_7%
  LibFunc>gdi32,DeleteObject,DOres,Penres
  LibFunc>user32,ReleaseDC,RDCres,HDC_1,HDC
END>DrawLine
*/

Thanks to Dick Lockey for writing this little script (back in 2014).

Remember this?
Case Study: Macro Scheduler Saves 3600 Elf-Hours and Gets Presents Delivered On Time

November 21, 2018

Macro Scheduler 14.5 Available

Filed under: Announcements — Marcus Tettmar @ 8:43 pm

Macro Scheduler build 14.5 is now available for download from the usual places:

Registered Downloads/Upgrades | Trial Versions | New Licenses | Version History

This version adds an improved JSONParse function with improved JSON Path parsing and output of arrays for multiple matches. We’ve also added the long awaited ability to add custom headers to HTTPRequest; the ability to retrieve formulas with XLGetCell instead of just values; support for adding HTML format to the clipboard with PutClipboard and a bunch of other improvements and some small fixes. ¬†For full details see the history list.

July 20, 2018

Cross Language 128 and 256 Bit AES Encryption

Filed under: Scripting,Web/Tech — Marcus Tettmar @ 10:47 am

With Macro Scheduler 14.4.10 we have added new 128 and 256 bit AES Encryption methods which interoperate well with other languages.

Cross-platform/cross-language encryption/decryption can be tricky. Different text encodings, different padding formats and understanding how different algorithms derive keys and initialisation vectors can make encrypting and decrypting between one language and another a bit of a pain!

Macro Scheduler’s AES function originally worked only on Unicode strings as this is the standard string format in Macro Scheduler. ¬†Having had some issues trying to make this work between NodeJS and PHP we decided to create some new UTF8 based AES options (available in Macro Scheduler 14.4.10) which we have verified work well with PHP/OpenSSL and NodeJS. ¬†

These new implementations provide 128 and 256 bit AES encryption. ¬†They use CBC chaining method and use a SHA256 password. If using AES 128 the SHA256 password is truncated to the 32 byte key length. ¬†By default the initialisation vector is set to “0000000000000000” but can be set to whatever you want (but must be 16 characters long). Padding is PKCS#5.

Here is an example of AES_256:

Let>AES_ALG=AES_256_CBC
AESEncrypt>hello world,mypassword,ENCRYPT,result
AESEncrypt>result,mypassword,DECRYPT,original


Here is a compatible PHP example:

data = "hello world";

$method = 'AES-256-CBC';

// simple password hash
$password = 'mypassword';
$key = hex2bin(substr(hash('sha256', $password),0,64));

echo "Method: " . $method . "\n";
$encrypted = encrypt($data, $key, $method);
echo "Encrypted: ". $encrypted . "\n";
$decrypted = decrypt($encrypted, $key, $method);
echo "Decrypted: ". $decrypted . "\n"; // plain text

function encrypt(string $data, string $key, string $method): string
{
  $iv = "0000000000000000";
  $encrypted = openssl_encrypt($data, $method, $key, OPENSSL_RAW_DATA, $iv);
  $encrypted = base64_encode($encrypted);
  return $encrypted;
}

function decrypt(string $data, string $key, string $method): string
{
  $data = base64_decode($data);
  $iv = "0000000000000000";
  $data = openssl_decrypt($data, $method, $key, OPENSSL_RAW_DATA,$iv);
  return $data;
}

Try this PHP Code Here

And here’s the Javascript equivalent:

let data = 'hello world';
let password = 'mypassword';
let iv = '0000000000000000';

let password_hash = crypto.createHash('sha256').update(password,'utf8').digest('hex');

let key = hex2bin(password_hash);
password_hash = Buffer.alloc(32,key,"binary");

let cipher = crypto.createCipheriv('aes-256-cbc', password_hash, iv);

let encryptedData = cipher.update(data, 'utf8', 'base64') + cipher.final('base64');

console.log('Base64 Encrypted:', encryptedData);

let decipher = crypto.createDecipheriv('aes-256-cbc', password_hash, iv);

let decryptedText = decipher.update(encryptedData, 'base64', 'utf8') + decipher.final('utf8');

console.log('Decrypted Text:', decryptedText)

function hex2bin(hex)
{
  var bytes = [], str;

  for(var i=0; i< hex.length-1; i+=2)
    bytes.push(parseInt(hex.substr(i, 2), 16));

  return String.fromCharCode.apply(String, bytes);
}

Try this Javascript code here.

You can try the Javscript and PHP examples at repl.it:

https://repl.it/@MarcusTettmar/phpaes256cbc1
https://repl.it/@MarcusTettmar/jsaes267cbc1

May 15, 2018

Nice Customer Testimonial

Filed under: General — Marcus Tettmar @ 12:20 pm

Just had this testimonial from a recent customer and had to share it with you:

“Just wanted to take some time to say thank you for helping us out on this project at such short notice. We’d sunk quite some time into getting up to speed with a¬†competing product, and had bought personal licenses as well as evaluation and then to find that it could not script all the parts we needed, and frankly how terrible their support was left us in a very bad spot – thank God we’d not stumped up the ¬£3k for their .NET module which we were on the verge of doing. We quickly ran through 4 other web automation packages – none of which were up to the job for our task, or the learning curve was too steep to find out – then stumbled on your Macro Scheduler. If only we’d got to you first. Thanks again, I have no doubt we’ll find use for Macro Scheduler on other projects – it’s a very useful tool to have in the tool-box now.”

You can read more testimonials here.

April 19, 2018

Macro Scheduler Subscriptions from only 12.50/month

Filed under: Announcements,General — Marcus Tettmar @ 8:40 am

Did you know we offer monthly, quarterly or yearly subscriptions for Macro Scheduler Standard?

These subscriptions have no minimum term, so can work out very cost effective if you only need the product for a short while. You may also prefer the budgeting aspect of fixed priced subscriptions over the up front cost of a perpetual license and then potential future upgrade or maintenance costs.  I know I do.

A subscription gives you access to the latest version of Macro Scheduler for as long as your subscription is active. So maintenance and upgrades are included and you never go out of date.

Check our the subscription options here.

 

« Newer PostsOlder Posts »