+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    10-11-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    2

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

    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?
    Last edited by Martha44; 03-04-2013 at 03:10 AM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

    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.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.
    Last edited by JBeaucaire; 12-27-2019 at 10:16 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    10-11-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    2

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

    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.

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

    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.

  5. #5
    Valued Forum Contributor
    Join Date
    11-15-2008
    Location
    ph
    MS-Off Ver
    2007/2010/2016
    Posts
    479

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

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1