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

Posted on 2007-04-03
Medium Priority
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
  • 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 = application.workbooks.open(directorty & 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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 = application.workbooks.open(directorty & file)
    take what you need and enter on activesheet
end if

LVL 85

Accepted Solution

Rory Archibald earned 2000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

600 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