Invoicing master sheet from others

bobbybrown

Board Regular
Joined
Apr 17, 2015
Messages
121
Hi,
I'm trying to get my accounts in order here and as such I have a folder full of invoices, all exactly the same layout as I use a template file that I made to create each invoice.

What I am trying to figure out is:
Is there a way that I can create a master sheet in excel with the field name across the top such as invoice number, date, name, product, price, transaction id then have the master sheet scan my folder and pick the information out of every invoice sheet automatically using a macro and populate the master sheet as a big list of sales?

Some people have more than 1 product so not sure how this could be handled

I could then autosum the total price so I can keep a running total of invoice totals.

If there was a way to re-run this periodically but have the macro only read new files that would be perfect too but I'm not sure if something like this could be done.

I guess first I am looking to see if this is possible and if so the best way to go about it.

Many thanks to you for reading my post!
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
No, it goes 1,2,3,4,5,6,7,8,9,10,100,101,102,103 and so on.
Also just noticed this George.
Records 1-9 are fine, when it reaches 3 numbers its adding a number to the customers name.
For example invoice 100 has a name of 0Name
101 = 1Name
102 = 2Name
all the way down to the final invoice, 183 which is 3Name.

Not sure if that will make a difference with finding the issue?
 
Last edited:
Upvote 0
I’ve been looking at this more and tried it on another computer but still get the same issues and can not see anything obvious causing it
 
Upvote 0
Sorry for not replying sooner Bobby. Work and then Christmas break getting in the way.

It looks like our initial assumption that the computer would read the documents in the order they were created is wrong. I believe this is what is causing the problem. Instead of the code looking at each document in the folder, we will need to amend it to list each reference/invoice no. for each document and then sort that into order. We can then use that list for the code to extract the data from.

I will hopefully get a chance to look at this soon.
 
Upvote 0
Hi Dan

Hope this is still of use to you.

Instead of going through each file in the order decided by the directory search, I amended the code to list all the excel files in the folder and take the name of each file to determine the invoice number and to sort by that (so don't add anything to columns K or L). It looks like it will do what you asked now - sorry it took so long. I had even got rid of the test data I had used first time around.

(Don't forget to amend the folder location back again)

Code:
Sub CompileInvoices()Dim InvoiceLocation As String, myExt As String, myFile As String
Dim LastInvNo As Integer, ThisFileInvNo As Integer, strCustomer As String
Dim wkbk As Workbook, intUseRow As Integer, wkshtMaster As Worksheet
Dim rng As Range, rngInv As Range, objFSO As Object, objFolder As Object, objFile As Object
    
    Set wkshtMaster = ThisWorkbook.Sheets("All Invoices")
    
    'look at each excel file in Folder
    InvoiceLocation = "C:\Users\GJJ\Desktop\TESTING" & "\" 'note the \ at the end is required
    myExt = "*.xls*"
    'myFile = Dir(InvoiceLocation & myExt)
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(InvoiceLocation)
    For Each objFile In objFolder.Files
        If "." & objFSO.getextensionname(objFile) Like myExt And Not objFile.Name = ThisWorkbook.Name Then
            wkshtMaster.Range("K65536").End(xlUp).Offset(1, 0).Value = objFile.Name
            wkshtMaster.Range("K65536").End(xlUp).Offset(0, 1).Value = _
                                                        Mid(objFile.Name, 1, InStr(1, objFile.Name, " ", vbTextCompare) - 1)
        End If
    Next objFile
    'sort column L in order
    Range("K:L").Sort key1:=Range("L1"), order1:=xlAscending, Header:=xlNo


    For Each rngInv In wkshtMaster.Range(wkshtMaster.Range("K1"), wkshtMaster.Range("K65536").End(xlUp))
        With wkshtMaster
            LastInvNo = .Range("G1").Value
            intUseRow = .Range("A65536").End(xlUp).Offset(1, 0).Row
        End With
        
        ThisFileInvNo = rngInv.Offset(0, 1).Value
        If ThisFileInvNo > LastInvNo Then
            'open workbook
            Set wkbk = Workbooks.Open(Filename:=InvoiceLocation & rngInv.Value)
            DoEvents
            For Each rng In wkbk.ActiveSheet.Range("B21:B45")
                If Not rng.Value = Empty And Not rng.Value = "Transaction ID" Then
                    wkshtMaster.Range("A" & intUseRow).Value = ThisFileInvNo  'A - invoice no
                    wkshtMaster.Range("B" & intUseRow).Value = wkbk.ActiveSheet.Range("C17").Value 'B - Date
                    wkshtMaster.Range("C" & intUseRow).Value = wkbk.ActiveSheet.Range("G8").Value 'C - Name
                    wkshtMaster.Range("D" & intUseRow).Value = wkbk.ActiveSheet.Range("C" & rng.Row).Value 'D - Prod
                    wkshtMaster.Range("E" & intUseRow).Value = wkbk.ActiveSheet.Range("B" & rng.Row).Value 'E - Qty
                    wkshtMaster.Range("F" & intUseRow).Value = wkbk.ActiveSheet.Range("J" & rng.Row).Value 'F - Price
                    wkshtMaster.Range("G" & intUseRow).Value = wkbk.ActiveSheet.Range("D45").End(xlUp).Value 'G - Trans ID
                    intUseRow = intUseRow + 1
                End If
            Next rng
            'wkshtMaster.Range("G" & intUseRow - 1).Value = wkbk.ActiveSheet.Range("D45").End(xlUp).Value 'G - Trans ID
            wkshtMaster.Range("H" & intUseRow - 1).Value = wkbk.ActiveSheet.Range("J50").Value 'H - Invoice Total
            'wkshtMaster.Range("I" & intUseRow).Value = wkbk.ActiveSheet.Range("J53").Value 'I - Outstanding


            wkbk.Close
            wkshtMaster.Range("G1").Value = ThisFileInvNo
        End If
    Next rngInv
    wkshtMaster.Range("K:L").Value = Empty
End Sub
 
Last edited:
Upvote 0
Hi George,
I will give it a go tomorrow when I’m back with the laptop and let you know the results.

Big thanks to you for coming back to this, I appreciate it.

Regards
Dan
 
Upvote 0
Hi George,
Sorry for the long delay, I have spent the entire month of January unwell and as such have rarely been on the computer.

I've just tested your updated code and it seems to work exactly as we were hoping.

Once again many thanks for your help, couldn't have done it without you.

Thanks,
Dan
 
Upvote 0
Thanks for getting back to me - was just thinking about this a few days ago.
Glad all seems sorted now.

Get well soon.
George
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top