Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-16-2010, 04:22 PM
psrs0810 psrs0810 is offline macro to pull data from multiple files Windows 7 macro to pull data from multiple files Office 2007
Novice
macro to pull data from multiple files
 
Join Date: Aug 2010
Posts: 5
psrs0810 is on a distinguished road
Question macro to pull data from multiple files

I need help with building a macro to pull data from over a 100 worksheets.
I have a questionnaire that I send out and now I need to pull the data from the worksheets. Every work sheet is the same.

To give you an example to what I need to pull, I have a question: Is your facility a standalone or a system?
In cell C10 and E10 there would be a box for them to put an X. I need to know how many X's or anything typed in that cell from all of the worksheets.



To help, I have all of the worksheets saved in one folder.

Thank you for your help in advance
Reply With Quote
  #2  
Old 10-23-2010, 07:32 PM
MWE MWE is offline macro to pull data from multiple files Windows XP macro to pull data from multiple files Office 2003
Novice
 
Join Date: Oct 2010
Posts: 8
MWE is on a distinguished road
Default

Quote:
Originally Posted by psrs0810 View Post
I need help with building a macro to pull data from over a 100 worksheets.
I have a questionnaire that I send out and now I need to pull the data from the worksheets. Every work sheet is the same.

To give you an example to what I need to pull, I have a question: Is your facility a standalone or a system?
In cell C10 and E10 there would be a box for them to put an X. I need to know how many X's or anything typed in that cell from all of the worksheets.

To help, I have all of the worksheets saved in one folder.

Thank you for your help in advance
writing a macro to do this is pretty easy assuming that you really know what data is to be read from each target worksheet. All we need to do is sequence through the target worksheets, open them, read the relevant data, save it in the master, etc. If you have all the target worksheets saved in one folder than the process is even easier because we do not need a list of worksheet names; we can just sequence through every worksheet in the target folder.

The macro would work with 1 target worksheets or 5 or 500 and can read any number of things from each worksheet as long as what is read does not change from worksheet to worksheet. I assume that you would want the macro to build a table in the master worksheet indicating each worksheet name and the relevant values read. The process steps would be:
  1. you open the "master" and click on a button or menu pick to start the process
  2. a window pops up asking you to navigate to the folder containing the target worksheets
  3. the macro creates a new sheet in the master with a unique name for this run (probably the current date)
  4. the macro opens each worksheet in the folder, reads the relevant data, creates a row entry in the new sheet in the master with the target name and relevant values and then closes the worksheet
If this is essentially corrent, I can do this in 15 minutes. Let me know.
Reply With Quote
  #3  
Old 10-25-2010, 01:49 PM
hannu hannu is offline macro to pull data from multiple files Windows Vista macro to pull data from multiple files Office 2007
Advanced Beginner
 
Join Date: May 2010
Location: Sydney, Australia
Posts: 30
hannu is on a distinguished road
Default

This is a great question and answer, let us know if there is going to be a youtube video of the process, I am interested to see it. I teach Excel and the objective of the more advanced groups is to create macros that save work time in cleaning up SAP imports and collect data from various places.

H
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Template pull down menus terpsichore Word 0 03-02-2010 02:39 PM
Macro to export document sections to individual txt files? MJMR999 Excel Programming 0 02-18-2010 12:49 PM
Merging Multiple Word files into One Stattovic Word 0 01-06-2010 07:19 AM
macro to pull data from multiple files Generating multiple documents from 1 data source themangoagent Word 2 08-14-2009 12:12 PM
Help-overwriting files-could it be macro virus? Timpotty Word 0 03-06-2009 04:28 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:06 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft