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

 

[Tom Urtis]

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.

I think we are talking about 2 different things here, if I understand the issue. One thing is the link itself, and it stands to reason that a successful result occurs with those files because they really are valid links by definition.

The other item is if the example Filename_MMDDYYY.zip file was actually uploaded to that link by the webmaster or whoever maintains those zips, and, if the file was uploaded, was its name ever changed on the host server but not re-uploaded, or any other possible action that can take place with downloadable files to render them having nothing to download.

The point is, I think, that you really don't need to verify if a link exists, or at most, verification of the link is only half of what you need. The other component is verifying that the file exists behind the valid link, a different story. So maybe combining the code you have with an error check for attempting to download what is not downloadable may be what you need to do, again, if I get what the task is.

 

[Jonmo1]

Well, I came up with a rather cumbersome way to do it.

After I found I could open the URL with Winrar, I thought I could also do it with windows explorer. Just put the URL in the address bar. That worked.

So then I thought maybe I could open the file with excel. From File - Open. I can, but since they are zip files, it said it's not a valid file type. But I said open it anyway, and it did. It's a big jumbled mess of gobledy gook, but it opened. And that's ok, I'm not actually working with the file at this point, just testing to see if it's there.

so then I used workbooks.open and displayalerts = false to open the file.

So here's the code I came up with if you're interested.

Sub FileDownloader2()
Dim MyFullFile, MyFileType, MyFileName, MyString As String
Dim i As Long
Dim Curbook As Workbook

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.AskToUpdateLinks = False

MyString = ""
For i = 2 to 10
    With Sheets("FileDownloader")
        MyFullFile = .Range("G" & i).Value
        MyFileType = .Range("A" & i).Value
        MyFileName = .Range("C" & i) & .Range("E" & i) & ".zip"
    End With
    On Error Resume Next
    Workbooks.Open MyFullFile
    If ActiveWorkbook.Name = MyFileName Then
        Set Curbook = Workbooks(MyFileName)
        Curbook.Close
        MyString = MyString & MyFileType & ","
    End If
Next i
If MyString = "" Then
    MsgBox "No files Ready For Download"
Else
    MsgBox "The files for " & MyString & " Are Ready For Download"
End If

Reset ' Macro to reset application settings
End Sub

 

[writetoevv]

Hi

In an excel file, i have links like below in column A. i want to validate the below links as well as identify whether the files actually present or not . if it is present, then Ok should be appear after that link in B column.otherwise Not Ok.

filename is like below.
ab090312_xxx.zip
ab100312_xxx.zip
ab110312_xxx.zip
ab120312_xxx.zip
ab130312_xxx.zip
ab140312_xxx.zip
ab150312_xxx.zip
ab160312_xxx.zip

full path of links are given below
http://www.xxxxxxxx.com/xxxxxxxx/ab090312_xxx.zip
http://www.xxxxxxxx.com/xxxxxxxx/ab100312_xxx.zip
http://www.xxxxxxxx.com/xxxxxxxx/ab110312_xxx.zip
http://www.xxxxxxxx.com/xxxxxxxx/ab120312_xxx.zip
http://www.xxxxxxxx.com/xxxxxxxx/ab130312_xxx.zip
http://www.xxxxxxxx.com/xxxxxxxx/ab140312_xxx.zip
http://www.xxxxxxxx.com/xxxxxxxx/ab150312_xxx.zip
http://www.xxxxxxxx.com/xxxxxxxx/ab160312_xxx.zip

can some one plz alter the below code for the same?

thanks in advance

--writetoevv

 

[writetoevv]

Hi

Can u let me know what should be present in cloumn G, A, C ,E.

It will be helpful if you can post snapshot of excel with above columns here.

plz reply.

Well, I came up with a rather cumbersome way to do it.

After I found I could open the URL with Winrar, I thought I could also do it with windows explorer. Just put the URL in the address bar. That worked.

So then I thought maybe I could open the file with excel. From File - Open. I can, but since they are zip files, it said it's not a valid file type. But I said open it anyway, and it did. It's a big jumbled mess of gobledy gook, but it opened. And that's ok, I'm not actually working with the file at this point, just testing to see if it's there.

so then I used workbooks.open and displayalerts = false to open the file.

So here's the code I came up with if you're interested.

Sub FileDownloader2()
Dim MyFullFile, MyFileType, MyFileName, MyString As String
Dim i As Long
Dim Curbook As Workbook
 
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.AskToUpdateLinks = False
 
MyString = ""
For i = 2 to 10
    With Sheets("FileDownloader")
        MyFullFile = .Range("G" & i).Value
        MyFileType = .Range("A" & i).Value
        MyFileName = .Range("C" & i) & .Range("E" & i) & ".zip"
    End With
    On Error Resume Next
    Workbooks.Open MyFullFile
    If ActiveWorkbook.Name = MyFileName Then
        Set Curbook = Workbooks(MyFileName)
        Curbook.Close
        MyString = MyString & MyFileType & ","
    End If
Next i
If MyString = "" Then
    MsgBox "No files Ready For Download"
Else
    MsgBox "The files for " & MyString & " Are Ready For Download"
End If
 
Reset ' Macro to reset application settings
End Sub