Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 04-08-2017, 08:38 AM
alistair_ alistair_ is offline Windows XP Office 2016
Novice
 
Join Date: Apr 2017
Posts: 2
alistair_ is on a distinguished road
Default Summarise information from multiple worksheets in multiple files

Hi All,



I'm a novice with Excel and new to the forum so I apologise if the post isn't clear.

I have a series of excel files in one folder. Each excel file contains information about a different material property i.e. unit weight, specific gravity etc. The material property is the file name.

Each file has a series of worksheets which contains information for different material types i.e. material 1, material 2 etc. The material name is the tab name. Within these worksheets I have calcuated the minimum, maximum, mean and standard deviation and these are always shown in the same cell.

I would like to use a VBA script in excel, so that I select the folder location and excel opens each individual file within the folder, record the file name, and for each tab, records the tab name summarise the information shown in these cells A3&4, B3&4, C3&4, & D3&4 (note cell 3 will be text and cell 4 an number). It would ideally loop through each file in folder and summarise the information into one excel table.

I was wondering whether anybody could help me out with a code to show a table like that below.

Any help would be greatly appreciated

Thanks


File Name i.e. Unit Weight
Tab 1 Name - Cell A3 - Cell A4
Tab 1 Name - Cell B3 - Cell B4
Tab 1 Name - Cell C3 - Cell C4
Tab 1 Name - Cell D3 - Cell D4

Tab 2 Name - Cell A3 - Cell A4
Tab 2 Name - Cell B3 - Cell B4
Tab 2 Name - Cell C3 - Cell C4
Tab 2 Name - Cell D3 - Cell D4

File Name i.e. Specific Gravity
Tab 1 Name - Cell A3 - Cell A4
Tab 1 Name - Cell B3 - Cell B4
Tab 1 Name - Cell C3 - Cell C4
Tab 1 Name - Cell D3 - Cell D4

Tab 2 Name - Cell A3 - Cell A4
Tab 2 Name - Cell B3 - Cell B4
Tab 2 Name - Cell C3 - Cell C4
Tab 2 Name - Cell D3 - Cell D4
Reply With Quote
  #2  
Old 04-08-2017, 11:26 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 1,901
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

Hi
this thread has been marked as " solved". Is this correct? There do not seem to be any answers?
Reply With Quote
  #3  
Old 04-09-2017, 03:10 AM
alistair_ alistair_ is offline Windows XP Office 2016
Novice
 
Join Date: Apr 2017
Posts: 2
alistair_ is on a distinguished road
Default

Hi Pecoflyer,

No it hasn't been solved yet. Not sure where the solved icon came from. Do you know if I can remove it?

Thanks
Reply With Quote
  #4  
Old 04-09-2017, 08:32 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 1,901
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

It's under "thread tools". I did it for you. Cheers
Reply With Quote
  #5  
Old 04-09-2017, 08:34 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 1,901
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

To solve your problem perhaps
http://www.rondebruin.nl/win/addins/rdbmerge.htm
or
http://www.rondebruin.nl/win/section3.htm
might help?
Reply With Quote
  #6  
Old 04-10-2017, 08:27 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Posts: 388
NoSparks is on a distinguished road
Default

Adapted from an example at The Spreadsheet Guru for looping through files in a chosen folder.
Maybe you can make use of something like this.
Attached Files
File Type: xlsm Info Summary.xlsm (22.9 KB, 3 views)
Reply With Quote
Reply

Tags
excel vba, vba script
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to open Multiple files and copy information to a master file gbaker Excel Programming 2 04-08-2016 08:44 AM
Combine or merge multiple worksheets into one worksheet timomaha Excel 1 07-21-2014 01:02 PM
Multiple Dynamic Worksheets Consolidated into One Static Worksheet mars1886 Excel Programming 3 02-09-2014 12:50 AM
Sum multiple worksheets and summary tab that would sum and count by rep name angie.chang Excel 1 05-16-2013 12:36 AM
convert multiple csv files to multiple excel files mit Excel 1 06-14-2011 10:15 AM


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


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft