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]

Not really got time to look at this as majorly busy at work. Will try to do this piecemeal to see if we can spot where it is going wrong.

Before the line
If LastInvNo < ThisFileInvNo Then
Put
debug.print "last invoice no. " & lastinvno & " less than This file invoice no. " & Thisfileinvno & LastInvNo < ThisFileInvNo

Hopefully this will show us if it is a problem with the numbering of the invoices not being in order.
If this part is okay, let me know and we will look at the next section.

To see the debug results, make sure in the VB Editor you have View Immediate window activated.

 

[bobbybrown]

Hi George,
Sadly when adding that line I get a runtime error 13 for a type mismatch

 

[George J]

Try removing from that line & LastInvNo < ThisFileInvNo
I am doing this on the fly but thought that would just show a true or false value.

When you get the results check if the first number is less that the second. If it isn't then it may not be looking at the invoices in the assumed sequential order which we will need to fix.

 

[bobbybrown]

That got it, it ran through the macro this time and opened the invoices.
Runtime error 5 at the end but nothing printed in cell G1 where the last invoice number should be and nothing else printed anywhere as a result of the debug.print line, I'm checking on the master sheet for the result of the debug, thats correct yes? If so it only has the invoice records starting with 1 and going down a row at a time, in sequential order minus the missing invoice records.
It seems as though the runtime error is stopping the code before it fills cell G1

 

[bobbybrown]

If I manually add a number in to G1 then the code carries on from that number and no runtime error occurs, but I still can't see anything printed as a result of the debug.

 

[George J]

The results of the debug.print line should be seen in the VB Editor where your code is.
At the bottom of the Editor, is there a window named 'immediate'? If not, click View and then immediate

You can use this windows to test code and values while stepping through code as well (e.g. ?lastinvno )

 

[bobbybrown]

Thankyou.
This is the result of the test:

last invoice no. 0 less than This file invoice no. 10
last invoice no. 1 less than This file invoice no. 21
last invoice no. 2 less than This file invoice no. 32
last invoice no. 3 less than This file invoice no. 43
last invoice no. 4 less than This file invoice no. 54
last invoice no. 5 less than This file invoice no. 65
last invoice no. 6 less than This file invoice no. 76
last invoice no. 7 less than This file invoice no. 87
last invoice no. 8 less than This file invoice no. 98
last invoice no. 9 less than This file invoice no. 109
last invoice no. 10 less than This file invoice no. 10010
last invoice no. 100 less than This file invoice no. 101100
last invoice no. 101 less than This file invoice no. 102101
last invoice no. 102 less than This file invoice no. 103102
last invoice no. 103 less than This file invoice no. 104103
last invoice no. 104 less than This file invoice no. 105104
last invoice no. 105 less than This file invoice no. 106105
last invoice no. 106 less than This file invoice no. 107106
last invoice no. 107 less than This file invoice no. 108107
last invoice no. 108 less than This file invoice no. 109108
last invoice no. 109 less than This file invoice no. 11109
last invoice no. 109 less than This file invoice no. 110109
last invoice no. 110 less than This file invoice no. 111110
last invoice no. 111 less than This file invoice no. 112111
last invoice no. 112 less than This file invoice no. 113112
last invoice no. 113 less than This file invoice no. 114113
last invoice no. 114 less than This file invoice no. 115114
last invoice no. 115 less than This file invoice no. 116115
last invoice no. 116 less than This file invoice no. 117116
last invoice no. 117 less than This file invoice no. 118117
last invoice no. 118 less than This file invoice no. 119118
last invoice no. 119 less than This file invoice no. 12119
last invoice no. 119 less than This file invoice no. 120119
last invoice no. 120 less than This file invoice no. 121120
last invoice no. 121 less than This file invoice no. 122121
last invoice no. 122 less than This file invoice no. 123122
last invoice no. 123 less than This file invoice no. 124123
last invoice no. 124 less than This file invoice no. 125124
last invoice no. 125 less than This file invoice no. 126125
last invoice no. 126 less than This file invoice no. 127126
last invoice no. 127 less than This file invoice no. 128127
last invoice no. 128 less than This file invoice no. 129128
last invoice no. 129 less than This file invoice no. 13129
last invoice no. 129 less than This file invoice no. 130129
last invoice no. 130 less than This file invoice no. 131130
last invoice no. 131 less than This file invoice no. 132131
last invoice no. 132 less than This file invoice no. 133132
last invoice no. 133 less than This file invoice no. 134133
last invoice no. 134 less than This file invoice no. 135134
last invoice no. 135 less than This file invoice no. 136135
last invoice no. 136 less than This file invoice no. 137136
last invoice no. 137 less than This file invoice no. 138137
last invoice no. 138 less than This file invoice no. 139138
last invoice no. 139 less than This file invoice no. 14139
last invoice no. 139 less than This file invoice no. 140139
last invoice no. 140 less than This file invoice no. 141140
last invoice no. 141 less than This file invoice no. 142141
last invoice no. 142 less than This file invoice no. 143142
last invoice no. 143 less than This file invoice no. 144143
last invoice no. 144 less than This file invoice no. 145144
last invoice no. 145 less than This file invoice no. 146145
last invoice no. 146 less than This file invoice no. 147146
last invoice no. 147 less than This file invoice no. 15147
last invoice no. 147 less than This file invoice no. 154147
last invoice no. 154 less than This file invoice no. 155154
last invoice no. 155 less than This file invoice no. 156155
last invoice no. 156 less than This file invoice no. 157156
last invoice no. 157 less than This file invoice no. 158157
last invoice no. 158 less than This file invoice no. 159158
last invoice no. 159 less than This file invoice no. 16159
last invoice no. 159 less than This file invoice no. 160159
last invoice no. 160 less than This file invoice no. 161160
last invoice no. 161 less than This file invoice no. 162161
last invoice no. 162 less than This file invoice no. 163162
last invoice no. 163 less than This file invoice no. 164163
last invoice no. 164 less than This file invoice no. 165164
last invoice no. 165 less than This file invoice no. 166165
last invoice no. 166 less than This file invoice no. 167166
last invoice no. 167 less than This file invoice no. 168167
last invoice no. 168 less than This file invoice no. 169168
last invoice no. 169 less than This file invoice no. 17169
last invoice no. 169 less than This file invoice no. 170169
last invoice no. 170 less than This file invoice no. 171170
last invoice no. 171 less than This file invoice no. 172171
last invoice no. 172 less than This file invoice no. 173172
last invoice no. 173 less than This file invoice no. 174173
last invoice no. 174 less than This file invoice no. 175174
last invoice no. 175 less than This file invoice no. 176175
last invoice no. 176 less than This file invoice no. 177176
last invoice no. 177 less than This file invoice no. 178177
last invoice no. 178 less than This file invoice no. 179178
last invoice no. 179 less than This file invoice no. 18179
last invoice no. 179 less than This file invoice no. 180179
last invoice no. 180 less than This file invoice no. 181180
last invoice no. 181 less than This file invoice no. 182181
last invoice no. 182 less than This file invoice no. 183182
last invoice no. 183 less than This file invoice no. 19183
last invoice no. 183 less than This file invoice no. 20183
last invoice no. 183 less than This file invoice no. 21183
last invoice no. 183 less than This file invoice no. 22183
last invoice no. 183 less than This file invoice no. 23183
last invoice no. 183 less than This file invoice no. 24183
last invoice no. 183 less than This file invoice no. 25183
last invoice no. 183 less than This file invoice no. 26183
last invoice no. 183 less than This file invoice no. 27183
last invoice no. 183 less than This file invoice no. 28183
last invoice no. 183 less than This file invoice no. 29183
last invoice no. 183 less than This file invoice no. 30183
last invoice no. 183 less than This file invoice no. 31183
last invoice no. 183 less than This file invoice no. 32183
last invoice no. 183 less than This file invoice no. 33183
last invoice no. 183 less than This file invoice no. 34183
last invoice no. 183 less than This file invoice no. 35183
last invoice no. 183 less than This file invoice no. 36183
last invoice no. 183 less than This file invoice no. 37183
last invoice no. 183 less than This file invoice no. 38183
last invoice no. 183 less than This file invoice no. 39183
last invoice no. 183 less than This file invoice no. 40183
last invoice no. 183 less than This file invoice no. 41183
last invoice no. 183 less than This file invoice no. 42183
last invoice no. 183 less than This file invoice no. 43183
last invoice no. 183 less than This file invoice no. 44183
last invoice no. 183 less than This file invoice no. 45183
last invoice no. 183 less than This file invoice no. 46183
last invoice no. 183 less than This file invoice no. 47183
last invoice no. 183 less than This file invoice no. 48183
last invoice no. 183 less than This file invoice no. 49183
last invoice no. 183 less than This file invoice no. 50183
last invoice no. 183 less than This file invoice no. 51183
last invoice no. 183 less than This file invoice no. 52183
last invoice no. 183 less than This file invoice no. 53183
last invoice no. 183 less than This file invoice no. 54183
last invoice no. 183 less than This file invoice no. 55183
last invoice no. 183 less than This file invoice no. 56183
last invoice no. 183 less than This file invoice no. 57183
last invoice no. 183 less than This file invoice no. 58183
last invoice no. 183 less than This file invoice no. 59183
last invoice no. 183 less than This file invoice no. 60183
last invoice no. 183 less than This file invoice no. 61183
last invoice no. 183 less than This file invoice no. 62183
last invoice no. 183 less than This file invoice no. 63183
last invoice no. 183 less than This file invoice no. 64183
last invoice no. 183 less than This file invoice no. 65183
last invoice no. 183 less than This file invoice no. 66183
last invoice no. 183 less than This file invoice no. 67183
last invoice no. 183 less than This file invoice no. 68183
last invoice no. 183 less than This file invoice no. 69183
last invoice no. 183 less than This file invoice no. 70183
last invoice no. 183 less than This file invoice no. 71183
last invoice no. 183 less than This file invoice no. 72183
last invoice no. 183 less than This file invoice no. 73183
last invoice no. 183 less than This file invoice no. 74183
last invoice no. 183 less than This file invoice no. 75183
last invoice no. 183 less than This file invoice no. 76183
last invoice no. 183 less than This file invoice no. 77183
last invoice no. 183 less than This file invoice no. 78183
last invoice no. 183 less than This file invoice no. 79183
last invoice no. 183 less than This file invoice no. 80183
last invoice no. 183 less than This file invoice no. 81183
last invoice no. 183 less than This file invoice no. 82183
last invoice no. 183 less than This file invoice no. 83183
last invoice no. 183 less than This file invoice no. 84183
last invoice no. 183 less than This file invoice no. 85183
last invoice no. 183 less than This file invoice no. 86183
last invoice no. 183 less than This file invoice no. 87183
last invoice no. 183 less than This file invoice no. 88183
last invoice no. 183 less than This file invoice no. 89183
last invoice no. 183 less than This file invoice no. 90183
last invoice no. 183 less than This file invoice no. 91183
last invoice no. 183 less than This file invoice no. 92183
last invoice no. 183 less than This file invoice no. 93183
last invoice no. 183 less than This file invoice no. 94183
last invoice no. 183 less than This file invoice no. 95183
last invoice no. 183 less than This file invoice no. 96183
last invoice no. 183 less than This file invoice no. 97183
last invoice no. 183 less than This file invoice no. 98183
last invoice no. 183 less than This file invoice no. 99183

It seems its checking the final invoice 183 against all the ones it misses, 11 to 99.
Also not sure why it jumps to 6 digit numbers, there are 183 invoices in the folder.

 

[George J]

Can you post the code you are using?

 

[bobbybrown]

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:\Users\Becky and Dan\Desktop\Testing" & "\" 'note the \ at the end is required
    myExt = "*.xlsx*"
    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)
    Debug.Print "last invoice no. " & LastInvNo & " less than This file invoice no. " & ThisFileInvNo & LastInvNo
        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]

The long invoice numbers are because I left the & LastInvNo in the debug code. Just removed that and its giving better readings now.
For example:
last invoice no. 0 less than This file invoice no. 1
last invoice no. 1 less than This file invoice no. 2
last invoice no. 2 less than This file invoice no. 3
last invoice no. 3 less than This file invoice no. 4
last invoice no. 4 less than This file invoice no. 5
last invoice no. 5 less than This file invoice no. 6
last invoice no. 6 less than This file invoice no. 7
last invoice no. 7 less than This file invoice no. 8
last invoice no. 8 less than This file invoice no. 9
last invoice no. 9 less than This file invoice no. 10
last invoice no. 10 less than This file invoice no. 100
last invoice no. 100 less than This file invoice no. 101
last invoice no. 101 less than This file invoice no. 102
last invoice no. 102 less than This file invoice no. 103
last invoice no. 103 less than This file invoice no. 104
last invoice no. 104 less than This file invoice no. 105
last invoice no. 105 less than This file invoice no. 106
last invoice no. 106 less than This file invoice no. 107
last invoice no. 107 less than This file invoice no. 108
last invoice no. 108 less than This file invoice no. 109