Adventures in Excel. and where OCD can get you.

[efanton]

Im sure at this stage you have, like myself,  all tried many many different URL's with no success.
I have tried so many that I started to loose track of what I had done and what I had yet to try so decided I needed a way to keep track.
A spreadsheet to keep track,  that will be a simple solution wouldn't it?, well maybe not.  Read on if you're curious as to where obsessive behavior can take you.
.


As is normal in my life I got a bit OCD  ,  but the result of 3 nights solid and very little sleep I think I have come up with something truly impressive.

So I started with three columns,  representing the three parts of a web address (stanza3,stanza4.stanza5).  In each columns I was keeping track of everything I had tried but after a while I realised that there could be endless combinations.
I decided that excel was going to do this for me, but didn't have a clue how to go about it.
Much reading, experimenting, failure and hair-pulling later,  I got it to work

You put this in the top cell of the 4th column and copy it down.

=IFERROR(INDEX($A:$A,IF(INT((ROW(1:1)-1)/(((COUNTA(B:B)-1)*((COUNTA(C:C)-1)))))+2>COUNTA(A:A),-1,INT((ROW(1:1)-1)/(((COUNTA(B:B)-1)*((COUNTA(C:C)-1)))))+2))&"."&INDEX(B:B,MOD(INT((ROW(1:1)-1)/(COUNTA(C:C)-1)),(COUNTA(B:B)-1))+2)&"."&INDEX(C:C,MOD((ROW(1:1)-1),(COUNTA(C:C)-1))+2),"")

This will automatically generate a column of URL's of all combinations of columns A B and C.
WARNING  this list gets extremely long if you have tried as many options as me and it will take excel a LONG LONG time to calculate all options.

Delighted with the results,  but daunted by the prospect of typing in and trying every single one of them individually,  (all 1200 at my first attempt) I realized that even my OCD was not up to the task and I had to find a better solution.
So more reading, research, and hair-pulling until I found a way to convert next column to convert the results in URLs into the correct format (HTTP://xxx.yyy.zzz)
It was quite simple in the end you just use this in the top cell of the next column 

=IF(D1="","","http://"&D1)

The IF state simply prevent empty cells in the previous column from being calculate.  If its empty you create an empty cell, if its not you are tagging HTTP:// to the front of what was in the cell from the previous column.
Initially I converted all these to a hyperlink  using the HYPERLINK function  but then realised that I would still have to click every single entry in the column to try open the page in a browser.  Bad mistake.  Most of them will not even open a webpage.


So more reading, research, and hair-pulling to see if there was a way to see if excel could check whether these were or were not valid web links.
No such function in excel exists  ,  but I was on a mission and my OCD took hold again.  I had read somewhere that you can create your own functions in excel.  I have very little programming experience, but how hard could that be,  its got to be simple hasn't it 

Any how 12 hours more straight,  much reading, research, and hair-pulling  I read that you can create function in excel using VBA.  
The next night I sat down thinking,  I have a rough idea of what to do,  I've done the research,  it really cant be that hard 
What I came up with is this.

Public Function CheckURL(url As String)

Dim request As Object

Set request = CreateObject("WinHttp.WinHttpRequest.5.1")

On Error GoTo haveError

With request

.Open "HEAD", url

.Send

CheckURL = .Status

End With

Exit Function

haveError:

CheckURL = Err.Description

End Function

What it does is essentially allow you to check a cell for URL validity,  you use it like this CheckURL(D3) if cell D3 contains a URL, and it returns the URL Status code.  You might not think yo know what a URL status code is, but you see it all the time.  Enter a wrong web address and we have all seen the dreaded 404 error.  That is a URL status code.  URL status code 200 means that the URL exists and can be accessed.

So in the top cell of the next column i entered the following

=IF(E1="","",CheckURL(E1))

again using an IF statement to prevent excel from trying to process empty cells. Despite this Excel went berserk calculating and processing every cell.  Two hours later excel decided it finally had done what it needed to do and allowed me to actually navigate the spreadsheet again.  Well this wasn't good , determined not to to let a silly program like excel beat me, after a bit of research and more hair pulling, I found out that you can stop excel from automatically recalculating every time you make a change or scroll up or down the spreadsheet. You go to the FORMULA tab/menu and you will find a 'calculations option' icon that you can set to manual.

I now have a column besides each URL telling me its status code and know exactly which entries will give a valid webpage.  In the next column I do a simple check to see which cells return a value of 200, then lookup the corresponding URL and put this in the appropriate cell using a HYPERLINK function.

I now have a column (a very very long column) that has sporadically down through it, a list of hyperlinks.  Splitting my screen so that I can see both my browser and the spreadsheet, I simply click each hyperlink in the spreadsheet and that link opens in the browser.  How cool is that
When you click the link the hyperlink changes colour so that you know the ones you have already clicked.
All I had to do then was look at the webpages that were opened, and check to see whether they were the buried treasure webpage.  Many many click later, and no I have not found buried treasure site

The great thing about this is that I now have a list of weblinks That I have already  tried or are yet to be tried. 
If I want to try new combination I simply add my guesses for stanza 3, 4, and 5 and force excel to recalculate, watch  excel going crazy for a hour or so, and the look to see if it produces any new fresh links that have yet to be clicked.


Cant post the spreadsheet its simply to big,  but if you too are mad enough or OCD enough to try it the above you walks you through it.

Hope you all got a bit of a giggle,  I can laugh too now that its done.
I have decided that if I win this treasure hunt,  I will put the €1000 towards a desperately needed hair transplant