Invoicing master sheet from others - Page 5

Thanks Thanks:  0
Likes Likes:  0
Page 5 of 5 FirstFirst ... 345
Results 41 to 46 of 46

Thread: Invoicing master sheet from others

  1. #41
    Board Regular George J's Avatar
    Join Date
    Feb 2002
    Location
    Edinburgh, Bonnie Scotland
    Posts
    950
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Invoicing master sheet from others

     
    do invoices 11-99 appear in this new list?
    George J

  2. #42
    Board Regular
    Join Date
    Apr 2015
    Posts
    109
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Invoicing master sheet from others

    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 by bobbybrown; Nov 13th, 2017 at 09:05 AM.

  3. #43
    Board Regular
    Join Date
    Apr 2015
    Posts
    109
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Invoicing master sheet from others

    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

  4. #44
    Board Regular George J's Avatar
    Join Date
    Feb 2002
    Location
    Edinburgh, Bonnie Scotland
    Posts
    950
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Invoicing master sheet from others

    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.
    George J

  5. #45
    Board Regular George J's Avatar
    Join Date
    Feb 2002
    Location
    Edinburgh, Bonnie Scotland
    Posts
    950
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Invoicing master sheet from others

    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 by George J; Jan 9th, 2018 at 09:10 PM.
    George J

  6. #46
    Board Regular
    Join Date
    Apr 2015
    Posts
    109
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Invoicing master sheet from others

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com