About Us

K[_2_]

On Mar 18, 10:16*am, Joel wrote:
I'm not sure if you want to search both main folder and subfolder. *I only
did subfolders. *Also I'm just searching for the basic filename (doesn't
include folders) when looking for file on the worksheet.

Sub getfiles()

FolderName = "C:\Document\Data"
FolderName = "C:\Temp"

Set fs = CreateObject("Scripting.FileSystemObject")
Set Folder = fs.GetFolder(FolderName)

LastRow = Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1

For Each Subfld In Folder.subfolders

* *FName = Dir(Subfld & "\" & "*.xls")

* *Do While FName < ""
* * * Set c = Columns("A").Find(what:=FName, _
* * * * *LookIn:=xlValues, lookat:=xlWhole)
* * * If c Is Nothing Then
* * * * *Set bk = Workbooks.Open(Filename:=Subfld & "\" & FName)
* * * * *With bk.Sheets(1)
* * * * * * Data = .Range("B2")
* * * * * * .Range("A" & NewRow) = FName
* * * * * * .Range("B" & NewRow) = Data
* * * * * * NewRow = NewRow + 1
* * * * *End With
* * * * *bk.Close savechanges:=False
* * * End If

* * * FName = Dir()
* *Loop
Next Subfld

End Sub

"K" wrote:
On Mar 17, 10:52 pm, Joel wrote:
I didn't see this posting before. *It is very simple

Sub getfiles()

Folder = "C:\Document\Data\"

FName = Dir(Folder & "*.xls")

LastRow = Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1

Do While FName < ""
* *Set c = Columns("A").Find(what:=FName, _
* * * LookIn:=xlValues, lookat:=xlWhole)
* *If c Is Nothing Then
* * * Set bk = Workbooks.Open(Filename:=Folder & FName)
* * * With bk.Sheets(1)
* * * * *Data = .Range("B2")
* * * * *.Range("A" & NewRow) = FName
* * * * *.Range("B" & NewRow) = Data
* * * * *NewRow = NewRow + 1
* * * End With
* * * bk.Close savechanges:=False
* *End If

* *FName = Dir()
Loop

End Sub

"K" wrote:
Hi all, I am using excel 2007. *I have list of files names in column A
of Sheet1 like (see below)

* * * A ------column
Record A
Record B
Record C
etc….

All the names listed in column A are the names of the files which are
in Folder "C:\Document\Data". *I want macro assigned to a button in
Sheet1 which should Match or Lookup files names listed in column A of
Sheet1 with names of files which are in above Folder. *And if there
are new files been saved in Folder which names are not listed in
column A of Sheet1 then macro should open them one by one and copy
cell B2 value from those files and paste it in column B of Sheet1 and
Put that file name without extension below the last value cell of
column A and then close those files. *Please can any friend can help
as i need simple and small macro if possible and i been asking this
question from two weeks but didnt have any accurate answer.- Hide quoted text -

- Show quoted text -

thanks- Hide quoted text -

- Show quoted text -

Thats brilliant Joel. Thanks lot

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Lookup Formula: Return 1st match, then 2nd match, then 3rd match	Scott	Excel Discussion (Misc queries)	4	December 11th 09 05:50 AM
lookup with match	[email protected]	Excel Worksheet Functions	4	December 9th 08 05:11 PM
Lookup? Match? pulling rows from one spreadsheet to match a text f	cjax	Excel Worksheet Functions	3	July 21st 06 02:51 PM
Lookup or Match ?	carl	Excel Worksheet Functions	4	July 12th 05 07:22 PM
Lookup / Match help ???	RayG	Excel Discussion (Misc queries)	3	January 14th 05 02:13 AM

Menu

About Us