Logging from Microsoft Office and Visual Basic for Applications
While Python is my go-to language for anything substantial, I’ve found that Microsoft Excel and Visual Basic for Applications (VBA) scripting offer a fast path to creating automated and surprisingly sophisticated business dashboards and reports. The Loggly team will tell you that the stuff I do in Excel is simply scary…which I take as a compliment. I guess I do have an unnatural affection for Excel, but I digress…
HTTP POST and GET via VBA (Mac OS X and Windows)
Since we are a logging company, I naturally want to integrate logging and alerting into any automated process I create. So I’d like to share my tips on how to log from any Microsoft Office application – on Windows or Mac. Incidentally, the code is actually a cross-platform solution for any HTTP GET or POST from Microsoft Office.
Loggly supports logging to an HTTPS endpoint, and it’s this facility that you use to log directly from the VBA application. When invoking the function, you can optionally include one or more tags (separated by commas). I always use tags to identify my applications so that I can create source groups that segregate logs from different applications. This simplifies searching and alerting.
In the examples below, replace TOKEN
with your Customer Token. The strEventmsg
input parameter can be any string you like including JSON formatted. Choose the code for either Mac or Windows below.
For Windows
Option Explicit | |
Function blnPost_to_Loggly(ByVal strEvent_msg As String, Optional ByVal strTags As String) As Boolean | |
' | |
' Logging function for Windows | |
' | |
Dim strResult As String | |
Dim strURL As String | |
Const cstrCustToken = "TOKEN" ‘Replace with your Loggly Customer Token | |
Const cstrURLprefix = "http://logs-01.loggly.com/inputs/" | |
If Len(strTags) = 0 Then | |
strURL = cstrURLprefix & cstrCustToken & "/" | |
Else | |
strURL = cstrURLprefix & cstrCustToken & "/tag/" & strTags & "/" | |
End If | |
strResult = strDo_Post(strURL, strEvent_msg) | |
If strResult = "{""response"" : ""ok""}" Then | |
blnPost_to_Loggly = True | |
Else | |
blnPost_to_Loggly = False | |
End If | |
End Function | |
Function strDo_Post(strURL As String, strEvent_msg As String) As String | |
' | |
' HTTP POST routine for VBA on Windows. | |
' | |
Dim winHttpReq As Object | |
Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1") | |
winHttpReq.Open "POST", strURL, False | |
winHttpReq.SetRequestHeader "Content-Type", "text/plain" | |
winHttpReq.Send (strEvent_msg) | |
strDo_Post = winHttpReq.responseText | |
End Function |
For Mac
Option Explicit | |
‘ | |
' strDo_Post() function courtesy of Robert Knight via StackOverflow | |
' http://stackoverflow.com/questions/6136798/vba-shell-function-in-office-2011-for-mac | |
'Declarations for Mac OS X only. Delete for Windows | |
Private Declare Function popen Lib "libc.dylib" (ByVal command As String, ByVal mode As String) As Long | |
Private Declare Function pclose Lib "libc.dylib" (ByVal file As Long) As Long | |
Private Declare Function fread Lib "libc.dylib" (ByVal outStr As String, ByVal size As Long, _ | |
ByVal items As Long, ByVal stream As Long) As Long | |
Private Declare Function feof Lib "libc.dylib" (ByVal file As Long) As Long | |
Function blnPost_to_Loggly(ByVal strEvent_msg As String, Optional ByVal strTags As String) As Boolean | |
' | |
' Logging function for Loggly for both Mac OS X and Windows | |
' | |
Dim strResult As String | |
Dim strURL As String | |
Const cstrCustToken = "TOKEN" ‘Replace with your Loggly Customer Token | |
Const cstrURLprefix = "http://logs-01.loggly.com/inputs/" | |
If Len(strTags) = 0 Then | |
strURL = cstrURLprefix & cstrCustToken & "/" | |
Else | |
strURL = cstrURLprefix & cstrCustToken & "/tag/" & strTags & "/" | |
End If | |
strResult = strDo_Post(strURL, strEvent_msg) | |
If strResult = "{""response"" : ""ok""}" Then | |
blnPost_to_Loggly = True | |
Else | |
blnPost_to_Loggly = False | |
End If | |
End Function | |
Function strDo_Post(strURL As String, strEvent_msg As String) As String | |
' | |
' HTTP POST routine for VBA on Mac OS X. REMOVE Function on Windows | |
' | |
Dim lngFile As Long | |
Dim lngRead As Long | |
Dim strBuff As String | |
Dim strCommand As String | |
Dim exitcode As Long | |
strCommand = "curl " & _ | |
"-H " & Chr(34) & "content-type:text/plain" & Chr(34) & _ | |
" -d " & Chr(34) & strEvent_msg & Chr(34) & " " & _ | |
strURL | |
lngFile = popen(strCommand, "r") | |
If lngFile = 0 Then | |
Exit Function | |
End If | |
While feof(lngFile) = 0 | |
strBuff = Space(50) | |
lngRead = fread(strBuff, 1, Len(strBuff) - 1, lngFile) | |
If lngRead > 0 Then | |
strBuff = Left$(strBuff, lngRead) | |
strDo_Post = strDo_Post & strBuff | |
End If | |
Wend | |
exitcode = pclose(lngFile) | |
'exitcode not used... | |
End Function | |
Function strDo_Post(strURL As String, strEvent_msg As String) As String | |
' | |
' HTTP POST routine for VBA on Windows. REMOVE Function on Mac OS X | |
' | |
Dim winHttpReq As Object | |
Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1") | |
winHttpReq.Open "POST", strURL, False | |
winHttpReq.SetRequestHeader "Content-Type", "text/plain" | |
winHttpReq.Send (strEvent_msg) | |
strDo_Post = winHttpReq.responseText | |
End Function |
The Loggly and SolarWinds trademarks, service marks, and logos are the exclusive property of SolarWinds Worldwide, LLC or its affiliates. All other trademarks are the property of their respective owners.
Loggly Team