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]

What 'part' of the instructions is on the next row?

You don't seem to have mentioned that part.

The main problem I can see is that you seem to want to open the workbooks using hyperlinks.

If you do that it might be hard to get a reference to the workbook and check for the value.

If you opened the workbook(s) in the 'usual' way it shouldn't be a problem.

 

[sdoppke]

What 'part' of the instructions is on the next row?

You don't seem to have mentioned that part.

The main problem I can see is that you seem to want to open the workbooks using hyperlinks.

If you do that it might be hard to get a reference to the workbook and check for the value.

If you opened the workbook(s) in the 'usual' way it shouldn't be a problem.

The next row is the next URL. Its a column of 100 URL's. It should open the first URL, check for a value, return True or False in the cell left of the active cell (left of the cell that has the URL) then close and repeat, using the next URL(next row, same column) and return True or False (next row, left of URL column) and continue this process 100+ times.


Thanks a ton for taking the time to help.

sd

 

[Norie]

That sounds pretty straightforward but I'm still unsure of the hyperlink thing.

Do you need to use hyperlinks?

 

[sdoppke]

That sounds pretty straightforward but I'm still unsure of the hyperlink thing.

Do you need to use hyperlinks?

Yes, (totaly open to another way, if you are aware of one ) the .xlsx files are on our company sharepoint site. My only real aim is to check if they exist and return true or false, but ahve spent countless hours, to only come up with the laborous way of opening each file and returning a consitant value that each one has. to tell me if they exist or not. Sorry for the long explanation, but in short. I think i need to stick with the URL's.

sd

 

[Norie]

I don't know much about Sharepoint, keep on meaning to find out more but...

Do you know if you can open a workbook in VBA like this?

Dim strURL As String
 
      strURL = "\\mysharepoint.mycompany.com\myfile.xls"
 
     Workbooks.Open strURL

Obviously replace the URL with a real one.

 

[sdoppke]

I don't know much about Sharepoint, keep on meaning to find out more but...

Do you know if you can open a workbook in VBA like this?

Dim strURL As String
 
      strURL = "\\mysharepoint.mycompany.com\myfile.xls"
 
     Workbooks.Open strURL

Obviously replace the URL with a real one.

I was able to open the workbook that way!

 

[Norie]

Right.

If you can do that then we don't really need to hyperlink.

Do you have all the URLs/paths/filenames/UNC etc?

ie whatever SharePoint uses

 

[sdoppke]

Right.

If you can do that then we don't really need to hyperlink.

Do you have all the URLs/paths/filenames/UNC etc?

ie whatever SharePoint uses

You bet, in column I(9).

sd

 

[sdoppke]

Right.

If you can do that then we don't really need to hyperlink.

Do you have all the URLs/paths/filenames/UNC etc?

ie whatever SharePoint uses

I came up with this, but it does not move to the next active cell?

Sub test()
Dim strURL As String
On Error Resume Next
Worksheets("Sheet1").Range("I6").Select
strURL = ActiveCell
 For Each c In Worksheets("Sheet1").Range("I:I").Cells
  If c.Value <> " " Then Workbooks.Open strURL
        If Sheets("Week 1").Range("AT1") = "have" Then ActiveCell.Offset(0, -1) = "True" Else ActiveCell.Offset(0, -1) = "False"
 Next c
     
End Sub

I cant exactly figure how to get it to return False is no file is opened (found)

sd