VBA to scrape data from workbook

    Hi Guy's,

    I am new to this forum.

    I need to get Specific data from multiple xlsm files, saved in Single folder with subfolders. (Total more than 200 files)

    Folder path is "E:\DATA\OPD DATA\Ped Neo"

    Sheet name in each file is "PreTerm". ( It has 5 sheets, data required only from sheet "PreTerm")

    Specific data required from multiple cells i.e. "A2", "A66", "A65:K65"


    Data from single file to be copied in seperate row. The first column of the row should show the file name from where the data has been taken.

    I am using Office 2013


    Please

    The attached file should meet the requirements. It is a stand-alone file containing macros that will open the files in the nominated folder, extract the cell values from the PreTerm sheet and write these values to a new workbook which will be saved in the nominated output folder.


    The main macro is called ReadPreTermFiles and can be launched by the Extract Data button.


    The output filename is coded in the macro, currently this is in the format of "PreTerm Output dd-mmm-yy hh.mm.xlsx" but obviously this can be changed.


    Please note there is only minimal checking in the macro (file/folder exists, etc).


    PreTermExtractor.xlsm

    Thanks a Lot gijsmo for immediate help.

    It is working absolutely fine. Exactly what I required.


    But one major problem. As I mentioned earlier that total files more than 200. It is working fine for upto 10 or 20 files. but when I give it folder with more than 200 files the programme shuts down automatically. Means excel closes down.

    Sorry but I cannot see any real reason why the number of files would make any difference other than time to process.

    Each file is opened, data is extracted and then the file is closed so there is no reason I can see why this should not work on a large number of files.

    Can you help me with one more file work?


    I have auto macro enabled file , which run the macro as soon the file is opened.

    I need to run these multiple files so that First file is opened, macro runs, saves and close, then the next file is opened and so on.

    All these files are in one folder.


    Thanks in advance

    Hi Gijsmo

    The file is working excellently.

    One request if possible.

    Can the Output file have Fixed Headers in the first row.

    Quote from gijsmo

    Absolutely, the attached update has a 2nd sheet called PreTerm which holds the fixed headers. Obviously you can rename and reformat the headers as required.


    The PreTerm sheet is copied as a starting point for the output file. The data from the individual files is then appended below these headers.


    PreTermExtractor v2xlsm.xlsm

    Wonderfull
    Excellent

    Its doing Perfectly as desired.


    Thanks a million once again

    Quote from gijsmo

    Absolutely, the attached update has a 2nd sheet called PreTerm which holds the fixed headers. Obviously you can rename and reformat the headers as required.


    The PreTerm sheet is copied as a starting point for the output file. The data from the individual files is then appended below these headers.


    PreTermExtractor v2xlsm.xlsm

    As I mentioned earlier that total files more than 200, the programme shuts down automatically. Means excel closes down.

    Is there any solution to it

    Without 200 or so test files, it's difficult to diagnose the issue.


    Have you tried breaking the files up into groups of, say, 20-50 files and test on those ie, not all the files at once but eventually run through all files.


    This might help determine if it's the number of files being processed or if there is one or more files that may be causing the issue.

    Quote from gijsmo

    Without 200 or so test files, it's difficult to diagnose the issue.


    Have you tried breaking the files up into groups of, say, 20-50 files and test on those ie, not all the files at once but eventually run through all files.


    This might help determine if it's the number of files being processed or if there is one or more files that may be causing the issue.

    Not yet

    I'll try and let you know.

    Quote from gijsmo

    Without 200 or so test files, it's difficult to diagnose the issue.


    Have you tried breaking the files up into groups of, say, 20-50 files and test on those ie, not all the files at once but eventually run through all files.


    This might help determine if it's the number of files being processed or if there is one or more files that may be causing the issue.

    Hi Gijsmo,

    I have found the problem. Its number of cell data to scrape.

    If I scrape only 5 cell data's then it works fine.


    But If I need to scrape 6-10 Cell data then it collapses.


    Any solution for this?

    Can you please clarify.


    When you say "5 cell data's" does this relate to the number of cells being scraped from each PreTerm sheet or do you mean the issue occurs after 5 workbooks?


    If it relates to cells, the next step would be to try and work out if there is a particular cell on a particular sheet that may be causing the issue.


    It is very hard to diagnose without actual data/workbooks but obviously you cannot upload these if they contain sensitive data.

    5 Cell data relates to number of cells being Scraped


    No particular cell or sheet causing the issue. Because when I reduce to 5 cells only it works fine.

    It crashes sometimes immediately, sometimes after few minutes.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login