Problem with openning closed xls files which have names in main xls in a column and then taking data from them with Vlookup

[jasmin10]

Dear Experts,
I have tried all night and day to solve this problem.
I have a main excel file for the results and approximately 500 excel names in acolumn in that main excel. In another folder a have nearly 300 excel files. What I want to do is:
1-Read the first cell (in the main excel) that is (cell: "B3").
2-Find the file named ("B3"=ADANA) in 'E:\BIST\DATA\FINANSALLAR\2009-12\("B3").
3-Open that excel file.
4- Search for a string named "NÜFUS".
5- Take the third column value written near "NÜFUS" word. (VLOOKUP?)
6- Copy the value to the main excel cell: "C3"
7-Save main excel.
8- Read the next cell (in the main excel) that is (cell: B4)
9-Find the file named ("B4"=ANKARA) in 'E:\BIST\DATA\FINANSALLAR\2009-12\("B4").
10- Open the file named B4.
11-Close the file named B3.
12- Search for the same string named "NÜFUS".
13-Take the third column value written near "NÜFUS" word.
14- Copy the value to the main excel cell: "C4"
.... This will continue for 500 excel cells in the main excel. End up in B503.

I appreciate for your helps. I found many links and tried most of them but I am lost.
Thank you...

 

[jasmin10]

Your first post say the folder is.
E:\BIST\DATA\FINANSALLAR\2009-12\

I have more than one folders. I will change and use macro for the all.

 

[jasmin10]

Your first post say the folder is.
E:\BIST\DATA\FINANSALLAR\2009-12\

Macro works for the first file. But can not open it. Says it can not find it by giving the name of the first file.

 

[jasmin10]

Your first post say the folder is.
E:\BIST\DATA\FINANSALLAR\2009-12\

How can we add after "Workbooks.Open (MyFile)" If there is not a same name workbook go to next c?

Thank you again.

 

[davesexcel]

You may need to have the folder address then for example Column E next to the first wb name. so when you loop through the column B it will also pickup the folder name in column E
MyFile = Dir(c.offset(,3) & "\" & c & ".xls")

 

[jasmin10]

You may need to have the folder address then for example Column E next to the first wb name. so when you loop through the column B it will also pickup the folder name in column E
MyFile = Dir(c.offset(,3) & "\" & c & ".xls")

Thank you. Actually I could not make the code work for one file. It gives the name of the file in the error button but it does not open it.

 

[mohadin]

Thank you for your help. It gives a define error for & b(i).

OOPs it my stupid mistake
Sorry for that
Please Try this version

Sub test()
    Dim i As Long
    Dim Dwb As Workbook
    Dim fnd As Range
    For i = 3 To 503
        Set Dwb = Workbooks.Open("E:\BIST\DATA\FINANSALLAR\2009-12\" & Range("b" & i) & ".xlsx")
        Set Dwb = ActiveWorkbook
        Set fnd = Dwb.Sheets(1).Range("A:A").Find(what:="xxx", LookIn:=xlValues, lookat:=xlWhole)
        fnd.Offset(, 3).Copy ThisWorkbook.Sheets("sheet2").Range("C" & i)
        Dwb.Close False
    Next
End Sub

Assuming that your files extension are .XLSX and starting the file name in cell (B3)
Let me know
Thank you

 

[davesexcel]

Thank you. Actually I could not make the code work for one file. It gives the name of the file in the error button but it does not open it.

It could be the file name does not match the range name

 

[jasmin10]

It could be the file name does not match the range name

ı have solved it. Thanks...

 

[mohadin]

Deleted