check multiple URLs (each one in a row) by opening each one at a time and returning a value

[sdoppke]

Hi everyone, I have script that opens a file on a SharePoint site based on the URL that is in a cell. It opens the file then returns a value in one of its cells, then closes the file and is done. I need to modify that script to open the next URL in the cell below the previous one and follow the same procedure. This will need to go on for 2000 URLs (wow) till there are no more. Can anyone help with that.

Thanks in advance

:]
Sd

 

[sdoppke]

Hi everyone, I have script that opens a file on a SharePoint site based on the URL that is in a cell. It opens the file then returns a value in one of its cells, then closes the file and is done. I need to modify that script to open the next URL in the cell below the previous one and follow the same procedure. This will need to go on for 2000 URLs (wow) till there are no more. Can anyone help with that.

Thanks in advance

:]
Sd

Wondering if anyone has any ideas on this? If it helps here is the code i use for just one URL:

Sub Place_Path()
Dim wb As Workbook
On Error Resume Next
ThisFile = Worksheets("MyStoreInfo").Range("C2")
Area = Worksheets("MyStoreInfo").Range("E8")
Region = Worksheets("MyStoreInfo").Range("F8")
District = Worksheets("MyStoreInfo").Range("C8")
M0nth = Worksheets("Schedule Dashboard").Range("F3")
CheckThis = M0nth & "Schedule" & ThisFile & ".xlsx"
fPath = ("[URL]http://infonet.abcdefg.com/sites/retail/WFM/[/URL]" & Area & "/" & Region & "/" & District & "/" & M0nth & "Schedule" & ThisFile & ".xlsx")
sstring = Replace(fPath, " ", "%20")
Sheets("MyStoreInfo").Range("G2") = sstring
Application.ScreenUpdating = False
Application.DisplayAlerts = False
BookName = CheckThis
Set wb = Workbooks.Open(Filename:=sstring)
BookCount = Workbooks.Count
For i = 1 To BookCount
If BookName = Workbooks(i).Name Then
BookCheck = 1
GoTo 1
Else: BookCheck = 2
End If
Next i
1 If BookCheck = 1 Then
For Each wb In Application.Workbooks            ' Loop through each workbook
        If wb.Name <> ThisWorkbook.Name Then        ' Exclude this workbook
            For Each ws In wb.Sheets                ' Loop through each worksheet of each workbook
                If ws.Name = "Week 1" Then
                Sheets("Week 1").Select
                Sheets("Week 1").Range("AT1").Copy
                ThisWorkbook.Activate
                Sheets("Schedule Dashboard").Select
                Range("W11").Select
                ActiveSheet.Paste
                Range("F3").Select
            End If
        Next ws
    End If
Next wb
                    For Each wb In Application.Workbooks()
                        If wb.Name <> ThisWorkbook.Name Then wb.Close wb.Saved = True
                    Next
 
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
If BookCheck = 2 Then
Sheets("Schedule Dashboard").Range("W11") = "have not"
End If

Thanks sd

 

[sdoppke]

I have created a spread sheet that has all the URL's in column A and the soon to be values after opened and checked in column B

Does anyone have an idea how to run the below script: The important detail is the sstring and the value destination move on to the next row each time. (See below, the first and last lines of the script)

Set wb = Workbooks.Open(Filename:=sstring) 'the first sstring is in A4 and then A5 and A6 etc....
BookCount = Workbooks.Count
For i = 1 To BookCount
If BookName = Workbooks(i).Name Then
BookCheck = 1
GoTo 1
Else: BookCheck = 2
End If
Next i
1 If BookCheck = 1 Then
For Each wb In Application.Workbooks            ' Loop through each workbook
        If wb.Name <> ThisWorkbook.Name Then        ' Exclude this workbook
            For Each ws In wb.Sheets                ' Loop through each worksheet of each workbook
                If ws.Name = "Week 1" Then
                Sheets("Week 1").Select
                Sheets("Week 1").Range("AT1").Copy
                ThisWorkbook.Activate
                Sheets("Schedule Dashboard").Select
                Range("W11").Select
                ActiveSheet.Paste
                Range("F3").Select
            End If
        Next ws
    End If
Next wb
                    For Each wb In Application.Workbooks()
                        If wb.Name <> ThisWorkbook.Name Then wb.Close wb.Saved = True
                    Next
 
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
If BookCheck = 2 Then
Sheets("Schedule Dashboard").Range("B4") = "have not"   'This is the posted result, then the next one would need to be posted in B5 and then B6 etc....
End If

Thank you so much in advance.

sd

 

[sdoppke]

Hoping to get some guidance here on Running scrips from the last post. I just cant figure out how to tell it to start over again (and again and again ) and get each URL from the next line (2000 lines) and place the result in the next line and so on.


Realy appreciate any examples or ideas.

sd