Little help needed on extracting value from external file

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…. :oops:
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Anybody?

This should not be that hard, should it?
I have a file location in Cell AD1
There must be a simple formula to get the value in cell Sheet1!G42 out of the specified file?

HELP PLEASE?!?!?!?!?!
 
Upvote 0
Re: Little help needed on extracting value from external fil

Howdy merlin_the_magician,

Just one question where do you want to place the values you extract from each file? Do you want to place the values in the column immediately to the right of the filename?

-Ken
 
Upvote 0
Re: Little help needed on extracting value from external fil

Not a problem. The basic method I use to accomplish this albeit usually pulling much more info is as follows:

The initial setup:

Open up one of the files that you will be pulling the data from as well as the main workbook where the data will go into.

In the main workbook:
Use the INSERT-NAME-DEFINE option of the toolbar and select the range (in this case a single cell) where the data you are pulling exists. Name it whatever you like something like maybe "MyValue" whatever. Add it and close the box.

Now anywhere in your main workbook select the cell you want that piece of data to reside temporarily and enter in this example "=MyValue" without the quotes and enter it as an array formula by hitting CTRL-SHIFT-ENTER keys

If this is done correctly you should see the data you want to pull in that cell.

Now your main workbook is setup what you will then do is add to your code within the loop a routine that changes the definition of "MyValue" to each filename in the folder.

Private Sub Workbook_Open()
Dim fileList() As String
Dim fName As String
Dim fPath As String
Dim I As Integer
Dim Myxlname As String

fPath = "H:\mydocs\"
fName = Dir(fPath & "*.xls")
While fName <> ""
I = I + 1
ReDim Preserve fileList(1 To I)
fileList(I) = 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)
Myxlname = "='" + fPath + "[" + fileList(I) + "]" + "Sheet1'"
ActiveWorkbook.Names.Add Name:="MyValue", RefersTo:=Myxlname + "!$G$42"
Range("AE"& I).Value = (Wherever your temp cell is)
Next
End Sub


Something like that.
 
Upvote 0
This is great Egress!!!

One minor thing however: in column AD is a list of files that are in speciefied folder. I would like to get the value out of ALL of these files.
Problem is that i cannot name a range for every file, since files are added every now and then, and filenames vary. Therefore, i need to use the filenames displayed in AD.

I think the problem is in the line
Range("AE" & I).Value = '(Wherever your temp cell is)
i set value to 1, so the extracted value should appear in AE1, right?
 
Upvote 0
I managed to actually create the formula's i need by putting some data together. One minor problem: the formula is text. Is there any way of converting it to a working formula?
 
Upvote 0
Hi Merlin,

merlin_the_magician said:
This is great Egress!!!

I think the problem is in the line
Range("AE" & I).Value = '(Wherever your temp cell is)
i set value to 1, so the extracted value should appear in AE1, right?

I apologize for perhaps not explaining it well enough. It sounds like you may have missed a step in the setup because as it is it should place your pulled data to the column AE to the right of the filenames.

What I think you might be missing is selecting the cell in your main workbook to temp hold your pulled data.

Let us say just for an example on your main workbook cell ZZ1 is not being used for anything. You could use that cell to hold the temp data being pulled from each file. So in that cell you would use the formula "=MyValue" entered as an array with CTRL-SHIFT-ENTER.

Now when you've done this you would change the line in the modified code to

Range("AE" & I).Value = Range("ZZ1")

Thats it... now when you run the macro it will place the data from each file in the folder to the right of each filename. You don't need to add any formulas or change anything at all the code does all that for you.

I hope I explained it better this time.
 
Upvote 0
Re: Little help needed on extracting value from external fil

Could you use the Files Collection? Help has this example code:

Code:
Sub ShowFolderList(folderspec)
    Dim fs, f, f1, fc, s
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(folderspec)
    Set fc = f.Files
    For Each f1 in fc
        s = s & f1.name 
        s = s & vbCrLf
    Next
    MsgBox s
End Sub

HTH

Alan

(y)
 
Upvote 0
Re: Little help needed on extracting value from external fil

Egress,

i think i love you! :LOL: :LOL:

I have spend very much time figuring out how to get this working, and in the end, it seems yet so simple...!
This little piece of code will finally complete my workbook and is the crown on my work. Thank you ever so much!!! :pray:
 
Upvote 0
This is PERFECT for what I'm trying to do... only thing... is it possible to do the same with a directory on a web server (i.e. Sharepoint)?
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top