+ Reply to Thread
Results 1 to 3 of 3

VBA code to open many files, copy info, move file to new folder, paste info in second work

  1. #1
    Registered User
    Join Date
    11-27-2012
    Location
    Portland, Oregon
    MS-Off Ver
    Excel 2007
    Posts
    81

    VBA code to open many files, copy info, move file to new folder, paste info in second work

    Hi All,
    Hoping someone can help with this project, its way beyond my knowledge of excel.
    This is in two parts, first I’m trying to copy the same information from many files in a folder, and they will look like the attached “PCN 2014 Test” workbook (PCN Form Tab), but will have a name like PCN12345 and copy the info to my “PCN Data” workbook. The cells I’m trying to copy in the “PCN 2014 Test” workbook are the PCN Number cell P1, Names & Dates in cells H4 thru Q10 which are in drop down list and the date auto-populates, also PCN Type cell D13 which is also a drop down list. Note: Many of the cells in the “PCN 2014” workbook have Defined Names.

    Then in the “PCN Data” workbook (Issued Tab) a button (see attached) would be pushed to drop in the info into columns A, B, D & E (multiple PCN’s). Then hoping once the PCN’s have been processed from the “Issued” folder (this issued folder is where we store the completed PCN’s) the PCN’s could then be moved into a folder that is in the same location as the PCN’s are, like a “Processed PCN File” folder. When the PCN Data workbook is opened a week later, the user would click the button again and the list would keep growing as more PCN’s are completed. There are many PCN's and plans to do pivot tables and charts from the PCN data workbook.

    Part two if possible, is to pull the same info as above from the PCN forms (when button is pushed) but pull the PCN’s from a different folder; this folder sits next to the “Issued” folder above and its call “Change Notice”. It has all the PCN’s that are in process (but not complete) and would like to copy same info to the “PCN Data” workbook but on the (Change Notice Tab) instead of the (Issued Tab). This will let us see what department has the PCN and once I get the formulas working we can see how many days they have been sitting on them.

    We are trying to see how long a PCN takes to get thru the entire process and also monitor the PCN’s that are still in process and where they are at.

    Hope this is not too confusing and that someone can help.

    Thanks,
    Bikeman
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: VBA code to open many files, copy info, move file to new folder, paste info in second

    So I am familiar with everything you asked except "Moving" files in the VBA and everything is absolutely possible (probably even the moving or saving as a new file in another directory and deleting the original).

    Typically what I have done if created two buttons or steps. One to list everything in the folder, and another to batch process everything. So if I were you I would break your project down to a few critical steps.
    One, get a list of things that need to be processed. Workbooks, sheets certain data, certain ranges etc
    Two determine all the logic needed to "process" the files. Does the row count grow, does the column count grow?
    Three, implement some sort of loop to go through them all.

    When you break it down into manageable chunks people are more likely to help you with a specific problem you are dealing with and you aren't waiting/hoping someone will build the entire thing for you (as it will likely not happen)

    Just to give you an idea of what is possible, we have a process somewhat like your request that was fast-prototyped to capture data from about 100 workbooks each having between 40 and 100 tabs (and about 75MBs). We basically loop through each workbook, and EACH tab to see if we need to grab the data and if we do, we grab it process it then append it to our data set.

    We use ADO to go through about 4500 tabs (180Million! data points) in about 45 minutes!

    Feel free to look through the stuff I attached below


    Good luck
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Registered User
    Join Date
    11-27-2012
    Location
    Portland, Oregon
    MS-Off Ver
    Excel 2007
    Posts
    81

    Re: VBA code to open many files, copy info, move file to new folder, paste info in second

    Mike,

    Thanks! You are right I should break this down. I will start new thread in steps.

    I do already have a process that does exactly what I described in the start of this thread and it was built by one of the Excel Forum contributors and he did an awesome job. But unfortunately upper management wanted the layout changed, I had the data in rows and they wanted the data in columns so the can run pivot tables and charts according to them. I have been unable to communicate with the one who built it for me (I have tried many times but no luck) and I do not know enough about defined names to modify the workbook into columns to make it work again.

    The PCN data workbook I sent at the start of this thread is not the workbook that the Excel Forum contributor created for me, the one I sent was a new one setup with the columns instead of rows.
    If you would like to see the other PCN data workbook that the Excel Forum contributor created for me let me know. I would not need to re-do this workbook if I could figure out how to modify the one that is already working.

    Thanks for your response.
    Bikeman

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA code to open many files, copy text, close file, paste text in second work
    By Bikeman in forum Excel Programming / VBA / Macros
    Replies: 27
    Last Post: 02-11-2014, 12:59 PM
  2. Debug vbs code to copy and paste info from Sh1 to Sh2 triggered by selection
    By FlyFisherman in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-19-2013, 10:26 PM
  3. [SOLVED] Cmd to Listbox (Find info in sheet2) copy/paste info to A7 Excel 2007
    By Excelnoub in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-14-2013, 09:49 AM
  4. [SOLVED] VBA Code open files in folder, copy text to workbook-Next time folder opened copy only new
    By Bikeman in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-02-2013, 07:59 PM
  5. [SOLVED] Open specific folder, select file to open and copy then paste
    By Kranky in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-22-2012, 12:14 AM

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