Put multiple fields from various excel invoices into 1 spreadsheet

[RogPos]

I use Excel (Office 365) to create invoices, and I wanted to see if it's possible to take all invoice files I have and create 1 spreadsheet that has everything listed. Basically I want to consolidate all my customers into 1 list. I believe all my customer data is consistently in the same Column/Row per each invoice, although the Items/Description lines change.

See attached photos below for my invoice example, and the end result I would like to achieve.

[#2]

@RogPos 

That's doable with VBA provided you give us more details about the layout of the Invoice Sheet. Why not upload a sample invoice sheet with some dummy data in it with the same layout?

[#3]

@Subodh_Tiwari_sktneer 

I attached 4 sample invoices that represent my usual invoice layouts.

I tried using Power Query, but I'm not familiar with how to use it properly and the results were listed all over the place, so VBA sounds promising.

Thank you!

[#4]

@RogPos 

You uploaded invoices without any dummy data, never mind.

Where exactly you will enter the Invoice#?

Is it possible for you to upload one invoice with some dummy data only in the fields which are to be fetched into Master Invoice File?

Btw I saw that you have created the Named Ranges on invoice sheet, that's a good idea but at the same time names of the named ranges are not meaningful. You could name them like _Invoice, _CompanyName, _Address etc.

Few queries...

1) When a Master Invoice Workbook is created with details of all the invoices, how would you like to name it? Will it be a new Master Invoice with a datetime stamp in the file name each time you run the code.

2) If the above assumption is not correct, would you always like to have one Master Invoice File and  delete any existing data in previously Existing Master Invoice file and then append data from all the invoices? Or once you run the code, all the invoices which are processed by the code should be moved to another sub-folder (say Consolidated Invoices in the same directory) so that you don't duplicate the data in the Master Invoice File while running the code next time?

3) I assume that all the invoices are saved in the same folder. Can I also assume that names of all the invoice files start with a # sign i.e. #1000_CompanyA-Project1.xls etc.? I am asking this as code needs way to differentiate between an Invoice file and other files if files other than invoice files are also saved in the same folder.

4) Will all the invoice files always have the .xls file extension?

5) Is it okay if the code opens a Folder Picker Dialog Window for you to choose the source folder where all the invoice files are saved? Or it is always a fixed folder? If yes, what's the full path of the source folder?

[#5]

@Subodh_Tiwari_sktneer 

1. You uploaded invoices without any dummy data, never mind.

   All the invoices I uploaded should have dummy data in the "Invoice" worksheet. I just randomly entered values that match how my layout is. Unless I don't understand what you mean by dummy data?

2. Where exactly you will enter the Invoice#?

   I enter it directly in the field next to "Invoice No." I believe the field is named NO.

3. Is it possible for you to upload one invoice with some dummy data only in the fields which are to be fetched into Master Invoice File?

   At bare minimum it would just be the "Customer" info: Name, Address, City, State, Zip, Phone, and the Invoice Number. (I'm not sure if it's possible to put in the Qty, Description, Unit Price since the locations vary with each invoice and they are multiple lines. It would be nice, but looks too complicated.) I'm only using information from the "Invoice" worksheet, not the "customize" one. For example:
   

   Name	Address	City	State	Zip	Phone	Invoice No
   Company A	Address A	City A	State A	Zip A	Phone A	1000
   Company B	Address B	City B	State B	Zip B	Phone B	1001

4. Btw I saw that you have created the Named Ranges on invoice sheet, that's a good idea but at the same time names of the named ranges are not meaningful. You could name them like _Invoice, _CompanyName, _Address etc.

   I actually didn't create the template/worksheets, someone else did a long time ago. But I agree with you, the naming isn't good.

1) When a Master Invoice Workbook is created with details of all the invoices, how would you like to name it? Will it be a new Master Invoice with a datetime stamp in the file name each time you run the code.

Named after the folder that the invoices are in + datetime stamp I was thinking.

2) If the above assumption is not correct, would you always like to have one Master Invoice File and  delete any existing data in previously Existing Master Invoice file and then append data from all the invoices? Or once you run the code, all the invoices which are processed by the code should be moved to another sub-folder (say Consolidated Invoices in the same directory) so that you don't duplicate the data in the Master Invoice File while running the code next time?

I like the idea of moving to a sub-folder so I don't have duplicate data, but I don't know if that will be good in practice, unless it's easy for me to disable those lines of code if needed? But just saving everything with the folder name + datetime stamp is fine.

3) I assume that all the invoices are saved in the same folder. Can I also assume that names of all the invoice files start with a # sign i.e. #1000_CompanyA-Project1.xls etc.? I am asking this as code needs way to differentiate between an Invoice file and other files if files other than invoice files are also saved in the same folder.

They are all in the same main folder(s) with some having subfolders.

They all start that same way: # followed by invoice number and underscore.

4) Will all the invoice files always have the .xls file extension?

xls or xlsx

5) Is it okay if the code opens a Folder Picker Dialog Window for you to choose the source folder where all the invoice files are saved? Or it is always a fixed folder? If yes, what's the full path of the source folder?

Folder Picker Dialog Window is fine since I have different folders I need to run it in.

[#6 · Accepted solution]

@RogPos 

Please find the attached with a code called "CombineAllInvoices" on m_CombineAllInvoices Module.

The file has a hidden sheet called Template in it with the headers only.

You will also find a button called "Combine All Invoices" on Main Sheet and you may click this button to run the code.

Here is the code....

Sub CombineAllInvoices()
Dim xlApp                   As Application
Dim wbMaster                As Workbook
Dim wbInvoice               As Workbook
Dim wsMaster                As Worksheet
Dim wsInvoice               As Worksheet
Dim strSourceFolderPath     As String
Dim strNewFolderPath        As String
Dim strNewFileName          As String
Dim strMoveFolderName       As String
Dim fso                     As Object
Dim SourceFolder            As Object
Dim Invoice                 As Object
Dim rngQty                  As Range
Dim rngTotal                As Range
Dim lr                      As Long
Dim dlr                     As Long
Dim i                       As Long
Dim Dicsount                As Double

Application.ScreenUpdating = False
Application.DisplayAlerts = False

With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select Invoice Folder!"
    .AllowMultiSelect = False
    If .Show <> -1 Then
        MsgBox "You didn't select any Invoice Folder.", vbExclamation
        Exit Sub
    Else
        strSourceFolderPath = .SelectedItems(1)
    End If
End With

Set fso = CreateObject("Scripting.FileSystemObject")
Set SourceFolder = fso.GetFolder(strSourceFolderPath)

'Invoice Files processed by the code will be transferred to the following sub-folder in the chosen source folder
strMoveFolderName = "Invoiced Moved"
strMoveFolderName = strMoveFolderName & " " & Format(Now, "dd-mmm-yy hhmmss")
strMoveFolderName = strSourceFolderPath & "\" & strMoveFolderName

'Creating folder to move already processed invoice files
fso.CreateFolder (strMoveFolderName)

With ThisWorkbook.Worksheets("Template")
    .Visible = xlSheetVisible
    .Copy
    .Visible = xlSheetVeryHidden
End With

Set wbMaster = ActiveWorkbook
Set wsMaster = wbMaster.Worksheets(1)
wsMaster.Name = "All Invoices"

On Error GoTo Skip
Set xlApp = New Application

For Each Invoice In SourceFolder.Files
    If LCase(fso.GetExtensionName(Invoice)) = "xls" Or LCase(fso.GetExtensionName(Invoice)) = "xlsx" Then
        If Left(Invoice.Name, 1) = "#" Then
            Set wbInvoice = xlApp.Workbooks.Open(Invoice)
            Set wsInvoice = wbInvoice.Worksheets("Invoice")
            
            Set rngQty = wsInvoice.Range("D:D").Find(what:="Qty", lookat:=xlWhole)
            
            If Not rngQty Is Nothing Then
                Set rngTotal = wsInvoice.Range("K:K").Find(what:="TOTAL*", lookat:=xlWhole, MatchCase:=False)
                lr = rngTotal.Row - 5
                For i = rngQty.Row + 1 To lr
                    If wsInvoice.Cells(i, 4) <> "" And wsInvoice.Cells(i, 5) <> "Discount" Then
                        dlr = wsMaster.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
                        
                        'Invoice details
                        wsMaster.Range("A" & dlr).Value = wsInvoice.Range("data5").Value
                        wsMaster.Range("B" & dlr).Value = wsInvoice.Range("data6").Value
                        wsMaster.Range("C" & dlr).Value = wsInvoice.Range("data7").Value
                        wsMaster.Range("D" & dlr).Value = wsInvoice.Range("data8").Value
                        wsMaster.Range("E" & dlr).Value = wsInvoice.Range("data9").Value
                        wsMaster.Range("F" & dlr).Value = wsInvoice.Range("data10").Value
                        wsMaster.Range("G" & dlr).Value = wsInvoice.Range("data1").Value
                        wsMaster.Range("H" & dlr).Value = wsInvoice.Range("NO").Value
                        wsMaster.Range("I" & dlr).Value = wsInvoice.Range("data2").Value
                        
                        'Order details
                        wsMaster.Range("J" & dlr).Value = wsInvoice.Range("E" & i & ":J" & i).Value
                        wsMaster.Range("K" & dlr).Value = wsInvoice.Cells(i, 4).Value
                        wsMaster.Range("L" & dlr).Value = wsInvoice.Cells(i, 11).Value
                        wsMaster.Range("M" & dlr).Value = wsInvoice.Cells(i, 12).Value
                    End If
                    
                    If wsInvoice.Cells(i, 4) <> "" And wsInvoice.Cells(i, 5) = "Discount" Then
                        wsMaster.Range("N" & dlr).Value = wsInvoice.Cells(i, 11).Value
                        wsMaster.Range("O" & dlr).Value = wsInvoice.Range("TOT").Value
                    End If
                Next i
            End If
            
            wbInvoice.Close False
            fso.MoveFile Invoice, strMoveFolderName & "\" & Invoice.Name
        End If
    End If
Next Invoice

'The Master Invoie File will be saved in this folder
strNewFolderPath = strSourceFolderPath & "\" & Format(Now, "dd-mmm-yy hhmmss")

'Name of the Master File
strNewFileName = "Master Invoice.xlsx"

fso.CreateFolder strNewFolderPath
With wsMaster.Range("A1").CurrentRegion
    .Borders.Color = vbBlack
    .Columns.AutoFit
End With

wbMaster.SaveAs strNewFolderPath & "\" & strNewFileName, 51
wbMaster.Close True
MsgBox "Master File has been saved successfully..." & vbNewLine & vbNewLine & _
        strNewFolderPath & "\" & strNewFileName, vbInformation
        
Skip:
xlApp.Quit
Set xlApp = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

[#7]

@Subodh_Tiwari_sktneer 

This works perfectly! Thank you so much.

I'm still going over all the code to learn what it does but I have been able to adapt parts of it further for what I need done. The different "If Then statements" are making me think and in what order I need to add some extra things, but so far so good! I just need to familiarize myself with it more.

Thank you again for your help!

[#8]

@RogPos  If all invoice files are structured identically, you can also collect all the data with Power Query without writing a single line of VBA.

[#9]

@Ingeborg Hawighorst 

I actually wanted to use Power Query originally and tried to figure it out, but I didn't understand how to get it to put the data into the fields I wanted.

This was as far as I could figure out (see attached). It correctly opened up my sample files but put the info all over the place, and I don't know how to correctly order it into how I wanted it in my above post's example.

[#10]

@RogPos 

You're welcome! Glad it worked as desired.

Thanks for the feedback!