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

jasmin10

New Member
Joined
Jan 2, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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...
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

jasmin10

New Member
Joined
Jan 2, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 2, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 26, 2006
Messages
1,089
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

New Member
Joined
Jan 2, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
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
VBA Code:
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

Well-known Member
Joined
Feb 26, 2006
Messages
1,089

ADVERTISEMENT

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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,683
Members
415,920
Latest member
ExcelNoob28

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
Top