Solved

Extracting Data from Separate Excel Files

Posted on 2011-09-21
13
173 Views
Last Modified: 2012-06-21
Hello, Experts.

What I need to do is extract the values from a large number of separate Excel files that are all contained in one folder.
For instance, I have cell F7 from a tab named "ChgNotice", cell A17 from the same tab, and cell C1 from another tab named "Data Entry".
The cells and tabs they're located in are the same for all of the files.
I am hoping for something that will put the values of all of the files in this particular folder in a flat file of some kind, either in a database, a spreadsheet, or something I can import.
Is there a good macro solution for this, or some kind of code for MS Access I can use?

Thanks!!
0
Comment
Question by:Nero83
  • 7
  • 6
13 Comments
 
LVL 2

Expert Comment

by:jan24
ID: 36577642
Try this.  You enter the folder path (including final \) in cell A1 and the references you want to read to the right of it.  Then press the Churn button and off it goes.
Book9.xlsm
0
 

Author Comment

by:Nero83
ID: 36581112
Thanks.  However when I run this, it comes up with an error msg box that just says "400" and nothing happens.  I looked at the macro, but this isn't my strong point.  Any ideas on what's causing the problem?  I couldn't find out what the error means.
0
 
LVL 2

Expert Comment

by:jan24
ID: 36581575
Which version of Excel are you using?
Any chance you could send an example file, and my spreadsheet set up as you were using it when you got the error?  If you can do that I'll have a look and see if I can work out what's going wrong.
0
 

Author Comment

by:Nero83
ID: 36581726
I'm using 2003.  There was a conversion when I downloaded your spreadsheet.
See attached for Excel file as it ended up on my machine and an example of a file.

Thanks much!
ChurningFeeRecords.xlsm
0
 

Author Comment

by:Nero83
ID: 36581731
OK, here's an example of a file the data's coming from.
14144.xls
0
 
LVL 2

Expert Comment

by:jan24
ID: 36583846
Hi.  The "400" error message is pretty bizarre and unhelpful, but I think I've tracked the problem down.

The range name sources is #REF! in your version - I guess this was caused when Excel 2003 imported it because Excel 2003 can only cope with 255 columns.  If you redefine sources to refer to Sheet1!$B$1:$IV$1 then it should work.

Let me know if that solves it.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Nero83
ID: 36589498
Thanks again!  When you say "redefine sources", I'm unsure where you're referencing to...?  I feel a little dense here, but I looked around and haven't figured it out.  Help?
0
 
LVL 2

Expert Comment

by:jan24
ID: 36598071
Sorry to confuse.  There's a range name called "sources" in the worksheet.  A range name is a way of refering to a cell or range of cells by a meaningful name, rather than having to always use a reference like A1 or C3 in your formulae.

That range name had been "broken" in the process of Excel 2003 converting the workbook from Excel 2007 format to Excel 2003 format.

To fix it, press CTRL+F3 and select the one called "sources" and change the reference that it refers to so that instead of #REF! it is $B$1:$IV$1.

0
 
LVL 2

Expert Comment

by:jan24
ID: 36598075
In case that doesn't work for you, I've made the necessary change in the attached.
Book9-1-.xls
0
 

Author Comment

by:Nero83
ID: 36599852
Thanks again...still having problems with it.  When I save your file, it saves as "Book9-1-.xls".  
When I run the macro, it comes up with an error msg " 'Book[9].xlsm' could not be found..." attached.  I don't see where this file name is in the code or I would try to point it somewhere else or change the name to see if that could help.  I can't save the file with the square brackets in the name.
 Book9-Error-Msg.doc
0
 
LVL 2

Accepted Solution

by:
jan24 earned 500 total points
ID: 36600324
Oh dear - it does seem like Excel 2007 and 2003 don't like talking to each other sometimes!  It seems like the churn button is not correctly remembering which macro is supposed to be assigned to it.

I've tried again saving it as Book10.xls without any brackets.  Hopefully that will fix it.

If you are still having problems with the churn button then try this:
- Go into design mode (look up in Help if you're not familiar with this)
- Right-click on the "Churn This Folder" button
- Click Assign Macro
- Choose the one called Sheet1.churn
Then try it again.

Book10.xls
0
 

Author Closing Comment

by:Nero83
ID: 36600368
Excellent!  This worked perfectly.  I'm not sure how it works, but that'll just take some more time on my part which I don't have right now.

Thanks very much jan24!!!
0
 
LVL 2

Expert Comment

by:jan24
ID: 36600434
You're welcome!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to read BOM (Byte Order Mark) from csv file. 4 37
Highlighting cells in Excel 9 17
InStr Function not working properly in macro 3 19
Dynamic Filter ? 4 21
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now