Solved

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

Posted on 2007-04-03
6
248 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
[X]
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
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

Technology Partners: 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!

Question has a verified solution.

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

Suggested Solutions

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
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 Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

710 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