Invoicing master sheet from others

[bobbybrown]

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!

 

[bobbybrown]

Would you like me to send you the master workbook and the dummy sheets like I did with the invoice file? Would that help at all?

Thankyou for your help with this.

 

[George J]

That would be good - at least we'll be working on the same data.
If you put the folder with dummy invoices and the master workbook into a zipped folder that would be great.

 

[bobbybrown]

No problem, I'll do that and send you the link to download shortly.

 

[George J]

Well that was horrible.

A few silly errors in there that I should have spotted first time around. That's what happens with late-night coding. Sorry.
The invoice number - I was trying to compare the number from the last recorded number with that of the file name, but had that as a string. Can't do that, so now changed both to integer.
The last row used I identified at the start and used in the Do while loop for all the invoices - didn't change the row number as I progressed though, did I?

I have left a couple of lines commented out as I wasn't sure if you would want the transaction ID against each item, or just the invoice total. The other one is to extract the outstanding total from the invoice - if there ever is one.

I would suggest having "Last Invoice: " right aligned in cell F1 and maybe a Title at the top of the workbook so you can easily identify what the report is showing.
Other than that, I think that's everything.

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


    'look at each excel file in Folder
    InvoiceLocation = "C:\Testing\Testing" & "\" 'note the \ at the end is required
    myExt = "*.xls*"
    myFile = Dir(InvoiceLocation & myExt)
    
    Set wkshtMaster = ThisWorkbook.Sheets("All Invoices")
    With wkshtMaster
        LastInvNo = .Range("G1").Value
        intUseRow = .Range("A65536").End(xlUp).Offset(1, 0).Row
    End With


    Do While myFile <> "" 'This will look for workbooks in the folder, but not subfolders
        ThisFileInvNo = Mid(myFile, 1, InStr(1, myFile, " ") - 1)
        If LastInvNo < ThisFileInvNo Then
            'open workbook
            Set wkbk = Workbooks.Open(Filename:=InvoiceLocation & myFile)
            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 = Mid(myFile, Len(ThisFileInvNo) + 1, InStr(Len(ThisFileInvNo) + 1, myFile, ".", vbTextCompare) - Len(ThisFileInvNo) - 1) '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
            LastInvNo = ThisFileInvNo
            If Not wkshtMaster.Range("A" & intUseRow).Value = Empty Then intUseRow = intUseRow + 1
        End If
        'next file
        myFile = Dir
    Loop
    wkshtMaster.Range("G1").Value = LastInvNo
End Sub

 

[bobbybrown]

That seems to have cracked it! Massive thanks to you.

When I run the code, it will cycle through the invoices and open each one up as it cycles through, it then asks if I want to save them or not, this pops up for every file.
Not a problem when testing with 3 but if running the code on 300 it may cause some issues. Is there a way to get the code to close the invoice its opened once its pulled the data from it and have it deal with the "do you want to save the changes" prompt?

This is just what I was looking for George, really greatful.

 

[George J]

Glad it is of use.

Before the line wkbk.Close
put wkbk.saved=true
This will tell the code that the invoice sheet has already been saved and to just close.

It's a bit strange though as you aren't making any changes to the invoice. Are you using xls files? That may explain it.

 

[bobbybrown]

Xlsx files.

I'll that the extra bit to the code this evening and give it a whirl

 

[bobbybrown]

Just a quick update to this, I haven't added the code yet however when testing at work using Excel 2007 I don't get the save prompts and it works as it should.
At home when using Excel 2010 I get the prompts.

I'm guessing its due to a difference in versions? I'll try adding the snippet of code to the file on my home computer tonight and see if it takes away the save prompts.

 

[bobbybrown]

Hi,
I've now managed to test this out in a real world environment without dummy data and its giving me a couple of issues.
In my folder I have pasted in 183 invoices. When I run the code it lists 1 to 10, then the next one is 100 so its missing invoice 11 through to 99.
Also when browsing through the data it has compiled it missed some here and there, its missing 148 to 153.

I've had a look through the coding but I can't see anything obvious that may be causing it to create these issues.

The invoice file used is exactly the same as the template I used when using dummy data.

Any help appreciated.

Many thanks in advance.

 

[bobbybrown]

One more thing to add that I've just noticed, when it opens each file as it cycles through it opens 10, then opens 100, so its completely missing 11 to 99.

Also when its finished running I get a Runtime Error '5'. Invalid procedure call or argument.