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,

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

 

[Harrish]

The worksheet name in each of the workbooks are the following:

WorkbookOne; Worksheet name = PDP
WorkbookTwo; Worksheet name = EQT
WorkbookThree; Worksheet name = BDS
WorkbookFour; Worksheet name = TW
WorkbookFive; Worksheet name = SNO
WorkbookSix; Worksheet name = CAR
WorkbookSeven; Worksheet name = CYN
WorkbookEight; Worksheet name = FDN

There is only one worksheet in each workbook.

 

[hiker95]

Harrish,

Here you go.


I have three workbooks in a folder, each with a different value in cell A1 (100.01, 200.02, and 300.03).


Before the macro, in your Summary workbook, the active sheet:

Excel Workbook
	A
1
Sheet1

After the macro:

Excel Workbook
	A
1	600.06
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Sub SumA1s()
Dim MyDir As String, FN As String, MyTotal As Double
Application.ScreenUpdating = False

'********** Change the path to suit your environment **********
'MyDir = "C:\TestData\"

MyDir = "C:\FLDR 1\"


FN = Dir(MyDir & "\*.xls")
Do While FN <> ""
  If FN <> ThisWorkbook.Name Then
    With Workbooks.Open(MyDir & FN)
      With .Sheets(1)
        MyTotal = MyTotal + .Range("A1").Value
      End With
      .Close False
    End With
  End If
  FN = Dir
Loop
Range("A1") = MyTotal
Application.ScreenUpdating = False
End Sub

Then run the "SumA1s" macro.

 

[Harrish]

Awesome. Worked just how I envisioned. Thanks a bunch for your help and patience.

Regards,

 

[Harrish]

Hiker95,

Thanks again for your help. It would be great to see an explanation for each line of code, just so I can understand it better. Some lines I understand, but others I have no idea what the purpose is. Sometime if you have a moment, that would be great.

 

[hiker95]

Harrish,

It would be great to see an explanation for each line of code

  'Option Explicit requires me to assign my Variables with 'Dim' statements.
  '
Option Explicit
Sub SumA1s()

  'These are the 'Variables' and 'type' I used in the macro:
  '  MyDir is a string variable for the directory/folder where the data files are stored
  '  FN is a string variable for the filenames
  '  MyTotal is used to sum the various A1's
  '    Its type is 'Double' to sum numbers with decimal places
  '
Dim MyDir As String, FN As String, MyTotal As Double

  'This turns off screen flicker, and allows the code to run faster
  '
Application.ScreenUpdating = False

'********** Change the path to suit your environment **********
'MyDir = "C:\TestData\"

MyDir = "C:\FLDR 1\"

  'We are searching for '*.xls' filenames in MyDir
  '
FN = Dir(MyDir & "\*.xls")

  'Loop in the directory/folder until there are no more '*.xls' files.
  '
Do While FN <> ""
  
  'Do not include this workbook in the search for '*.xls' files,
  '  if it is stored in the directory/folder where the data files are stored.
  '
  If FN <> ThisWorkbook.Name Then
    
    'Open each '*.xls' file found
    '
    With Workbooks.Open(MyDir & FN)
      
        'Using the first worksheet in each workbook, where the data files are stored,
        '  instead of the worksheet name, that could vary.
        '
      With .Sheets(1)
      
        'Keep a running total of all range 'A1' values.
        '
        MyTotal = MyTotal + .Range("A1").Value
      End With
      
        'close the opened '*.xls' file that was found.
        '
      .Close False
    End With
  End If
    
    'Get the next '*.xls' filename.
    '
  FN = Dir
  
  'Loop to the 'Do While' statement for the next '*.xls' file to open.
  '
Loop

  'In the active workbook, active worksheet, put the running total in range A1.
  '
Range("A1") = MyTotal

  'Turn on screen updating.
  '
Application.ScreenUpdating = False

  'End the macro.
  '
End Sub

 

[Harrish]

So if I want to specify a specific cell to place the sum total, which line of code do I do that on? Is it: Range("A1") = MyTotal

 

[hiker95]

Harrish,

So if I want to specify a specific cell to place the sum total, which line of code do I do that on? Is it: Range("A1") = MyTotal

Yes

 

[xzerox]

how will you write same code but on Mac excel 2004?

 

[hiker95]

xzerox,

how will you write same code but on Mac excel 2004?

Does Excel 2004 on a MAC allow for macros?