VBA: Extract data from multiple spreadsheets :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

VBA: Extract data from multiple spreadsheets
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

Melanie
Board Regular


Joined: 17 Sep 2003
Posts: 30

Flag: Blank

Status: Offline

 Reply with quote  

VBA: Extract data from multiple spreadsheets

I have several spreadsheets that are formatted exactly the same and I want to extract data located in the same place on each spreadsheet. Is there a way in vba to accomplish this? I do have the names of each spreadsheet on a separate spreadsheet.

I essentially want to loop through the spreadsheet names, open up each spreadsheet, extract the data and paste it in a table format.

Thanks in advance.

Post Fri Sep 19, 2003 3:18 pm 
 View user's profile Send private message

earlyd
Board Master


Joined: 11 Dec 2002
Posts: 955

Flag: Usa

Status: Offline

 Reply with quote  

Re: VBA: Extract data from multiple spreadsheets

The following statement in a VBA macro with your values substituted for the "This" and "That" items below will copy from 1 book to another:

Workbooks(ThisBook).Sheets(ThisSheet).cells(ThisRow,ThisColumn).value = Workbooks(ThatBook).Sheets(ThatSheet).cells(ThatRow,ThatColumn).value

Post Fri Sep 19, 2003 3:21 pm 
 View user's profile Send private message

Melanie
Board Regular


Joined: 17 Sep 2003
Posts: 30

Flag: Blank

Status: Offline

 Reply with quote  

Re: VBA: Extract data from multiple spreadsheets

I will definitely try that.

Do you know how to loop through the spreadsheet names I have listed on the spreadsheet?

Post Fri Sep 19, 2003 3:25 pm 
 View user's profile Send private message

earlyd
Board Master


Joined: 11 Dec 2002
Posts: 955

Flag: Usa

Status: Offline

 Reply with quote  

Re: VBA: Extract data from multiple spreadsheets

I just realized that you are referring to spreadsheets and not workbooks. Do you mean extracting data from several sheets within the same workbook or from several different workbooks?

Post Fri Sep 19, 2003 4:05 pm 
 View user's profile Send private message

Melanie
Board Regular


Joined: 17 Sep 2003
Posts: 30

Flag: Blank

Status: Offline

 Reply with quote  

Re: VBA: Extract data from multiple spreadsheets

I meant extracting from several different workbooks.

For example, if I have the following workbook names listed in cells A1, A2, and A3 of a spreadsheet.

A.xls
B.xls
C.xls

I want to loop through the three cells and open up each workbook using the name contained within each cell. Then I need to copy a range from each workbook and paste it into a common spreadsheet.

Post Fri Sep 19, 2003 4:15 pm 
 View user's profile Send private message

earlyd
Board Master


Joined: 11 Dec 2002
Posts: 955

Flag: Usa

Status: Offline

 Reply with quote  

Re: VBA: Extract data from multiple spreadsheets

Here is my suggestion; others may prefer a different method:

Above the workbooks: Have a cell where you enter the folder name where the workbooks have to be read from, assuming they are all in the same folder (?)

Give that cell a range name of "FolderName".

Also above the workbooks - have a "header row" where you type something like "List of Workbooks". Name that cell "WkbksHeader".
Beneath the list of workbooks have a "footer row" where you type "End of List". Name that cell "WkbksEnd".

Your loop can be a For/Next: For NextBook = (Range("WkbksHeader").row + 1) to (Range("WkbksEnd").row - 1)

Each time through the loop, you get the next workbook name, append it to the foldername, which you read prior to the loop, and open the workbook. Do your cell copies and then close the workbook.

Hope this gets you started.
icon_smile.gif

Post Fri Sep 19, 2003 4:29 pm 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.