How do I extract data from multiple workbooks using VBA

[Harrish]

Hello,

I am trying to create functions in VBA and I need to extract data from multiple workbooks. For example, is it possible to sum data(numbers) contained in multiple workbooks using VBA? If so, how do I create that function in VBA.

Any suggestions would be much appreciated,

 

[hiker95]

Harrish,

Welcome to the MrExcel board.

What version of Excel are you using?

What is the full path to the directory/folder of the files?

What is the sheet name in the files?

What is the cell, or range of cells, that you want to extract?

Can we have some screenshots of your Summary worksheet, and one from the multiple workbooks.

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

Or, if your file does not contain sensitive information, you can upload it to www.box.net and provide a link to your workbook.

 

[seageath]

Hello,

I am trying to create functions in VBA and I need to extract data from multiple workbooks. For example, is it possible to sum data(numbers) contained in multiple workbooks using VBA? If so, how do I create that function in VBA.

Any suggestions would be much appreciated,

The steps are:

- Open the file (you can create a list of the file in another worksheet of summary, this is what I do)

- When open the file loop all the worksheet inside the workbook to get the value

- Do not forget to create public variable to cater the sum of the data

- Normally for this operation you have already had specific word that mark the range of the amount of the data you want to capture for example total, subtotals etc.

Hope it help and happy coding

-sea-

 

[Harrish]

For simplicity, lets say I have 10 workbooks (wkbk1, wkbk2,......wkbk10) and I want to sum the values contained in cell A1 of each workbook. What is the VBA code to create this function? All the workbooks are contained in the same folder (FLDR 1) on the C: drive. I am using excel 2007 and have no experience using VBA. I thought it might be easier and less repetitive if I could create a function in VBA as opposed to using the sum function and linking the workbooks.

 

[hiker95]

Harrish,

What version of Excel are you using? Excel 2007


What is the full path to the directory/folder of the files? C:\FLDR 1


What is the sheet name in the files to copy from?


What is the cell, or range of cells, that you want to extract? A1


How may workbooks are you copying from?


Are the number of workbooks to copy from changing?

 

[Harrish]

I am copying from 10 different workbooks. No, the number of workbooks isn't changing.

 

[hiker95]

Harrish,

One last try:

What is the sheet name in the files to copy from?

 

[Harrish]

For simplicity, can we just call them; wkbkone, wkbktwo.......wkbkten?

 

[hiker95]

Harrish,

Please answer each qustion.

Is there more than one worksheet in each of the workbooks in "C:\FLDR 1"?


What is the worksheet name in each of the workbooks in "C:\FLDR 1"?

 

[Harrish]

There is only one worksheet in each workbook and their names are:
PDP,BDS,EQT,TW,SNO,CYN,CAR,FDN......respectively (wkbkone thru eight). Sorry, I guess I only have eight workbooks.