Inserting Vlookup function in all workbooks

[luckyros]

I have around 700 workbooks in a folder on my PC.

My master file(master database.xlsx) is kept open from which i have to Vlookup the values into these 700 workbooks.

The "agent Id" column is common in both the master database.xlsx as well as those other workbooks also.

The thing i want to do is i need the data from the "reporting supervisor" which is in the master database.xlsx file to be available in the G column of the other 700 books ( i have attached only 4 sample books). The G column can be then named as "Reporting Supervisor". Since "Agent ID" is available in both files we can vlookup taking this as a base. I have attached the 4 sample files out of 700 as well as the master database file in a zip file.

Hope my question is clear to you. Please give a nice solution to this or else i have to open all the 700 files and do vlookup one by one.

[luckyros]

Hello, noone can solve this Issue ??? is this so tough ? Plz help if someone can do this easily.

[JBeaucaire]

See, once you post up some useful data, it goes quickly. Put this macro into your Master Database program, run it from there. You will need to edit the part I marked in red to the folder you will use each time.

This macro will run on ALL the files in the folder starting with the "Agent Call Report...." in the name.

Please Login or Register  to view this content.

The macro also sorts the Master Database info and gives it a named range.

[luckyros]

I have pasted this in the module in master database.xlsx

After running its giving error "Run-time error '445': Object doesn't support this action. when i press debug its comes on here With Application.FileSearch which is marked yellow.

[JBeaucaire]

Did you try just running it as is in the workbook attached? I don't believe any of the code I used is non-Excel-2007 friendly, but maybe.

Edit the workbook I attached to the proper path for your files, or somewhere you have a couple to test on....and run the macro from inside the book I gave you. See if you get better results.

The macro had no trouble opening your Excel 2007 A"agent" files and updating them, it ran right through the whole bunch of them.

[JBeaucaire]

Wait, you can't run a macro in a file called Master Database.xlsx, can you? It has to be named Master Database.xlsm in Excel 2007.

[luckyros]

I have saved the file in the format .xlsm but i am getting same error. Can you attached the master database file with the code pasted in it ?

[luckyros]

are you able to do it correctly on your side ? all my other macros run properly even if i dont save them as .xlsm file. Please help me with the solution for this.

[JBeaucaire]

Post #3 has had that attachment all along.

[luckyros]

Oh no same error comes again and after debugging it comes near "With Application.FileSearch" highlighting it in Yellow. Why is this happening ya ?

[JBeaucaire]

Good question.

[luckyros]

so i thing my query will remain unsolved and i will have to do manually ? dont tell me.

[JBeaucaire]

What did you change the RED sections in that code to?

[luckyros]

With Application.FileSearch
.NewSearch
.LookIn = "E:\New Folder"
.SearchSubFolders = False
.Filename = "Agent Call Report*.xlsx"
.Execute

I change the folder name to the above mentioned one as my files are in that folder. Rest everything is same. But the macro stops before coming to this line.

[luckyros]

This is what i found on the net..........


Wednesday, 9 May 2007

No Application.Filesearch in 2007

In earlier versions Aplication.FileSearch could be used to loop through all the files contained in a Directory. This does not appear to work in 2007 versions. I have managed to use Dir to achieve this instead.

--------------------------------------------------------------------------------------' Module : Module1
' DateTime : 09/05/2007 08:43
' Author : Roy Cox (royUK)' Website : www.excel-it.com for more examples and Excel Consulting
' Purpose : Open all worksheets in a specific folder' Disclaimer; This code is offered as is with no guarantees. You may use it in your' projects but please leave this header intact.
'---------------------------------------------------------------------------------------

Option Explicit
Sub Open_All_Files()
Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String

sPath = "C:\Documents and Settings\Roy Cox\My Documents\" 'location of files
ChDir sPath
sFil = Dir("*.xlsx") 'change or add formats
Do While sFil <> "" 'will start LOOP until all files in folder sPath have been looped through Set oWbk = Workbooks.Open(sPath & "\" & sFil) 'opens the file

' do something
oWbk.Close True 'close the workbook, saving changes
sFil = Dir
Loop ' End of LOOP
End Sub

Can you change the code according to the example i have given up ? to suit my needs ?

[JBeaucaire]

Please edit posts #14 and #15 and put CODE tags around your VBA code as per the forum rules. See the "How To.... ....Add Code Tags" at the top menu for instructions if you've never done that.

I'll take a look when I get back to my office, but I do not use Excel 2007. You could post a new thread with my macro (in between code tags, please) and an appropriate title : "Convert 2003 macro to work with 2007" and I'm sure someone would make quick work of that for you.

[douglasba]

Hi Guy,

This code is working nicely,

regards