Validate a URL

[Jonmo1]

Is it possible to verify a URL is valid, without actually following the URL?

We have an internal web site that has files I need to download daily. The filenames have date strings in them. I've setup some formulas to make the url based on the NEXT dated file I need to download.

And I don't have direct access to the drive the files are stored on, I can only get them through this web site.

Right now, I have individual macros for each file I need. They'll follow the url and download the file if it's there, or return a message to me if it's not. But there are several different files. I have to run each macro one at a time, at different intervals during the day until they get downloaded.

Is it possible to make a macro loop through all the URLs (I have them stored on a sheet, called "FileDownloader" in Range G2:G10) and check if the URL's are valid (without actually attempting to download the file). I can then make some kind of dashboard to tell me when the files are ready for download.


Thanks

 

[Richard Schollar]

Jonmo

How do you define a valid URL? Do you mean it must begin with www, then have a period (.), then the domain, period, com/net , forward slash (/) etc? Or a URL that actually works (in which case I'm not sure how you would verify that without actually testing said URL by opening it)? If this is an intranet, then your work servers must have some kind of stroed list of the valid URLs - maybe you could access that?

 

[Jonmo1]

How do you define a valid URL?

Thanks for response. I mean does the URL work. I already know the URL's work and are in valid format, if the file exists on the web site. I need to know if the URL works before I try to run it. Right now I'm just running each macro over and over until it works. It would be nice to have a dashboard to tell me "Hey, the file is there now and ready for download"

I had setup a Web Query that downloads the list of files on the site. That worked for me. But I'm now needing to share this with others. Problem is that the web site requires a login. And I have 2003 which allows me to log in within the Web Query. But others have 2000, and I've been unable to figure out how to login within the web query in 2000.

Any ideas for that?

Thanks again.

 

[Richard Schollar]

Regarding xl2000 I'm afraid I have very little experience of using it, so I don't know if there is a workaround available - hopefully somebody else can chip in on this point.

If this site is using some kind of Content Management System, then I would assume that there must be a database table somewhere within it that would hold the available file list. Maybe if you have access to whichever server it is, you could populate a querytable with the file list?? (or ADO it). Not sure if this would be any easier than the web Query approach though

 

[Jonmo1]

Unfortunately I don't have direct access to the network drives this stuff is stored on. I ONLY have access to the web site.

However, I did just discover a possible workaround which would be even bettter. don't know if this should be a new post...

The files are in Zip format. I found that If I open WinRar, I can open the file directly with the URL in the Winrar GUI.

I've made some (very little) command line code that Zips files INTO an archive file. But I'm having difficutly getting it to EXTRACT from the zip file.

 

[Tom Urtis]

Is it possible to verify a URL is valid, without actually following the URL?

Yes, example to be placed in its own standard module:

Private Const FLAG_ICC_FORCE_CONNECTION = &H1
Private Declare Function InternetCheckConnection Lib "wininet.dll" Alias "InternetCheckConnectionA" (ByVal lpszUrl As String, ByVal dwFlags As Long, ByVal dwReserved As Long) As Long
Sub Form_Load()
Dim myURL$
myURL = "http://www.mrexcel.com/"
If InternetCheckConnection(myURL, FLAG_ICC_FORCE_CONNECTION, 0&) = 0 Then
MsgBox "Connection to " & myURL & " could not be made.", 48, "Invalid URL"
Else
MsgBox "Connection to " & myURL & " was successful.", 65, "Verified"
End If
End Sub

Just modify the code to loop through G2:G10 where you keep those URLs listed; worked for me when tested.

 

[Richard Schollar]

Tom, that's neat. Does it effectively 'ping' the web address to confirm it's valid? Will it still work with sub-directories under the main domain?

 

[Tom Urtis]

Yes and Yes as far as I can tell with a few random tests. It pings the address without opening it, and it verified this address
http://www.mrexcel.com/board2/viewforum.php?f=2
which is the message board page to this site
and it verified this address
http://www.mrexcel.com/board2/viewtopic.php?t=304969
which is this particular thread,
and failed for addresses I knew to be false.

Maybe a false positive can occur with all the trillions of possible domains and sites and subdirectories out there, but this might reasonably suffice.

 

[Richard Schollar]

That's so cool

I have to investigate API functions further...

 

[Jonmo1]

that is almost what I need, thanks Tom.

But it seems to be just validating the Domain Part of the url.

the URL's I have are actually downloadable Zip files.

so my URL is
"http://MyCompanyName/SomePath/Filename_MMDDYYY.zip"

So I think it's just validating that the domain and path is valid. doesn't validate the zip file is there.

Because it results in successfull every time, weather the file is there or not.