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

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
wrguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dirknibleckCommented:
use application.getopenfilename() to prompt a user to pick a file.
0
dirknibleckCommented:
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

wend




0
dirknibleckCommented:
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, "\"))
    Wend
   
    temp = Left(str, i)
    dirStr = temp
       
End Function
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

dirknibleckCommented:
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

wend
0
Rory ArchibaldCommented:
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
        .NewSearch
        .LookIn = strParentFolder
        .SearchSubFolders = False
        .FileName = "*.xls"
        .MatchTextExactly = True
        .FileType = msoFileTypeExcelWorkbooks
        .Execute
        ' 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

HTH
Rory
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wrguyAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.