Is it possible to return how many times my macro has been used over hundreds of files?

[Bassie]

Hey,

I would like to release a bunch of macros bundled into an add-in soon and I was wondering if it was possible to track the amount of times each macro has been used and send it back to me. I currently do have the counting of the macros used in place but sending it to me is what I am struggling with as I dont have any experience with that. I was thinking maybe something http related but I would not know where to start.

Any suggestions?

-
Bassie

 

[Ruddles]

Do you have access to a Web server which allows you to run PHP code? If so, you could create a page which uses PHP to capture the IP address from which the page is load and writes it to a log file on your Web server together with any other information which the page load command passes, for example:-

http://www.mysite.com/logger.html?filename=somefile.xlsm&macroname=somemacro

The PHP might be something like this:-

<?php
  if (getenv ("HTTP_CLIENT_IP"))
    $ip = getenv ("HTTP_CLIENT_IP");
  else
    if (getenv ("HTTP_X_FORWARDED_FOR"))
      $ip = getenv ("HTTP_X_FORWARDED_FOR");
    else
      if (getenv ("REMOTE_ADDR"))
        $ip = getenv ("REMOTE_ADDR");
      else
        $ip = "UNKNOWN";
  $ip = str_pad ($ip , 15);
  $page = getenv ("HTTP_REFERER");
  date_default_timezone_set ("Europe/London");
  $logfilename = date ("Ymd") . ".log";
  $fh = fopen ("./logs/$logfilename" , "a");
  fwrite ($fh , date ("Y/m/d H:i:s - ") . $ip . " - " . $page . "\n");
  fclose($fh);
  return "";
?>

This produces one log file per day called (for example) 20230505.log but the code can be modified to produce one log file per month or per year instead.

You'd end up with a log file containing entries looking something like this:-

05/05/2023 14:00 192.168.0.1 - http://www.mysite.com/logger.html?filename=somefile.xlsm&macroname=somemacro
05/05/2023 14:20 127.0.0.1 - http://www.mysite.com/logger.html?filename=someotherfile.xlsm&macroname=someothermacro

You could then load the log file into Excel to analyse it.

Is any of this any help?

 

[Bassie]

Do you have access to a Web server which allows you to run PHP code? If so, you could create a page which uses PHP to capture the IP address from which the page is load and writes it to a log file on your Web server together with any other information which the page load command passes, for example:-

http://www.mysite.com/logger.html?filename=somefile.xlsm&macroname=somemacro

The PHP might be something like this:-

<?php
  if (getenv ("HTTP_CLIENT_IP"))
    $ip = getenv ("HTTP_CLIENT_IP");
  else
    if (getenv ("HTTP_X_FORWARDED_FOR"))
      $ip = getenv ("HTTP_X_FORWARDED_FOR");
    else
      if (getenv ("REMOTE_ADDR"))
        $ip = getenv ("REMOTE_ADDR");
      else
        $ip = "UNKNOWN";
  $ip = str_pad ($ip , 15);
  $page = getenv ("HTTP_REFERER");
  date_default_timezone_set ("Europe/London");
  $logfilename = date ("Ymd") . ".log";
  $fh = fopen ("./logs/$logfilename" , "a");
  fwrite ($fh , date ("Y/m/d H:i:s - ") . $ip . " - " . $page . "\n");
  fclose($fh);
  return "";
?>

This produces one log file per day called (for example) 20230505.log but the code can be modified to produce one log file per month or per year instead.

You'd end up with a log file containing entries looking something like this:-

05/05/2023 14:00 192.168.0.1 - http://www.mysite.com/logger.html?filename=somefile.xlsm&macroname=somemacro
05/05/2023 14:20 127.0.0.1 - http://www.mysite.com/logger.html?filename=someotherfile.xlsm&macroname=someothermacro

You could then load the log file into Excel to analyse it.

Is any of this any help?

This is great thank you! I am not 100% familiair with it yet but this gives me a very good start to learn something new!