+ Reply to Thread
Results 1 to 4 of 4

Extract data from multiple workbooks into one data table

  1. #1
    Registered User
    Join Date
    09-02-2016
    Location
    Winchester, England
    MS-Off Ver
    Office 2013
    Posts
    2

    Question Extract data from multiple workbooks into one data table

    Hi,

    I have approx 400 excel files which contain a variety of data but I want to extract 7 particular cells of data from each and create a simple table of this data in a new workbook (7 columns x 400 rows). Can anyone show me a quick way of doing this. I'm sure it can be done but I cannot find a simple solution anywhere.

    All 400 files are saved in "C:\Documents\Data Extraction"

    Cell references are as follows.

    PIN - I10
    Start Easting - D12
    Start Northing - F12
    End Easting - H12
    End Northing - J12:K12 (Merged cell)
    Safety Score - D128
    Total Score - I129

    Any help would be much appreciated.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,410

    Re: Extract data from multiple workbooks into one data table

    This may get what you want. It looks in the directory for Excel files of any type. The assumption is that the data is on the first sheet of the files in the directory. I don't have any source files, so the code is untested. There might be an issue with the merged cell. If it's a problem let me know.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    09-02-2016
    Location
    Winchester, England
    MS-Off Ver
    Office 2013
    Posts
    2

    Re: Extract data from multiple workbooks into one data table

    Thank you dflak.

    Sorry, I forgot to mention the data is on Sheet 2, renamed to "Pavement SAR"

    Can the code be changed to look on sheet 2 instead? I would send a source file but they are quite large.

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,410

    Re: Extract data from multiple workbooks into one data table

    You have a choice. Change this line
    Set shS = xlFile.Sheets(1)
    to
    Set shS = xlFile.Sheets(2)
    or better yet
    Set shS = xlFile.Sheets("Pavement SAR")

+ 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. Extract Specific Data from Multiple Workbooks
    By beartimeusa in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-10-2013, 08:54 AM
  2. Vba to extract data from multiple closed workbooks
    By The1theycallp in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-07-2013, 07:49 AM
  3. extract data from multiple workbooks to one report
    By me1992 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-04-2013, 07:46 AM
  4. How to extract data via a macro from multiple workbooks?
    By lross318 in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 09-17-2012, 08:31 AM
  5. Extract Data from Multiple Workbooks to One Sheet
    By jketcher in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-01-2012, 04:41 PM
  6. Extract data from multiple workbooks
    By mclav in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-06-2011, 04:46 PM
  7. Extract data from multiple workbooks to master
    By unley in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-24-2010, 06:22 AM
  8. Data Extract from Multiple Workbooks.
    By realdealsxbl in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-04-2008, 11:13 PM

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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.06224 seconds
  • Memory Usage 8,961KB
  • Queries Executed 16 (?)
More Information
Template Usage (34):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (4)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (4)postbit_legacy
  • (4)postbit_onlinestatus
  • (4)postbit_wrapper
  • (4)showthread_bookmarksite
  • (8)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (50):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • postbit_attachment
  • tag_fetchbit
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1