+ Reply to Thread
Results 1 to 16 of 16

VB - returning values from multiple, moving external Excel files

  1. #1
    Registered User
    Join Date
    02-28-2017
    Location
    Woodstock, GA
    MS-Off Ver
    2013
    Posts
    10

    VB - returning values from multiple, moving external Excel files

    New member here and only know enough VB to get errors, lol, so please bear with me. I am working with Excel 2013. Here’s my set up:

    • My project involves over 200 book files to be reviewed, each has its own folder and follows a specific workflow.
    • The book folders are uniquely-named, moved as the status changes, and may reside in one of six subfolders at any given time.
    • An Excel file (called a traveler) resides in each book’s folder that contains the current status and workflow history of that book.

    I have a master Excel file (MasterFile) that resides in the main folder and provides a status of ALL of the travelers (these travelers must remain closed). Currently, I have WS code accomplishing this. It works, but there are a ton of repeated external paths that I know would be much cleaner in VB. I figured the first task is to define all the path variations as strings and concatenate them, but I just can’t figure out how. After that, I will need to figure out what function in VB is comparable to VLOOKUP, so I can pull dates from the travelers based on the status. I’ve Googled it to death over several weeks. Every time I find where someone is answering a similar question, I get stuck trying to decipher which part of the suggested code is an actual VB function, and which is the variable.

    Folder structure:

    MAIN FOLDER
    -MasterFile
    -SUB1
    -SUB2
    -SUB3
    -SUB4
    -SUB5
    -SUB6

    Please include the entire SUB routine... yes, i'm that much of a newbie.

    Thanks in advance for any help!

  2. #2
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862

    Re: VB - returning values from multiple, moving external Excel files

    If you load the master file and the traveler files (or just 2 of them if they are all structured the same) we will be able to understand what you need and offer a better solution.
    MatrixMan.
    --------------------------------------
    If this - or any - reply helps you, remember to say thanks by clicking on *Add Reputation.
    If your issue is now resolved, remember to mark as solved - click Thread Tools at top right of thread.

  3. #3
    Registered User
    Join Date
    02-28-2017
    Location
    Woodstock, GA
    MS-Off Ver
    2013
    Posts
    10

    Re: VB - returning values from multiple, moving external Excel files

    Ok, i made generic replicas of my files and included a screenshot of a folder structure like what i'll be using (attached). Yes, all of the travelers are laid out exactly the same. Thanks for the help!
    Attached Images Attached Images
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862

    Re: VB - returning values from multiple, moving external Excel files

    OK - I understand what you're after and yes this can be done. I can write some code in the master file to search through your directory structure and find each book's traveller file. Normally you would open the file, get the data you're after (the dates of each completed status step), write to the master and close the traveller file. To avoid opening and closing 200+ files, I'll create a temp sheet and replicate the traveller sheet by formula reference and use that to get the data. Last thing I need is the full path of the master file and of the directory labelled "MAIN" in the picture above.

  5. #5
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862

    Re: VB - returning values from multiple, moving external Excel files

    Hi Cmiannay. See attached file. The only issue you might have with this is where you have a book with the word "traveler" in it (e.g. The Time Traveler). The code will pull that out and think that it's a traveler file. If this is a possibility, you can trap this and exclude .. let me know if you can't do this and need help.

    Meanwhile - this code is in a module - please note: you need to change the root directory constant!! If you do not it will look in every sub-directory in your C drive (which will take ages). If this happens ESC is your friend.
    Please Login or Register  to view this content.
    And this is just a button click call to the module code and is in the Master sheet object.
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    02-28-2017
    Location
    Woodstock, GA
    MS-Off Ver
    2013
    Posts
    10

    Re: VB - returning values from multiple, moving external Excel files

    Wow! Thank you so, so much! I won't have time to test this for a few days, but i'll let you know how it goes. You spent a lot of time with this... i really appreciate it! :D

    BTW, i will never have the issue with a book title containing "traveler".
    Last edited by Cmiannay; 03-04-2017 at 11:20 AM.

  7. #7
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862

    Re: VB - returning values from multiple, moving external Excel files

    You're very welcome - let me know if you run into any issues. If you do have a book title with traveler you'd just need to validate the file format before processing it.

  8. #8
    Registered User
    Join Date
    02-28-2017
    Location
    Woodstock, GA
    MS-Off Ver
    2013
    Posts
    10

    Re: VB - returning values from multiple, moving external Excel files

    MatrixMan, I finally had a chance to get back to this project and the code works great! Now I need your help tweaking it to work with my actual terminology. I was hoping to find/replace "STATUS 1", "STATUS 2", etc., but it looks like you've made it to search the column header "Action" and used variables as integers to return the dates. The problem is the book sometimes requires a second time in STATUS 4. For example:
    STATUS 1
    STATUS 2
    STATUS 3
    STATUS 4
    STATUS 5
    STATUS 4
    STATUS 5
    STATUS 6
    Because of this, there will be another column in the Master for the second date of STATUS 4. Is there any way to "dumb it down" for me and have it search the actual text?

    Also, I need it to search in column B ("Action" (for linking purposes)"), instead of A.

    I am envious of your talent with vb. Maybe someday I can actually understand everything you wrote, lol.

  9. #9
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862

    Re: VB - returning values from multiple, moving external Excel files

    Hi - the bit of code you're referring to that uses Action is just a loop that writes the contents of each traveler file to the temp sheet; because there are blank rows above the header, it can't just stop when it finds a blank row - it stops when it finds a blank row AFTER the header row, which I recognise by the title Action. This just writes the status and date to the temp sheet ...

    The next bit of code under the "write summary data to master sheet" comment sets a range variable "rng" as the cells containing "STATUS 1, STATUS 2" etc and the dates are next to that range. Then there is a loop through all cells in that range (cels in rng), which writes the dates under each status. This is where the issue you describe needs to be addressed. If you have more than one date for a status then the code needs to search for each status and write to the appropriate column (as the sample traveler files only had one date per status, they were sequential and one to one so this wasn't necessary). However - it can be done.

    The question is one of layout. If a book goes from status 4 to 5 then back to 4 then back to 5 (perhaps a few times - perhaps from 5 to 4 to 3 to 4 to 5 and so on) then you can either add columns as you say or add rows. I suggest rows over columns because it will represent the back and forth better ... you could end up with three or four columns per status otherwise. Instead if a book goes from status 1 to 2 to 3 and then back to 2, I would put that status 2 date on a new row for the same book, expecting it to then go on to 3 to 4 to 5 etc. This shows exactly where the "backsteps" occurred in the journey from status 1 to 6.

    What do you think?

  10. #10
    Registered User
    Join Date
    02-28-2017
    Location
    Woodstock, GA
    MS-Off Ver
    2013
    Posts
    10

    Re: VB - returning values from multiple, moving external Excel files

    Lets forget the complication for now. I am hung up on trying to use your code with the actual names of my statuses and books. I can't find where you've referenced "STATUS" or "Book", so I can't even make it work at a basic level. I'm sorry I can't share my actual files.

  11. #11
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862

    Re: VB - returning values from multiple, moving external Excel files

    OK - I'll try to explain ...

  12. #12
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862

    Re: VB - returning values from multiple, moving external Excel files

    OK - I'll try to explain ...

    The first chunk of code just reads into the Temp sheet the contents of the found Traveler file. It's the bit that has the comment "create temp sheet with file data - only need cols A-C" above it. Traveler files are structured with the book name in cell(1,1), a series of blank cells, then a header where the first word is "Action". That is followed by something in col A, the status in col B, and the status date in col C. This code reads that data in until there is a row somewhere after the header row is found that all three cells are blank, which is where it stops - the loop condition dictates this:
    Please Login or Register  to view this content.
    The next bit is where the data from the Temp sheet is written to the Master log - it has the comment "write summary data to master sheet" above it. I'll go through this line by line for you.

    lngWriteRow gets the next row in Master to write to.
    Then we write the book name to Master from the first cell in Temp.
    The next line is:
    Please Login or Register  to view this content.
    The status is in col B of the Temp sheet (hence the "B"); lngTravelDataRow was incremented during the first Do/Loop where we were searching for the header row with "Action", so this is the first row under the header row. And i was the row counter also incremented during the previous Do/Loop process. I could have done another .End(xlUp) on col B but we already have the row count so I just used it here.

    The next bit - the For/Next loop - is where the dates are written to the appropriate status col in Mastger. The reason you can't find the word "Status" anywhere is because it is assumed Status is in col B; the only condition searched for is whether or not the last character is a number ... Status 1, Status 2 etc. It really could say Apple 1, Apple 2 etc and it would still run as it does.

    If the last character is a number (confirmed by checking if converting that character to a number would throw an error - IsError(CInt(Right(cel.Text, 1)), then that number is assigned to k and - if there is a valid date next to that cel in col B it is written to the Master sheet.

    Because the book name is in col A of the Master sheet, then Status 1 col, Status 2 col, etc, Status 2 is 2 columns offset from col A, Status 5 col is 5 columns offset from col A etc, so I can use k as both the status number and the column offset.

    Does that help?

  13. #13
    Registered User
    Join Date
    02-28-2017
    Location
    Woodstock, GA
    MS-Off Ver
    2013
    Posts
    10

    Re: VB - returning values from multiple, moving external Excel files

    Hello again...
    MatrixMan, i hope you are still active on this forum! I was never able to get the code you wrote to work for me and then i got pulled off on another project. Anyway, i would like to upload my actual folders/files if you wouldn't mind tweaking the code accordingly. I'll wait to hear back from you first.

  14. #14
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862

    Re: VB - returning values from multiple, moving external Excel files

    Hi - I'm not too active on here these days but I did get the notification that you were looking for me! If you load your files and let me know what the problem is, I'll take a look.

    MM.

  15. #15
    Registered User
    Join Date
    02-28-2017
    Location
    Woodstock, GA
    MS-Off Ver
    2013
    Posts
    10

    Re: VB - returning values from multiple, moving external Excel files

    So glad you responded! I am getting the files together and will send soon.

  16. #16
    Registered User
    Join Date
    02-28-2017
    Location
    Woodstock, GA
    MS-Off Ver
    2013
    Posts
    10

    Re: VB - returning values from multiple, moving external Excel files

    MatrixMan,

    I've uploaded the files. You will see I have a few travelers in different states in the workflow. In the Master tracker, I've left the WS code (which sort of works) in the first row of FM & MM to give you an idea of what i need to accomplish using VB.

    A few things:

    - Leave MAY 2018 FM, columns A, B, C, & D, and rows 9-12 as is.
    - Leave MAY 2018 MM, columns A, B, C, & D, and rows 264-268 as is.
    - Leave MAY 2018 STATUS tab as is.
    - Do not delete hidden tabs.
    - Do not delete conditional formatting.

    Book folder names may be appended at any given time as follows:

    02_TAGGING and 03_QC folders:
    _LOGGED
    _IN WORK
    _HOLD

    04_SV TAC folder:
    _SENT

    Thank you so, so much for your help!
    Attached Files Attached Files
    Last edited by Cmiannay; 06-30-2018 at 11:04 AM.

+ 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] Extract data from multiple external excel files
    By rxk in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-03-2016, 11:06 PM
  2. Searching for files by cell value and returning values in Excel 2010
    By trilla99 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-25-2014, 09:17 AM
  3. Using external text file to assign VBA variables for multiple excel files
    By evancharles in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-29-2013, 05:30 AM
  4. Extract Values from external Files
    By Sultix in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-12-2012, 02:39 PM
  5. Preserving external links while moving excel files
    By zzz975 in forum Excel General
    Replies: 1
    Last Post: 10-25-2011, 03:50 PM
  6. Replies: 2
    Last Post: 02-10-2008, 10:51 AM
  7. Need advice : consolidating data from multiple CSV files in Excel - External data handling
    By Matthieu Gaillet in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-01-2005, 05:10 AM

Tags for this Thread

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