Installing the Add-in
- Click on w3c.zip to download the add-in
- Unzip and save 'Import W3C Web Logs.xla' into the appropriate add-ins directory (for example, C:\Program Files\Microsoft Office\Office\Library)
- Start Excel
- Select 'Add-Ins...' from the 'Tools' menu
- Use the 'Browse' button to locate the add-in 'Import W3C Web Logs.xla'
- Check the 'Import W3C Web Server Logs' add-in as shown below:
Select a File for Opening
To import a W3C web server log file, select 'Open W3C Web Server Log File' from the 'Capacitas' menu, as shown below:
The following code is used to request the name of the W3C web server log to be opened:
WebLogFileName = Application.GetOpenFilename(FileFilter:="W3C Web Server Logs (*.log), *.log", Title:="Capacitas")
This allows the user to select a W3C web server log file for opening as shown below:
Open the File
The following code ensures that if the user clicks the Cancel button then an error message is displayed, otherwise the chosen file is opened:
If WebLogFileName = False Then
ErrorMessage = "No W3C Web Server Log file was selected for opening"
GoTo ErrorHandler
Else
Workbooks.OpenText Filename:=WebLogFileName, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False, Space:=True, Other:=False, TrailingMinusNumbers:=True
End If
Check the File Format
A check is then conducted to ensure the first cell contains the expected value, meaning it is in the correct format. If not, then an error message is displayed and the log file is closed:
If ActiveSheet.Cells(1, 1) <> "#Software:" Then
ErrorMessage = "The selected log file is not in the expected format"
ActiveWorkbook.Close
GoTo ErrorHandler
End If
Remove Headers
Custom-written sub functions are used to remove unnecessary and repeated headers from the log file data:
Run RemoveRows("#Software:", True)
Run RemoveRows("#Version:", True)
Run RemoveRows("#Date:", True)
Run RemoveCells("#Fields:")
Run RemoveRows("date", False)
For example, the RemoveCells function deletes all cells containing the specified text:
Function RemoveCells(StringToRemove As String) As Variant
With ActiveSheet.Range("A:A")
Do
Set CellsToDelete = .Find(StringToRemove)
If Not CellsToDelete Is Nothing Then
CellsToDelete.Delete Shift:=xlToLeft
End If
Loop While Not CellsToDelete Is Nothing
End With
End Function
Example
The following screenshots show an example W3C web server log file that has been opened firstly using 'Open' from the 'File' menu, and then using the add-in:
W3C web server log file opened using 'Open' from the 'File' menu
W3C web server log file opened using the add-in
