merlin_the_magician
Active Member
- Joined
- Jul 31, 2002
- Messages
- 480
I have been trying something like this before, i could not get it fixed. Let's give it another go:
I have a number of files in a specific folder. I need to extract values from cell Sheet1!G42 out of every file in this folder. Problem that occurs, is that I do not know how many files are in this folder, neighter do I know what the filenames are. I need some magic to perform extraction of the desired values.
As for the files in the specified folder I have this VBA code, that suits my needs just fine:
Private Sub Workbook_Open()
Dim fileList() As String
Dim fName As String
Dim fPath As String
Dim I As Integer
fPath = "H:\mydocs\"
fName = Dir(fPath & "*.xls")
While fName <> ""
I = I + 1
ReDim Preserve fileList(1 To I)
fileList(I) = fPath & fName
fName = Dir()
Wend
If I = 0 Then
MsgBox "No files found"
Exit Sub
End If
For I = 1 To UBound(fileList)
Range("AD" & I).Value = fileList(I)
Next
End Sub
This code will display all found files, including their path in column AD. (as plain text)
Question that remains: how am I to fix a formula In (column AE) that will extract the value found in Sheet1!G42 of each file? This must be very easy, but i can’t get this to work….
I have a number of files in a specific folder. I need to extract values from cell Sheet1!G42 out of every file in this folder. Problem that occurs, is that I do not know how many files are in this folder, neighter do I know what the filenames are. I need some magic to perform extraction of the desired values.
As for the files in the specified folder I have this VBA code, that suits my needs just fine:
Private Sub Workbook_Open()
Dim fileList() As String
Dim fName As String
Dim fPath As String
Dim I As Integer
fPath = "H:\mydocs\"
fName = Dir(fPath & "*.xls")
While fName <> ""
I = I + 1
ReDim Preserve fileList(1 To I)
fileList(I) = fPath & fName
fName = Dir()
Wend
If I = 0 Then
MsgBox "No files found"
Exit Sub
End If
For I = 1 To UBound(fileList)
Range("AD" & I).Value = fileList(I)
Next
End Sub
This code will display all found files, including their path in column AD. (as plain text)
Question that remains: how am I to fix a formula In (column AE) that will extract the value found in Sheet1!G42 of each file? This must be very easy, but i can’t get this to work….