Excel webquery, check for validity

[pkrmott]

I have been using the querytable command within excel to access a user-defined website. If the user inputs an invalid web address, the macro I'm using fails on the querytables part. I have included the code below. Does anyone know of a method that will allow the macro to "check" to ensure that the webaddress is valid before doing the query? In an ideal world the user could then input a correct address.

website = "URL;

http://finance.yahoo.com"

'I'd like to do the check here ----- <

With ActiveSheet.QueryTables.Add(Connection:=website, Destination:=Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With

I really appreciate your help. I am new to TT so please let me know if I have made some egregious error. Thanks again. -P

 

[CautionMP]

pkrmott,
Here is a little routine that may work for you. I used Microsoft WinHTTP Services, version 5.1 since it will throw an error if an invalid URL is passed to [tt]Open()[/tt]. I also used a generic Object container for [tt]objHTTPRequest[/tt] but you could easily add a reference to the Library and implicitly declare a [tt]WinHttpRequest[/tt] object.

Function URLIsValid(URL As String) As Boolean
'Requires Microsoft WinHTTP Services (winhttp.dll) to be registered
'on the host system.
'Version 5.1 was used for this sample in Excel 2000 SR-1
On Error GoTo URLIsValid_Error
Dim objHTTPRequest As Object
Set objHTTPRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
With objHTTPRequest
  .Open 1, URL ' WinHttpRequestOption_URL = 1
  .Send
End With

Clean_Up:
Set objHTTPRequest = Nothing
URLIsValid = True
Exit Function

URLIsValid_Error:
URLIsValid = False
Resume Clean_Up
End Function

In testing [tt]URLIsValid("

http://www.yahoo.com/")

= True[/tt] and [tt]URLIsValid("http:/

http://www.yahoo.com/")

= False[/tt].

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[pkrmott]

I'm not familiar with the details of that code, although it does seem to work for me. I'm curious if I could just use a goto command and direct the user to re-input the web address. If I were to do so, would the error handler work a 2nd time if the use AGAIN entered the wrong address? A preliminary test I ran seems to show that an error handler works only once.

 

[pkrmott]

Further searching seems to indicate that if I were to use an error handler and an associated goto command, the program never actually leaves the error handling stage. Thus, any future errors will not be handled appropriately (or, more exactly, AT ALL) by the error handler.

How do you get around this problem?

 

[CautionMP]

pkrmott,
First there is a bug in the original code, [tt]URLVisValid[/tt] should be set to [navy]True[/navy] before [tt]Clean_Up:[/tt], sorry about that.

...
End With
[b]URLIsValid = True[/b]

Clean_Up:
Set objHTTPRequest = Nothing
[s]URLIsValid = True[/s]
Exit Function
...

The error handler should catch the error each and every time the function is called. To apply this to your original example you could do something like this:

website = "URL;[URL unfurl="true"]http://finance.yahoo.com"[/URL]

'I'd like to do the check here ----- <
[b]Do
  website = InputBox("The address you entered is invalid, please re-enter:", "Address Error", website)
  'Check if the user clicked cancel
  If website = "" Then
    'If you think your user wants to quit exit your routine here
  End if
Loop Until URLIsValid(website)[/b]
    
With ActiveSheet.QueryTables.Add(Connection:=website, Destination:=Range("a1"))

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[CautionMP]

pkrmott,
For error handlers look at [navy]Resume[/navy] or [navy]Resume Next[/navy] instead of [navy]Goto[/navy].

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[pkrmott]

Thanks for the notes, it works great now.