Solved

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

Posted on 2007-04-03
6
246 Views
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
0
Comment
Question by:wrguy
  • 4
6 Comments
 
LVL 15

Expert Comment

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

Expert Comment

by:dirknibleck
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

wend




0
 
LVL 15

Expert Comment

by:dirknibleck
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, "\"))
    Wend
   
    temp = Left(str, i)
    dirStr = temp
       
End Function
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 15

Expert Comment

by:dirknibleck
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

wend
0
 
LVL 85

Accepted Solution

by:
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
        .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
 

Author Comment

by:wrguy
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.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

815 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now