VBA to add up invoice totals from all invoices (stored in separate workbooks) in specified folder.

Posted on 2007-04-03
Last Modified: 2010-04-30
Does anyone have a snippet of VBA code that does some or all of the following:
- prompts the user to browse for and select a folder in windows
- accesses all the Excel workbooks in that folder (which in my case will be invoices with a single worksheet containing identical formats and layouts, but different values)
- adds up all the invoice totals of which there is one in each workbook
- reports the total value of all the separate invoices
Question by:wrguy
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
LVL 15

Expert Comment

ID: 18844467
use application.getopenfilename() to prompt a user to pick a file.
LVL 15

Expert Comment

ID: 18844484
If you parse the string from getopenfilename, you can obtain the directory and then

file = dir(directory)

while file <> ""

if right(file, 3) = "xls" then
    set wb = & file)
    take what you need and enter on activesheet
end if


LVL 15

Expert Comment

ID: 18844493
You could use this to get the directory:

Function dirStr(str As String) As String

    Dim temp As String
    Dim i As Integer
    temp = str
    i = 0
    While InStr(temp, "\") > 0
        i = i + InStr(temp, "\")
        temp = Right(temp, Len(temp) - InStr(temp, "\"))
    temp = Left(str, i)
    dirStr = temp
End Function
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 15

Expert Comment

ID: 18844505
So then:

directory = dirstr(application.getopenfilename()
file = dir(directory)

while file <> ""

if right(file, 3) = "xls" then
    set wb = & file)
    take what you need and enter on activesheet
end if

LVL 85

Accepted Solution

Rory Archibald earned 500 total points
ID: 18844563
This will return the file names and value from cell G3 for each workbook in the specified directory to the active worksheet:

Sub GetTotals()
    TotalFiles "G3"
End Sub
Function GetFolder() As String
    Dim dlg As FileDialog
    Set dlg = Application.FileDialog(msoFileDialogFolderPicker)
    If dlg.Show = -1 Then
        GetFolder = dlg.SelectedItems(1)
    End If
End Function
Sub TotalFiles(strRange As String)
    Dim fs As FileSearch
    Dim lngCounter As Long, lngRow As Long, lngOutputrow As Long
    Dim wbk As Workbook, wks1 As Worksheet, wks2 As Worksheet, wksSummary As Worksheet
    Dim rngData As Range, rngCell As Range
    Dim strCompany As String, strParentFolder As String
    strParentFolder = GetFolder()
    If Len(strParentFolder) = 0 Then Exit Sub
    Application.ScreenUpdating = False
    Set wksSummary = ActiveWorkbook.ActiveSheet
    lngOutputrow = 1
    Set fs = Application.FileSearch
    With fs
        .LookIn = strParentFolder
        .SearchSubFolders = False
        .FileName = "*.xls"
        .MatchTextExactly = True
        .FileType = msoFileTypeExcelWorkbooks
        ' Loop through all the found files
        For lngCounter = 1 To .FoundFiles.Count
            Set wbk = Workbooks.Open(.FoundFiles(lngCounter))
            With wksSummary
               .Cells(lngOutputrow, 1) = wbk.Name
               .Cells(lngOutputrow, 2) = wbk.Sheets(1).Range(strRange).value
            End With
            lngOutputrow = lngOutputrow + 1
            wbk.Close False
        Next lngCounter
        Set wbk = Nothing
    End With
    Set fs = Nothing
    Application.ScreenUpdating = True
End Sub


Author Comment

ID: 18844713
Rory, thats awesome - thank you.  Exaclty what I wanted and works pefectly.

Dirknibleck - thank you for your answers too.  I decided to accept Rory's answer as it gave me the whole answer in one neat package.

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

636 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