1

Here's what I'm trying to do. I have a log file of execution times on a program. Normally, if I want a quick find on how many entries that the function of a program has been executed, average/max execution is to use a combination of grep, gawk, wc, sort and uniq.

i.e. Max execution time:

grep "Execution time " console.log | gawk "{print "$8", "$11"}" | grep "[FUNC NAME]" | gawk "{print "$1"}" | gawk "max==0 || $1 > max{max=$1}END{print max}"

What I want to do know is to extract all the function max/average execution time and occurrence found in the logfile and put the data in an excel file. I'd like the format to be like these: https://docs.google.com/spreadsheets/d/1xS5PrXFdBQD75_jIIcA6IHKX0xLOfi2Enh2nVOsYmhc/edit?usp=sharing

(I'm doing it manually in command prompt to get those values atm)

How do I do these in Excel's VBA?

Community
  • 1
  • 1
MDuh
  • 415
  • 1
  • 7
  • 19
  • Do you want to run Excel on Linux through Wine? Or run Linux tools on Windows through Cygwin? Or just read contents of existing text file (e.g. `*.csv`) into Excel? – xmojmr Oct 17 '14 at 13:37
  • I'm running linux tools in windows through GnuWin and unixtools – MDuh Oct 17 '14 at 16:40
  • 1
    Overall you can first run the command with output redirected to a file (use Google: `site:stackoverflow.com vba wscript.shell`) and then read and parse the text file (use Google: `site:stackoverflow.com excel vba read text file`) and distribute the values across the sheet (Google: `site:stackoverflow.com excel vba set cell value` can give you some hints). Once you have some code ready add it to your question and you may get some more precise answers – xmojmr Oct 17 '14 at 17:04

1 Answers1

0

After some googling and trial and error, I finally manage to do what I want in the excel VBA.

Here's how I solved it:

I create 3 custom functions named CustomMax, CustomAve, CustomCount

Here's a sample code for CustomMax: (note I hardcoded the path name of the file, but you can just set it yourself)

CustomMax:

Function CustomMax(var1 As String)

Dim s As Long, a() As String
s = CreateObject("Wscript.Shell").Exec("cmd.exe /c" & "grep ""Execution time "" ""C:/Users/MDuh/AppData/Local/Red 5 Studios/Firefall/console.log"" | gawk ""{print ""$8"", ""$11""}"" | grep -w """ & var1 & """| gawk ""{print ""$1""}"" | gawk ""max==0 || $1 > max{max=$1}END{print max}""").StdOut.ReadLine
CustomMax = s

End Function

What this is doing is creating a shell object, executing the command line with the command:

grep "Execution time " console.log | gawk "{print "$8", "$11"}" | grep -w "[NAME]" | gawk "{print "$1"}" | gawk "max==0 || $1 > max{max=$1}END{print max}"

Then capturing the output with Stdout.ReadLine. s variable is needed so the output in the worksheet is a number, not a string.

Some caveat that this method are:

  • Every function call will open up a CMD prompt then abruptly close it
  • It will recalculate everytime you do something that will update the worksheet so you might want to set recalculation manually
MDuh
  • 415
  • 1
  • 7
  • 19