Get file path from a cell, run a check to see if the file has a specific sheet if yes copy

[Martha44]

Hello

I am trying to build a master purchase order spreadsheet, which will run a macro from a specific cell and check if the file has a sheet called extract if it has then it will copy a name range in the sheet which is also called extract and paste to the master spread sheet moving one column to the left each time.

So far I found a spreadsheet on another website that runs a macro to get the filepaths into individual cells in a spreadsheet, I have found code on here that opens the workbooks, but now I'm thinking as there are over 500 files each in a seperate folder that it will be quicker to do this with the workbooks closed. The code I have so far is

Please Login or Register  to view this content.

Book2.xlsm

I need this code to loop throught the cells also.

I am fairly new to vba, is what im lookng for achievable?

[JBeaucaire]

Perhaps there are some concepts here you can adapt to your project. There are many example base macros on my site that deal with common ideas like this. For instance:
===========
'WORKBOOKS TO 1 SHEET STACKED
Here's a macro for collecting data from all files in a specific folder.

Workbooks to 1 Sheet

The parts of the code that need to be edited are colored to draw your attention.
===========

This macro will teach you how to cycle through all the workbooks in a single folder. That gets you most of the way there. Once the workbook is open, you need only test if the "sheet" exists and if so execute your copy commands, then the macro closes that wb and repeats with the next.

Give it a shot.

[Martha44]

Thanks for the link I have seen similiar code on here to copy from a folder my problem is that there is only one file in each folder that I require, and I have over 500 folders, so am looking for code to get into to folder and file then move to next folder.

I have copied your code and will give it a go in the office.

[JBeaucaire]

If you complete your macro for processing all the files in a single folder, post it here and I'll help you turn into a "spelunker", a macro that will dive down through all the folders starting from a main folder and process all files in all folders.

The technique I use can be found here.

[event21]

Hi -

you can try this function to check whether the sheet named "extract" is in the closed workbook.

Please Login or Register  to view this content.

use it like;

Please Login or Register  to view this content.

event