Search and extract

[Guest]

Hi...

I have 200 identical files that contain data that I need to populate a
standalone table. I have been using the following code superbly well to
extract pinpoint data from targeted sheets.

Sub MakeLinksForGordon()
Dim myCell As Range

For Each myCell In Selection
myCell.Formula = _
"='" & Cells(myCell.Row, 1).Value & "\[" & _
Cells(myCell.Row, 2) & "]" & _
Cells(myCell.Row, 3) & "'!" & _
Cells(1, myCell.Column)
Next myCell
End Sub

However, the sheet name has now become variable as some of the files are no
longer identical. The above code now asks me which sheet I desire to search
when the expected sheet can't be found

What I need to happen is this:

In range b2:b200 I enter the path
In range c2:c200 I enter the file.xls
In cell c3 I enter the sheet name (sheet 1) that will apply to the code but
not the range, because not all of the files in the range will have sheet 1.

Basically what I enter in cell C3 will be the only sheet searched. If the
code comes across a file that doesn't have say sheet 1 then it skips it?

Sounds complex? It is...any help or alternative would be greatly appreciated.

Thanks

Gordon...

 

[mcg]

Gordon napisal(a):

Hi...

I have 200 identical files that contain data that I need to populate a
standalone table. I have been using the following code superbly well to
extract pinpoint data from targeted sheets.

Sub MakeLinksForGordon()
Dim myCell As Range

For Each myCell In Selection
myCell.Formula = _
"='" & Cells(myCell.Row, 1).Value & "\[" & _
Cells(myCell.Row, 2) & "]" & _
Cells(myCell.Row, 3) & "'!" & _
Cells(1, myCell.Column)
Next myCell
End Sub

However, the sheet name has now become variable as some of the files are no
longer identical. The above code now asks me which sheet I desire to search
when the expected sheet can't be found

What I need to happen is this:

In range b2:b200 I enter the path
In range c2:c200 I enter the file.xls
In cell c3 I enter the sheet name (sheet 1) that will apply to the code but
not the range, because not all of the files in the range will have sheet 1.

Basically what I enter in cell C3 will be the only sheet searched. If the
code comes across a file that doesn't have say sheet 1 then it skips it?

Sounds complex? It is...any help or alternative would be greatly appreciated.

Thanks

Gordon...

put name of correct sheet in column D and

For i = 1 To 200
file = Cells(i, 2).Value & Cells(i, 3).Value
Sheet = Cells(i, 4).Value
Workbooks.Open Filename:=file
Sheets(Sheet).Range(your range). ' your action
.....
Next i

mcg