Checking if file exists using WEB ADDRESS?

[thommo41]

Hi,

I had a lovely simple solution (thanks to Mrexcel yet again!) for checking to see if an input file (CSV) existed before trying to import it. Only now it is all done, am I told the imoprt files will be stored on our Intranet...

In theory, I could share out the location of these files as a shared drive, but it's not a good idea considering the platform.

So, is there a way to check for the existence of a file on a website? The files live at http://IPADDRESS:PORT/imports/ but the dir(filename) doesn't like web addresses. Is there any way around this?

Thanks in advance
Alan

 

[Juan Pablo González]

Alan,

Try this function instead:

Function FileExistsURL(ByVal URL As String) As Boolean
   Dim objXmlHttp As Object      'MSXML2.XMLHTTP
   
   Set objXmlHttp = CreateObject("MSXML2.XMLHTTP") 'New MSXML2.XMLHTTP
   
   objXmlHttp.Open "HEAD", URL, False
   objXmlHttp.send ""
   
   FileExistsURL = objXmlHttp.Status = 200
   
   Set objXmlHttp = Nothing
End Function

Use it like

If FileExistsURL("http://yourserver/yourfilelocation") Then

End If

 

[thommo41]

AWESOME! Thank you!

Can you perhaps help me out again? If the file does not exist, I need to display a message saying that it doesn't exist. That bit is easy, but after displaying that message, I need the VBA to completely halt, stop all running routines. Is there a command for this?

Thanks
Alan

 

[Richard Schollar]

Hi Alan

You have two choices here: you could use Exit Sub to exit the current sub-procedure, or you could use End which will literally terminate all code running (this is fairly extreme, but does tie in with your stated aim):

If Not FileExistsURL("http://yourserver/yourfilelocation") Then 
   End
   'or
   'Exit Sub
End If

 

[nemmi69]

I tried this on a Win 7 64bit PC and got an error message "Run-time error '-2147467259(80004005)': Method 'open' of object 'IXMLHTTPRequest' failed"

Alan,

Try this function instead:

Function FileExistsURL(ByVal URL As String) As Boolean
   Dim objXmlHttp As Object      'MSXML2.XMLHTTP
   
   Set objXmlHttp = CreateObject("MSXML2.XMLHTTP") 'New MSXML2.XMLHTTP
   
   objXmlHttp.Open "HEAD", URL, False
   objXmlHttp.send ""
   
   FileExistsURL = objXmlHttp.Status = 200
   
   Set objXmlHttp = Nothing
End Function

Use it like

If FileExistsURL("http://yourserver/yourfilelocation") Then

End If