getting datas of each workbooks in directory

[damrus]

Hi!
I need to get some datas from each workbooks in directory.
How to do it?
For example: I need to get value from cell "A1" of each workbooks in directory.

Regards

 

[ClulessChris]

damrus, what code do you have so far?


Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[damrus]

Actually I have any code. I have no idea how to do it.
How to connect with another workbook without opening it and how to search chosen directory?

 

[ClulessChris]

Searching files and folders take a look at the File System Object

then maybe DAO/ADO to connect / open the workbooks.

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[SkipVought]

Is this a ONE TIME thing?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[damrus]

No, I do sth monthly. Each month one workbook is added into directory.
Simply I need to do loop until last file.xls in chosen directory. - this is one thing.
Secondly I need to get sth from each workbooks, so I need to connect with each workbook in one step of loop and for example get cell(1,1).

1) How to define last file in directory(ie C:\Directory\) ?
2) How to connect with each excell file and get ie cells(1,1)

 

[damrus]

Why does it work in excell 2000 and doesn't work in excell 2007?
Error 445 is generated
- object doesn't support this action.

Sub searchf()

Set fs = Application.FileSearch
With fs
.LookIn = "E:\Dane\Tym\"
.Filename = "*.*"
If .Execute > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

 

[SkipVought]

error on exactly what statment?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[damrus]

Set fs = Application.FileSearch

 

[SkipVought]

What happens is your do this...

Sub searchf()

With Application.FileSearch
    .LookIn = "E:\Dane\Tym\"
'.......

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[damrus]

The same error on

With Application.FileSearch

 

[Golom]

It appears that Filesearch has been dropped from Access 2007. Check out this link for the work-around using Dir or the FileSystemObject.

 

[SkipVought]

FileSearch appears NOT to be available in Excel 2007.

Did you try the FileSystemObject? as previously suggested?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[damrus]

Thanks

 

[damrus]

But I have still some problems.
How to get in this code name of each file?

Sub SearchF()
Dim fso As New FileSystemObject
Dim fld As Folder, f As File
Dim path As String, i As Long
path = "E:\Dane\Tym\"
Set fld = fso.GetFolder(path)
For i = 1 To fld.Files.Count
Debug.Print fld.Files.Count
' how to get file name?
Next
End Sub

 

[Golom]

For i = 1 To fld.Files.Count
    Debug.Print fld.Files.Count
    Debug.Print fld.Files(i).Name
Next

or

For Each f in fld.Files
   Debug.Print f.Name
Next

 

[SkipVought]

Sub SearchF()
    Dim fso As Object
    Dim fld As Object, fil As Object
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim path As String, i As Long
    path = "C:\"
    Set fld = fso.GetFolder(path)
    
    For Each fil In fld.Files
        Debug.Print fil.Name
    Next
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[damrus]

Golom,
second code works, but I'm wondering why in first code error occurs:
Invalid procedure call or argument
on Debug.Print fld.Files(i).Name

It can be useful if you want do sth with ie file(3)