Do this function for each row in worksheet
 

hi all

the code below gets a path from d1, a filename from k1 and gets a value from
that closed worksheet and displays result in a MsgBox

A need it to do two things.... I need it to loop through each row on my
worksheet, get the value from the closed workbook and paste the result in
column A.

Am not familiar with Loop functions - there are about 2000 rows of data (ie
2000 different files listed that I need it to get a value from, the value
will always be on sheet1 and "b6" on the closed workbooks)

any help apperciated

tia
steve
---------------------

Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists
If Right(path, 1) < "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("a1").Address(, , xlR1C1)

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function




Sub TestGetValue()

Dim packPath As String
Dim packFile As String


packPath = Sheets("sheet1").Range("d1").Value
packFile = Sheets("sheet1").Range("k1").Value

p = packPath
f = packFile
s = "Sheet1"
a = "b6"

MsgBox GetValue(p, f, s, a)

End Sub

Do this function for each row in worksheet
 

Sub TestGetValue()
Dim i As Long
Dim cRows As Long
Dim packPath As String
Dim packFile As String

With Worksheets("Sheet1")
cRows = .Cells(Rows.Count,"D").End(xlUp)Row

For i = 1 To cRows
packPath = .Cells(i,"D").Value
packFile = .Cells(i,"K").Value

s = "Sheet1"
a = "b6"

MsgBox GetValue(packPath, packFile, s, a)
Next i
End With

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Steve" wrote in message
...
hi all

the code below gets a path from d1, a filename from k1 and gets a value
from
that closed worksheet and displays result in a MsgBox

A need it to do two things.... I need it to loop through each row on my
worksheet, get the value from the closed workbook and paste the result in
column A.

Am not familiar with Loop functions - there are about 2000 rows of data
(ie
2000 different files listed that I need it to get a value from, the value
will always be on sheet1 and "b6" on the closed workbooks)

any help apperciated

tia
steve
---------------------

Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists
If Right(path, 1) < "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("a1").Address(, , xlR1C1)

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function




Sub TestGetValue()

Dim packPath As String
Dim packFile As String


packPath = Sheets("sheet1").Range("d1").Value
packFile = Sheets("sheet1").Range("k1").Value

p = packPath
f = packFile
s = "Sheet1"
a = "b6"

MsgBox GetValue(p, f, s, a)

End Sub