+ Reply to Thread
Results 1 to 20 of 20

Reading Excel files in folder/subfolders

  1. #1
    Registered User
    Join Date
    12-24-2012
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    33

    Reading Excel files in folder/subfolders

    Hi!

    I need some help reading out information from multiple excel files in folder/subfolders.
    The structure of the folders are /year/month/file
    So if it's a file from this month it would be /2014/Februar/test.xlsx
    I need to read all the files within this month and next month.

    I've attached a sample file which the information needs to be put in. under "Data" there's a table, i need to input the information from the files that are read.

    From the source file in question
    • Column A = C5
    • Column B = C7 with a link to the file which it's from
    • Column C = C110
    • Column D = F16
    • Column E = F10
    • Column F = F7
    • Column G = F8


    Hope this is clear and hope I can get some input on this, I'm pretty lost unfortunatly :/
    This has to be run when opening the "kalender.xlsx"
    Attached Files Attached Files
    Last edited by crakter; 02-27-2014 at 12:30 PM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Reading Excel files in folder/subfolders

    Do the labels (merged cells) on the Kalendar Sheet represent command button to pull the data for the particular month?

    In column B, you indicate that this should be a link to the file. Where is this coming from? In your example, it does not look like the file name as shown in the posting. Please clarify. Because the year will change and your folder will change, how do you wish to prompt for the year? Input Box popup, or will you populate a cell on the Kalendar sheet?
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    12-24-2012
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: Reading Excel files in folder/subfolders

    Hi Alan,

    The Kalendar sheet is not done, don't worry about that sheet. It's not ment to pull the data for that month, it's going to be a calendar which is sorting on date from the "data" sheet which is pulled from the files in the /year/month/ folder.

    It should link to the file it is prossessing at the point. As I said, the other columns need to be filled with the data from the files it reads in the folder.
    It should read the year and month as the current year and current month + next month.
    So that today it reads all files in /2014/February/ and 2014/March/ and populate the data sheet.

    Hope this clears it up.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Reading Excel files in folder/subfolders

    Understand. One more question. Will you ever run the report for the latest month (February) in March. Using Current Month means that you will have to run report for February in February. Please clarify as you use the term current month and I want to make sure of this.

  5. #5
    Registered User
    Join Date
    12-24-2012
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: Reading Excel files in folder/subfolders

    Correct, the report will not ever have to run last month (February) in March. If it's March 1 it should read out march month and april.

    Thanks in advance.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Reading Excel files in folder/subfolders

    Starting writing code and realized I still have another question. This statement

    I need to read all the files within this month and next month.
    Will there be more than one file per month?

  7. #7
    Registered User
    Join Date
    12-24-2012
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: Reading Excel files in folder/subfolders

    Quote Originally Posted by alansidman View Post
    Starting writing code and realized I still have another question. This statement



    Will there be more than one file per month?
    Yeah there are multiple files in the /2014/February/ folder. it needs to read all the files and pick up the information i need as stated in OP.

  8. #8
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    861

    Re: Reading Excel files in folder/subfolders

    here is a starting point:

    Please Login or Register  to view this content.
    .. and don't forget to have fun!
    Bogdan.

    mark SOLVED and Add Reputation if my answer pleases you

  9. #9
    Registered User
    Join Date
    12-24-2012
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: Reading Excel files in folder/subfolders

    Quote Originally Posted by bulina2k View Post
    here is a starting point:

    Please Login or Register  to view this content.
    Thank you for the start. I have got it working of some sorts. I'm unsure if there is any other way of doing it to speed it up but.
    I'm struggeling to add the link to the B column.
    Here is the code I'm using now.

    Please Login or Register  to view this content.
    Any advice would be greatly recieved.

  10. #10
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    861

    Re: Reading Excel files in folder/subfolders

    try this for hyperlink

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    12-24-2012
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: Reading Excel files in folder/subfolders

    Quote Originally Posted by bulina2k View Post
    try this for hyperlink

    Please Login or Register  to view this content.
    Unfortunatly i get a message saying "Run-time error '5': Invalid procedure call or argument"

  12. #12
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    861

    Re: Reading Excel files in folder/subfolders

    Paste here the entire code you're using now pls.

  13. #13
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Reading Excel files in folder/subfolders

    @crakter,
    My apologies for not responding earlier. I had been locked out of the forum for the last 24 hours due to an electronic issue. Any way, here is my code for your issue.
    Please Login or Register  to view this content.


    How to install your new code
    1. Copy the Excel VBA code
    2. Select the workbook in which you want to store the Excel VBA code
    3. Press Alt+F11 to open the Visual Basic Editor
    4. Choose Insert > Module
    5. Edit > Paste the macro into the module that appeared
    6. Close the VBEditor
    7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

    To run the Excel VBA code:
    1. Press Alt-F8 to open the macro list
    2. Select a macro in the list
    3. Click the Run button

  14. #14
    Registered User
    Join Date
    12-24-2012
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: Reading Excel files in folder/subfolders

    Thanks to both of you. This is great. I have a problem linking to the file though. Could you please have a check at my revised code?

    I get a "Run-time error '5': Invalid procedure call or argument" error :/

    Please Login or Register  to view this content.
    Last edited by crakter; 02-17-2014 at 03:35 PM.

  15. #15
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Reading Excel files in folder/subfolders

    When you click on debug, which line is highlighted? You used ActiveWorkbook.Path and then the variables. Are the Source files (2014/ParticularMonth) in a sub folder of the destination file? If you were to write out the full path for the source files, how does it compare to the path for the destination file? I tested using a specific file path.

    BTW: it is not necessary when responding to quote the entire previous post. If there are particular lines that are relevant, then quote those. Otherwise it is only taking ups space.

    Alan

  16. #16
    Registered User
    Join Date
    12-24-2012
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: Reading Excel files in folder/subfolders

    The line that is highlighted is:
    Please Login or Register  to view this content.
    The Source files is in the sub folders of the destination file yes. The path is the same, I have tested it.

  17. #17
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    861

    Re: Reading Excel files in folder/subfolders

    like that:

    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    12-24-2012
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: Reading Excel files in folder/subfolders

    Hi,

    I have tried this aswell and same error unfortunatly.

  19. #19
    Registered User
    Join Date
    12-24-2012
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: Reading Excel files in folder/subfolders

    Can someone please help on the issue?

    Thanks in advance

  20. #20
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Reading Excel files in folder/subfolders

    You may try:
    Please Login or Register  to view this content.
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

+ 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] list of subfolders in folder - without files and sub-subfolders
    By MartyZ in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-11-2022, 10:56 AM
  2. Moving Files from Folder and Subfolders
    By mvinay in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-30-2014, 01:59 AM
  3. Counting files in folder including subfolders also and folder size
    By mido609 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-12-2012, 03:26 PM
  4. [SOLVED] Opening Excel files in same folder through a Macro - but not subfolders?
    By toffee_madman in forum Excel General
    Replies: 9
    Last Post: 10-19-2011, 11:09 AM
  5. Excel Automation For reading data from .csv files in a folder
    By nalayak21 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-25-2011, 02:00 PM

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