Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
worksheet function... | Excel Worksheet Functions | |||
Help with worksheet function | Excel Worksheet Functions | |||
Reference the worksheet from a multiple worksheet range function ( | Excel Worksheet Functions | |||
Can the offset worksheet function reference another worksheet | Excel Worksheet Functions | |||
worksheet function in VBA | Excel Programming |