Check if URL exists, is so then return true

S

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
647
Hi everyone, Im hoping someone can help me write a function (or mabe a macro?) that will check if a URL exists. If it does, would like it to return true and if it does not, then false. So I can tie the function to a cell, and have the cell return text for either answer.


Sounds pretty simple, but really stumped me. Everything i find in the forum shows how to open the URL. (dont need that).

thanks everyone in advance :)

sd
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
sdoppke said:
Hi everyone, Im hoping someone can help me write a function (or mabe a macro?) that will check if a URL exists. If it does, would like it to return true and if it does not, then false. So I can tie the function to a cell, and have the cell return text for either answer.


Sounds pretty simple, but really stumped me. Everything i find in the forum shows how to open the URL. (dont need that).

thanks everyone in advance :)

sd
Click to expand...


Does anyone have any ideas on this?
sd
 
Upvote 0
Code: 
Function Test_URLExists()
  Dim url As String
  
  url = "http://www.mrexcel.com/forum/showthread.php?t=567315"
  MsgBox url, vbInformation, URLExists(url)
  
  url = "http://xwww.mrexcel.com/forum/showthread.php?t=567315"
  MsgBox url, vbInformation, URLExists(url)
End Function

Function URLExists(url As String) As Boolean
    Dim Request As Object
    Dim ff As Integer
    Dim rc As Variant
    
    On Error GoTo EndNow
    Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")
    
    With Request
      .Open "GET", url, False
      .Send
      rc = .StatusText
    End With
    Set Request = Nothing
    If rc = "OK" Then URLExists = True
    
    Exit Function
EndNow:
End Function
 
Upvote 0
Kenneth Hobson said:
Code: 
Function Test_URLExists()
  Dim url As String
 
  url = "http://www.mrexcel.com/forum/showthread.php?t=567315"
  MsgBox url, vbInformation, URLExists(url)
 
  url = "http://xwww.mrexcel.com/forum/showthread.php?t=567315"
  MsgBox url, vbInformation, URLExists(url)
End Function
 
Function URLExists(url As String) As Boolean
    Dim Request As Object
    Dim ff As Integer
    Dim rc As Variant
 
    On Error GoTo EndNow
    Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")
 
    With Request
      .Open "GET", url, False
      .Send
      rc = .StatusText
    End With
    Set Request = Nothing
    If rc = "OK" Then URLExists = True
 
    Exit Function
EndNow:
End Function
Click to expand...

Thank you so much for the help. Even though you wont see it in my code below. I was able to learn alot from yours and adapt to my needs. However im still not doing what i need it to. In short i just need worksheet "Schedule Template" K7 to read have (true) or have not (false) based on the below function. Can you tell why its not lighting up?

Code: 
Public Function URLExists(sstring As String) As Boolean
Dim url As String
Dim sstring As String
ThisFile = Worksheets("MyStoreInfo").Range("C2")
Area = Worksheets("MyStoreInfo").Range("E8")
Region = Worksheets("MyStoreInfo").Range("F8")
District = Worksheets("MyStoreInfo").Range("C8")
M0nth = Worksheets("Dashboard").Range("O8")
url = "[URL]http://abc.com/[/URL]" & Area & "/" & Region & "/" & District & "/" & M0nth & "Schedule" & ThisFile & ".xlsx"
sstring = Replace(url, " ", "%20")
 
If sstring <> "" Then ThisWorkbook.Sheets("Schedule Dashboard").Range("K7") = "have" Else ThisWorkbook.Sheets("Schedule Dashboard").Range("K7") = "have not"
End Function

Thanks a ton in advance.

sd
 
Upvote 0
Andrew Poulsom said:
You function isn't returning anything and you can't change another cell from a function on a worksheet. Use the function posted by Kenneth and place this formula in K7:

Rich (BB code): 
=urlexists("http://abc.com/"&MyStoreInfo!E8&"/"...StoreInfo!O8&"Schedule"&MyStoreInfo!C2&".xlsx")
Click to expand...

Thank you so much for help. Seems im still get a false reading, and i guess its because there are spaces in the values (i.e. Pacific Northwest)

I have uses this code:
Code: 
Function Test_URLExists()
  Dim url As String
  ThisFile = Worksheets("MyStoreInfo").Range("C2")
  Area = Worksheets("MyStoreInfo").Range("E8")
  Region = Worksheets("MyStoreInfo").Range("F8")
  District = Worksheets("MyStoreInfo").Range("C8")
  M0nth = Worksheets("Dashboard").Range("O8")
  url = "[URL]http://abc.com[/URL]" & Area & "/" & Region & "/" & District & "/" & M0nth & "Schedule" & ThisFile & ".xlsx"
  url = Replace(url, " ", "%20")
  MsgBox url, vbInformation, URLExists(url)
  
End Function
Function URLExists(url As String) As Boolean
    Dim Request As Object
    Dim ff As Integer
    Dim rc As Variant
    
    On Error GoTo EndNow
    Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")
    
    With Request
      .Open "GET", url, False
      .Send
      rc = .StatusText
    End With
    Set Request = Nothing
    If rc = "OK" Then URLExists = True
    
    Exit Function
EndNow:
End Function

and this formual in K7:
Code: 
=IF(URLExists("[URL]http://abc.com/sites/retail/WFM/"&MyStoreInfo!E8&"/"&MyStoreInfo!F8&"/"&MyStoreInfo!C8&"/"&Dashboard!O8&"Schedule"&MyStoreInfo!C2&".xlsx"),"have","have[/URL] not")

Any ideas where im going wrong at this point?

sd
 
Upvote 0
It's extremely difficulty to read such a long formula and it may be that the url is simply not correct. For debugging purposes (and even for real) its best to break it up into cells so you can see the result. For instance, put each item in a cell, then build your url and show it in a cell, which is what you then use in a formula:


<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Directory1/</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Directory2/</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Directory3/</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">File.xlsx</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">http://Directory1/Directory2/Directory3/File.xlsx</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">=URLExists(A5)</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

For what its worth, I find it not necessary to make the replacements with %20 for space in the file name with this function.
 
Upvote 0
xenou said:
It's extremely difficulty to read such a long formula and it may be that the url is simply not correct. For debugging purposes (and even for real) its best to break it up into cells so you can see the result. For instance, put each item in a cell, then build your url and show it in a cell, which is what you then use in a formula:


Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Directory1/</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Directory2/</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Directory3/</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>File.xlsx</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>http://Directory1/Directory2/Directory3/File.xlsx</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>=URLExists(A5)</TD></TR></TBODY></TABLE>
Sheet1​




For what its worth, I find it not necessary to make the replacements with %20 for space in the file name with this function.
Click to expand...

Thanks for the tip that was a great idea. I tried that and it still says its false, even when i see it there with my own eyes :( I checked the URL agaist each other and they are the same and even tried with/with out the %20. No change, still false. The URL is a .xlsx on a sharepoint site. does that make a difference?

sd
 
Upvote 0
I don't really know anything about sharepoint. It's always possible it makes a difference. You can download the files without code?
 
Upvote 0
no solution yet, but just learned something. If i copy the "put together" URL (which visually looks exactly the same as the correct URL, and place it in the browser. No luck. but the original URL placed in a borwser works fine?
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Checking for Worksheets that already exist
2
Replies
11
Views
123
Logit
Logit
L
  • Question Question
Cell referencing issue
Replies
1
Views
110
RoryA
RoryA
P
  • Question Question
TypeScript (or other solutions) for modifying given list of cells (and similar problems)
Replies
0
Views
83
Pingaware
P
MartinS
  • Question Question
Better Approach to Checking For Existence of Worksheet Level Range
Replies
4
Views
431
Micron
M
M
  • Solved
Look if values exist seperately and together, then write Yes or No in cell
2
Replies
11
Views
773
MasterBash
M

Forum statistics

Threads
1,223,842
Messages
6,174,981
Members
452,597
Latest member
Barny72

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top