PDA

View Full Version : Solved: Get Web Stats with Webalizer and Analyze



Anne Troy
06-14-2004, 09:03 AM
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
06-14-2004, 10:24 AM
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
06-14-2004, 10:30 AM
That would be VERY cool, yes. :)
Yes, just delete vbaexpress lines.
Nope. Don't need referrer lines cleaned up.

xXLdev
06-14-2004, 12:30 PM
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
06-15-2004, 12:32 AM
Parsing it with RegExp should be straightforward, can u please send me the necessary info

Cheers

Dave

Anne Troy
06-15-2004, 06:32 AM
xXLDev already finished this little project for me.
I've asked him to load it here, but... ?

mark007
06-15-2004, 06:41 AM
I'd be intrigued as to who our referrers are after vbaexpress has been stripped out. Can you publish the standings?

:)

Anne Troy
06-15-2004, 06:48 AM
Sure. :)

xXLdev
06-15-2004, 08:31 AM
Dreamboat,

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

Anne Troy
06-15-2004, 08:36 AM
No problem, Cesar! :) Thank YOU.

brettdj
06-15-2004, 04:49 PM
dang - you have to be quick around here.

Must challenge you to a game of Pacman sometime :)

brettdj
06-15-2004, 05:02 PM
Getting a time out error as per attached jpg - anyone else ?

Scottie P
06-15-2004, 05:06 PM
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
06-15-2004, 05:15 PM
Hm. Not me. :)
Are you prompted to enter the username/password?

brettdj
06-15-2004, 05:31 PM
No

xXLdev
06-15-2004, 06:24 PM
Brett, you should be prompted for a user name and password.

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

Anne Troy
06-15-2004, 06:34 PM
Works divine for me. :)

NateO
06-16-2004, 07:56 AM
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
06-16-2004, 08:03 AM
Well...whatever that did, it sure doesn't flash like it used to. :)

Anne Troy
06-16-2004, 08:04 AM
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. :)

xXLdev
06-16-2004, 08:20 AM
The flashing was stopped using Application.ScreenUpdating.
Setting Application.ScreenUpdating to false, helps increase the performance of your code.

NateO
06-16-2004, 08:24 AM
True, but likely less flashing as well, as not only that, but the revised code does up to two deletes on the entire range, whereas the original does up to x deletes (which can be a large number) up until a blank cell. This also has the potential for significant performance implications. :)

xXLdev
06-16-2004, 09:21 AM
Your code is faster by about 1.5 seconds. I knew that the maximum rows would be 250, so I was not too worried about performance. If the total rows were in the thousands, my solution would have been a poor/slow solution.

You code could be improved in the following ways:
.Range("D5 : D65536").AutoFilter Field:=1, _

.Range("D6 : D65536").SpecialCells(xlVisible).EntireRow.Delete

and delete
If CBool(InStrB(.Range("d6").Value, "vbaexpress")) Then _
.Range("d6").EntireRow.Delete

As in most cases in Excel, there are several ways of solving the problem. Each one teaches us interesting things about Excel.

NateO
06-16-2004, 09:39 AM
Correct, avoid the conditional! That was a generic approach for data beginning in row 1 with no header, but it need not apply here. :)

Are you missing the criteria? How about the following:

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

Also, when one says 1.5 seconds as opposed to what? Is that 100% or 2%, etc... :)

NateO
06-16-2004, 11:32 AM
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. :) How would one know this, old copy or is there a field of some sort?

I'd just download the two and use conditional formatting with a match function for this.

Since my internet explorer is broken right now, and I'm missing your passwords, I can't duplicate my advice, but if you post a sample of two downloads, I can show you. :)

Edit: I think you'd need to maintain a DB of referrers-to-date to effectively determine first time referrers eh.

Anne Troy
06-16-2004, 01:37 PM
Okay, let's check this one out. :)

Anne Troy
06-17-2004, 09:51 AM
Can somebody take that most recent version and "genericize" it for me? I did post at our host's forums and at least one person is interested in it, but I'd like them to easily be able to edit the code, so comments would be REALLY helpful. :)

xXLdev
06-17-2004, 09:52 AM
Dreamboat,

Thanks for posting the file for me. I was getting bleary-eyed and very cranky by the time I sent you the file. I had been up since 4am after only four bad hours of sleep.

Now I feel much better. It is surprising what a good night of sleep will do.

Nate's comment about keeping a list of the referrers is probably what you want. From some other posts you have done, it looks like that is what you are trying to do. Is that correct? If not, do you want this spreadsheet to do that? Or has someone already done that?

Anne Troy
06-17-2004, 09:54 AM
For me, I love it the way it is now. I mean...if someone *falls off the list* as a referrer, I'd like to know if they come back on, so I don't think I need an *all-time* referrers list. Suppose Joe Schmoe links to us in a newsletter or something. Then that dies away. If they link to us in a newsletter a year later, we'd want to see that. :)