Get Web Stats with Webalizer and Analyze

[Anne Troy]

Almost every day, I am looking at our web stats. The Webalizer software lists 250 referring URLs. I would like to grab all 250, remove any URLs containing the string "vbaexpress", and sort the remaining rows alphabetically by URL. Anyone care to take this on? It would be GREAT if I could more easily see where our traffic is coming from.

Whoever decides to help gets the required info to get to the web stats.
Don't worry. It may not look it, but there ARE non-vbax URLs.

[xXLdev]

Dreamboat,

Are you trying to automate the entire process?
So you click a button in Excel, it goes and downloads the web stats, then strips out the "vbaexpress" sites, the it sorts by name.

For the "vbaexpress" lines, it is good enough to delete the row in Excel?
Do you want referrer line for non-vbaexpress sites to be cleaned up in any way?

[Anne Troy]

That would be VERY cool, yes.
Yes, just delete vbaexpress lines.
Nope. Don't need referrer lines cleaned up.

[xXLdev]

I will give it a quick shot for you. The only potential problem might be automating the pwd. You might have to enter that yourself. Depending on how the pwd is done, one would have to use SendKeys which I am not a big fan of.

[brettdj]

Parsing it with RegExp should be straightforward, can u please send me the necessary info

Cheers

Dave

[Anne Troy]

xXLDev already finished this little project for me.
I've asked him to load it here, but... ?

[mark007]

I'd be intrigued as to who our referrers are after vbaexpress has been stripped out. Can you publish the standings?

[Anne Troy]

Sure.

[xXLdev]

Dreamboat,

Sorry. I did not understand that you wanted to post the file. Thanks for doing it for me.

[Anne Troy]

No problem, Cesar! Thank YOU.

[brettdj]

dang - you have to be quick around here.

Must challenge you to a game of Pacman sometime

[brettdj]

Getting a time out error as per attached jpg - anyone else ?

[Scottie P]

Yes, but I thought that perhaps it was just my machine (had 4 apps and about 6 windows open at the time). Since I am recently at only 50% capacity on memory (I mean the machine!) I thought that 'it' was the cause...

[Anne Troy]

Hm. Not me.
Are you prompted to enter the username/password?

[brettdj]

No

[xXLdev]

Brett, you should be prompted for a user name and password.

Dreamboat, I assume that the program is still working for you.

[Anne Troy]

Works divine for me.

[NateO]

Anne, you might want to replace:

Private Sub RemoveVBAExpress()
     Dim irow As Integer
irow = 6
     While (Cells(irow, 4).Value <> "")
   If (InStr(1, Cells(irow, 4).Value, "vbaexpress") > 0) Then
      Rows(irow).Delete
   Else
      irow = irow + 1
   End If
Wend
End Sub

With

Private Sub RemoveVBAExpress2()
 ' Macro recorded by Nate Oliver
 Application.ScreenUpdating = False
 With Worksheets(1)
    .Range("D6:D65536").AutoFilter Field:=1, _
    Criteria1:="=*vbaexpress*"
    On Error Resume Next
    .Range("D7:D65536").SpecialCells(xlVisible).EntireRow.Delete
    On Error GoTo 0
    If CBool(InStrB(.Range("d6").Value, "vbaexpress")) Then _
        .Range("d6").EntireRow.Delete
        .AutoFilterMode = False
End With
 Application.ScreenUpdating = True
 End Sub

The filter should perform much better than the loop, especially as the range in question grows in terms of row count.

[Anne Troy]

Well...whatever that did, it sure doesn't flash like it used to.

[Anne Troy]

Now, y'all seem to be enjoying this so much... it might be REALLY cool if I could run it, and see in red text or fill or something, any NEW referrers from the previous run.

This uses software called Webalizer. I could go into our host's forum and post this as a download for them to use on their own site.