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!

 

[George J]

This should be most of it - you may want to adjust to suit.
I didn't notice any VAT so you may want to check the cells on the non draft version to make sure I am picking up the right values.

Sub CompileInvoices()
Dim InvoiceLocation As String, myExt As String, myFile As String
Dim LastInvNo As Integer, ThisFileInvNo As String, 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:\Data\Data1\" '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
            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) 'C - Name
            For Each rng In wkbk.ActiveSheet.Range("B21:B45")
                If Not rng.Value = Empty And Not rng.Value = "Transaction ID" Then
                    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
                End If
            Next rng
            wkshtMaster.Range("G" & intUseRow).Value = wkbk.ActiveSheet.Range("D45").End(xlUp).Value 'G - Trans ID
            If Not wkshtMaster.Range("A" & intUseRow - 1).Value = ThisFileInvNo Then
                wkshtMaster.Range("H" & intUseRow).Value = wkbk.ActiveSheet.Range("J50").Value 'H - Invoice Total
                'wkshtMaster.Range("I" & intUseRow).Value = wkbk.ActiveSheet.Range("J53").Value 'H - Outstanding
            End If

            wkbk.Close
            LastInvNo = ThisFileInvNo
        End If
        'next file
        myFile = Dir
    Loop

End Sub 'Variation? VAT?

 

[George J]

Instead of mid(myFile, Len(ThisFileInvNo) + 1) for the customer name, this should be amended as it has the file extension on the end.
Try mid(myFile, Len(ThisFileInvNo) + 1, instr(Len(ThisFileInvNo) + 1, myFile, ".", vbtextcompare)-1)

 

[bobbybrown]

Thankyou so much George, I have tried to set up my master sheet according to how your code is laid out however I am getting runtime error 9, subscript out of range when I try to run it.
I have added the code to the master file.

 

[George J]

Hi Bobby

Have you amended the 'InvoiceLocation' path to the folder the invoices are located ?
In the Master Workbook, have you named the sheet 'All Invoices' ?
Have the Headings in Row 4 (leave row1 blank - G1 will be the last invoice number)

In the workbook code, you can create a workbook open event so the code will run straight away.
In the VB Editor, double click 'Thisworkbook' and put

Private Sub Workbook_Open()
Call CompileInvoices
End Sub

You will put the other macro in a standard module.

If it still isn't working, what line is it failing on?

Have you tried stepping through the code with F8 ?

 

[bobbybrown]

Thanks. I'll go through the steps tomorrow and let you know the outcome. I'm sure it will be something simple

 

[bobbybrown]

I have progress of sorts.
I've managed to get it to loop through the process however, I have put 3 dummy invoices in to a test folder and run it from there, it only picks up number 3, not 1 and 2.
Once it's done this it gives me the following error:
"Run-time error '5'. Invalid procedure call or argument"

I've tried to step through the code but it isn't telling me very much.
I've just run it again and it's picked up record number 3, missed a line then put a number 1 in the quantity box of the next line with no other information.

I'm sure this is something very simple that I'm missing.

EDIT TO ADD:
I've got rid of the runtime error but it is still only picking up invoice 03.

 

[bobbybrown]

One more point to note, if I remove invoice 3 from the folder then it only imports invoice 02. If I remove 2 and 3 from the folder then it imports 1.
It seems to be importing only the invoice with the highest invoice number....

I've made a small addition to your code for the customer name as it was still importing a partial file extension.

I've changed the end of the code to -3 rather than -1 which completely removes the file extension but keeps the customer name.

Here is the code as it is right now...

Sub CompileInvoices()
Dim InvoiceLocation As String, myExt As String, myFile As String
Dim LastInvNo As Integer, ThisFileInvNo As String, 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 = "Path to my file" '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
            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) - 3) 'C - Name
            For Each rng In wkbk.ActiveSheet.Range("B21:B45")
                If Not rng.Value = Empty And Not rng.Value = "Transaction ID" Then
                    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
                End If
            Next rng
            wkshtMaster.Range("G" & intUseRow).Value = wkbk.ActiveSheet.Range("D45").End(xlUp).Value 'G - Trans ID
            If Not wkshtMaster.Range("A" & intUseRow - 1).Value = ThisFileInvNo Then
                wkshtMaster.Range("H" & intUseRow).Value = wkbk.ActiveSheet.Range("J50").Value 'H - Invoice Total
                'wkshtMaster.Range("I" & intUseRow).Value = wkbk.ActiveSheet.Range("J53").Value 'H - Outstanding
            End If

            wkbk.Close
            LastInvNo = ThisFileInvNo
        End If
        'next file
        myFile = Dir
    Loop

End Sub 'Variation? VAT?

 

[George J]

I will give it a try with some dummy data tonight.

When you were running your tests, did you amend the value in G1?
This would have been the last invoice checked so would not have looked at anything preceeding this.

 

[bobbybrown]

No, I don't have a value in G1 after running the code?

 

[George J]

Excel crashed on me when I was making post 11 so i don't even have the workbook to go back and look at.
I thought it covered everything but I will do some tests tonight.
You could try adding after the loop:
wkshtMaster.Range("G1").Value = LastInvNo

I will get back to you tonight and make sure this gets working.