How to extract data via a macro from multiple workbooks?

**lross318** · 09-12-2012, 08:26 PM

Hi everyone!

I am new to the Forum, but I have a sticky issue. I have about 500 excel files that contain some data elements that I need to extract and insert into a master workbook. Using VBA I would like to call the individual workbooks and get the information from the specific cells. The cell reference will be the same in all the workbooks but each workbook has a different name (i.e Workbook names are: John.Smith.xlsm, Jane.Smith.xlms, James.Smith.xlms and in each workbook is data in cell reference (12,3) that I would like to have input in the master workbook.) Is this possible?

**arlu1201** · 09-13-2012, 04:32 AM

Sure its possible. So only M12 in each file that should be extracted into the master file?

Should the data be input one below the other? Do you want the name of the file to be input into the master file as well?

**TMS** · 09-13-2012, 05:04 AM

@Arlette: M12? I think it would be L3 or C12 depending on the way the co-ordinates are interpreted.

Cells(12,3) would be C12.

Regards, TMS

**arlu1201** · 09-13-2012, 05:13 AM

My bad TMS. I saw it as 12,13.

Yes, it should be C12.

**lross318** · 09-13-2012, 07:46 AM

Yes, C12 is the only thing that needs to be extracted from each file and the data should be one below the other. So in the master file C2 = Smith D2 = John J2 is where I want the macro to insert the data from the other excel file, for John.

**arlu1201** · 09-13-2012, 10:43 AM

Do you want the filename to be inserted along with the value from C12 of each file?

**lross318** · 09-13-2012, 11:35 AM

Yes, but that should be in the next column

**arlu1201** · 09-13-2012, 01:54 PM

Try this code

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

**lross318** · 09-13-2012, 07:53 PM

Thank you! This is really helpful! If I understand the code and variables correctly, I should replace {myPath = "D:\Test"} with mypath = {the directory of the excel files} is that true?

Thanks so much!
LRoss

**arlu1201** · 09-14-2012, 12:50 AM

Yes you are right.

**mouli51188** · 09-16-2012, 01:33 AM

Hi

i tried this and its working good. i am in the same position as lross318. I am having set of excel sheet in a single folder. The datas in the excel will change every month in the same cell. We took those values and paste it in another table. But this macro stored the exact values in the exact position of the master sheet. Did you have any idea to store those values in the some other cell of the master sheet.

**Cutter** · 09-16-2012, 06:51 AM

Hello mouli51188, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

**mouli51188** · 09-16-2012, 07:26 AM

Hi ,

Sorry for violating forum rules. wont do the same in future. Kindly chk the below thread for my queries

http://www.excelforum.com/excel-prog...her-excel.html

**lross318** · 09-16-2012, 06:20 PM

When I try to run the code I get a Debug error at {Workbooks.Open (myPath & "\" & CurrentFileName)}. Any thoughts?

**arlu1201** · 09-17-2012, 04:11 AM

What is the error number / description?

**lross318** · 09-17-2012, 07:15 AM

Run-time Error '1004'

'C:\test\*xmls' could not be found.

But I put the files in a test folder on the C drive, so I am a little confused.

**arlu1201** · 09-17-2012, 07:29 AM

What is the file extension of all the files?

**lross318** · 09-17-2012, 07:39 AM

Actually I see, what that error was, but now I get a Run time error number 52 Bad file name or number

**arlu1201** · 09-17-2012, 07:48 AM

What is the file extension?

**lross318** · 09-17-2012, 07:49 AM

All the files are .xlsm.

So my C:\test folder worked but I am wondering if I am getting an error because the files live on a secure shared server.

**arlu1201** · 09-17-2012, 08:01 AM

I am not sure about the secure share drive, but change this line from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

**lross318** · 09-17-2012, 08:21 AM

OK. I did that.

Here is where I am getting the runtime error 52 (Bad file name):

CurrentFileName = Dir(myPath & "*.xlsm")

It is as if the code is not recognising that the "*" is a wild card.

**arlu1201** · 09-17-2012, 08:31 AM

That line should be CurrentFileName = Dir(myPath & "\*.xlsm")

How to extract data via a macro from multiple workbooks?

LinkBack

Thread Tools

Rate This Thread

Display

How to extract data via a macro from multiple workbooks?

Re: How to extract data via a macro from multiple workbooks?

Re: How to extract data via a macro from multiple workbooks?

Re: How to extract data via a macro from multiple workbooks?

Re: How to extract data via a macro from multiple workbooks?

Re: How to extract data via a macro from multiple workbooks?

Re: How to extract data via a macro from multiple workbooks?

Re: How to extract data via a macro from multiple workbooks?

Re: How to extract data via a macro from multiple workbooks?

Re: How to extract data via a macro from multiple workbooks?

Re: How to extract data via a macro from multiple workbooks?

Re: How to extract data via a macro from multiple workbooks?

Re: How to extract data via a macro from multiple workbooks?

Re: How to extract data via a macro from multiple workbooks?

Re: How to extract data via a macro from multiple workbooks?

Re: How to extract data via a macro from multiple workbooks?

Re: How to extract data via a macro from multiple workbooks?

Re: How to extract data via a macro from multiple workbooks?

Re: How to extract data via a macro from multiple workbooks?

Re: How to extract data via a macro from multiple workbooks?

Re: How to extract data via a macro from multiple workbooks?

Re: How to extract data via a macro from multiple workbooks?

Re: How to extract data via a macro from multiple workbooks?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions