Loop to run a task and to get part of the "intructions" from the next row

[sdoppke]

Hi everyone, i know the title probably did more harm than good in explaining what im after. I have a column of URL's and need a loop to open the first URL check for a value in a cell (of the newly opened wb), return True or False in the main wb (in the cell of same row, next column of the URL), close the newly opened wb, then move, start ove on the next row, etc. till there are no more URL's to open.

Hope that made sence, thanks again for any help

sd

 

[Norie]

This code should loop through all the URLS, open the workbooks and then close them.

You'll need to add code between the opening/closing to do whatever it is you want to do.

Option Explicit
 
Sub test()
Dim wbOpen As Workbook
Dim strURL As String
Dim rng As Range
 
    Set rng = ThisWorkbook.Worksheets("Sheet1").Range("I6")

    While rng.Value <> ""
    
        strURL = rng.Value
 
        ' open and create a reference workbook at the end of strURL
        Set wbOpen = Workbooks.Open(strURL)
 
        ' do stuff with the workbook you've just opened
        '....
        '....
        ' close workbook once it's finished with
 
        wbOpen.Close
 
        Set rng = rng.Offset(1)
    Wend
    
End Sub

 

[sdoppke]

This code should loop through all the URLS, open the workbooks and then close them.

You'll need to add code between the opening/closing to do whatever it is you want to do.

Option Explicit
 
Sub test()
Dim wbOpen As Workbook
Dim strURL As String
Dim rng As Range
 
    Set rng = ThisWorkbook.Worksheets("Sheet1").Range("I6")
 
    While rng.Value <> ""
 
        strURL = rng.Value
 
        ' open and create a reference workbook at the end of strURL
        Set wbOpen = Workbooks.Open(strURL)
 
        ' do stuff with the workbook you've just opened
        '....
        '....
        ' close workbook once it's finished with
 
        wbOpen.Close
 
        Set rng = rng.Offset(1)
    Wend
 
End Sub

Norie just wanted to say thank you. I was able to figure it our with your help. Here is what I came up with. seems pretty slow, but it does work...

BTW are you really in Yaren?

Sub CheckSchedules()
Dim wbOpen As Workbook
Dim strURL As String
Dim rng As Range
Application.DisplayAlerts = False
    Set rng = ThisWorkbook.Worksheets("Sheet1").Range("I6")
    While rng.Value <> ""
    
        strURL = rng.Value
 
        On Error GoTo DeadURL
        Set wbOpen = Workbooks.Open(strURL)
        If Not DeadURL Then rng.Offset(0, -1) = "True"
    
    wbOpen.Close
        
        Set rng = rng.Offset(1)
    Wend
DeadURL:
rng.Offset(0, -1) = "False"
Resume Next
Application.DisplayAlerts = True
End Sub

sd

 

[Norie]

sd

So does that code work?

Do you know what's slowing things down?

Is it the opening of the files?

Maybe there's some other way to do whatever it is you want to do without opening the files?

 

[sdoppke]

sd

So does that code work?

Do you know what's slowing things down?

Is it the opening of the files?

Maybe there's some other way to do whatever it is you want to do without opening the files?

Sure does work.
Yep its the opening of the files
Im not aware of a way to ping the file and return a True or False based on it existing or not.

sd

 

[Norie]

sd

Is that what you need to do, check that the file exists?

 

[sdoppke]

sd

Is that what you need to do, check that the file exists?

yep, that's it.

 

[sdoppke]

sd

Is that what you need to do, check that the file exists?

Its a pretty dirty and laborous way to check if a file exists isnt it. I just can find another way to do it.

sd

 

[Norie]

I've seen some things when I search but the they all seem to be workaround type things.

For example I saw one that used the FileDownloadURL<SUP>*</SUP> API.

From what I could work it that would involve trying to download the file.

If the download fails then the file isn't there, if it doesn't fail it is there.

I'm sure it works but you could also end up downloading a whole load of files.

Not sure what the of impact of doing that would be.

<SUP>*</SUP> Might not be the exact name, but it's something like that.

 

[sdoppke]

I've seen some things when I search but the they all seem to be workaround type things.

For example I saw one that used the FileDownloadURL<SUP>*</SUP> API.

From what I could work it that would involve trying to download the file.

If the download fails then the file isn't there, if it doesn't fail it is there.

I'm sure it works but you could also end up downloading a whole load of files.

Not sure what the of impact of doing that would be.

<SUP>*</SUP> Might not be the exact name, but it's something like that.

I think i have seen the same things. Thanks again for all the help. The solution you helped me with, gets the job done.

sd