EXCEL VBA: Look for Dynamic Hyperlink

[JAMES717]

I have a static list of numbers (Vendor ID) and the job cycles through each number and opens a file (if present).

The problem is that the On Error only works the first time through the job. I can not use On Error Resume Next, because I have lines of code that if the file opens that need to be completed.

I can not use “if DIR(myfile) = “” then” because I am trying to lookup a file on the internet and DIR(“

http://xxxxx/yyyy/”

& ActiveCell & “.xls”) is not working.

Sample CODE (as is):

Do Until ActiveCell = ""

On Error GoTo errhndler:

Workbooks.Open FileName:="

http://sharepoint/SC/procure/scorecard/Combined Scorecards/"

& ActiveCell.Offset(0, 1) & " " & ActiveCell & ".xls

 

[JAMES717]

With some minor tweaking, I got the first part to work. I can surely tell the difference in my guerilla coding in your professional coding.

Next error:

on Set ws = Workbooks.Open( _
FileName:=sPath & "On-time%20Shipment/" & r.Offset(0, 1) & " " & r.Value & ".xls")

It File Open, but I am getting a Type mismatch error on that line

 

[SkipVought]

Set ws = Workbooks.Open( _
        FileName:=sPath & "On-time%20Shipment/" & r.Offset(0, 1) & " " & r.Value & ".xls")[b][red].Sheets(1)[/red][/b]

or whatever sheet you want.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JAMES717]

I will finish the coding out and let you know how it looks. Thanks so much for your help.

 

[JAMES717]

Skip,

Looks like it will work, but I can not get the job to go to the next ID. I do not use for each next loops, so I think I may have the next posted inaccuratly.

I do not think I need to use the activeCell.Offset(1,0).Select anymore becaue the for each next loop takes the place of all that, but where do I put the next?

 

[SkipVought]

activeCell.Offset(1,0).Select is an amateur method of looping. It is slow and cumbersome. I rarely use acive anything. Rather I reference object explicitly, using object variables.

Here is the basic control structure...

Sub test()
    Dim r As Range

    On Error Resume Next
    
    For Each r In Range([A2], [A2].End(xlDown))
    
        If Err.Number = 0 Then
        
        Else
          
          Err.Clear
        End If
    
    Next
End Sub

I would put the three folders in a list on a sheet and loop thru each of them...

Sub test()
    Dim r1 As Range, r2 as range

    On Error Resume Next
    
    For Each r1 In Range([A2], [A2].End(xlDown))
      for each r2 in range([i]YourFolderListrange[/i])
        'open the workbook for the r2 folder
        If Err.Number = 0 Then
           'save the workbook
           'copy the sheet
           'close the workbook
        Else
          
          Err.Clear
        End If
      next
    Next

   'whatever you do after all the workbooks have been opened, saved & copied

End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}