Check if URL exists, is so then return true

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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

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


Does anyone have any ideas on this?
sd
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,108
Office Version
  1. 365
Platform
  1. Windows
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
 

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
647
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

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
 

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
647
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:


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
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,795
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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.
 

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
647
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.

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
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,795
Office Version
  1. 2019
Platform
  1. Windows
I don't really know anything about sharepoint. It's always possible it makes a difference. You can download the files without code?
 

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
647
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:

Watch MrExcel Video

Forum statistics

Threads
1,122,911
Messages
5,598,819
Members
414,260
Latest member
joishe

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
Top