Visual Basic for Excel program to place multiple workbooks worth of invoices into one file for mass data comparison with a particular enterprise resource system of data.

Christopher Clayton

03/01/2015

Sample Visual Basic program for putting multiple Excel invoice workbooks together

WORKBOOK SHEET 1

This is the database of line numbers on different purchase orders in the enterprise resource planning system which are still open. This is the data to compare against the consolidated invoice data. This example is built upon a particular Microsoft Access report structure, so this program should be adapted for particular database and database report structures.

WORKBOOK SHEET 2

This is the output of a program which organizes only the data from sheet 1 that a user will need for invoice analysis, in this case the order # and item # combination, line #, # of open units, price per unit and customer order # if one wishes to include that in an invoice report (e.g. to help track it if a subsidiary or customer only references that order number and not one's local system order number).

WORKBOOK SHEET 3

This is the list of Excel workbook full of invoice data to consolidate. The associated macro only takes data that the user will need for invoice analysis, e.g. the order numbers, item codes, number of units being invoiced for that order number, and invoice number. The weakness in this program is that one must use a vertical look-up Excel formula to fill in the line numbers freom sheet 2.

WORKBOOK SHEET 4

The results of ripping data from sheet 3 via a macro. This example does not include a program to consolidate duplicate order number and item number instances into total #'s of units, the Excel functions need to be used for that or an additional program written.

WORKBOOK SHEET 5

The associated macro takes data from sheet 3 and organizes it all with invoice numbers as a header, and each order number associated with that invoice and all line numbers listed next to it. This is for basic HTML reporting, in case one wants to search for data in an email database about that particular order number and item number, so this is to be included in the email that one sends with the invoices, packing lists and bills of lading, with this invoice report. For best organizational outcomes, the data in sheet 4 should be organized sequentially by instance of invoice number, and secondly by instance of associated order number.

RIP DATA FROM SHEET 1 (DATABASE RESULTS) PROGRAM

 

Sub grabOpenPoData()

	Dim currentOpenPOWorkbookRow As Long
	Dim currentPrintToRow As Long

	currentOpenPOWorkbookRow = 2
	currentPrintToRow = 2

	Do While Sheets(1).Cells(currentOpenPOWorkbookRow, 1) <> 0
   		Sheets(2).Cells(currentPrintToRow, 1) = Sheets(1).Cells(currentOpenPOWorkbookRow, 5) & Sheets(1).Cells(currentOpenPOWorkbookRow, 19)
    		Sheets(2).Cells(currentPrintToRow, 2) = Sheets(1).Cells(currentOpenPOWorkbookRow, 17)
    		Sheets(2).Cells(currentPrintToRow, 3) = Sheets(1).Cells(currentOpenPOWorkbookRow, 22)
    		Sheets(2).Cells(currentPrintToRow, 4) = Sheets(1).Cells(currentOpenPOWorkbookRow, 25)
    		Sheets(2).Cells(currentPrintToRow, 5) = Sheets(1).Cells(currentOpenPOWorkbookRow, 30)
    
    		currentOpenPOWorkbookRow = currentOpenPOWorkbookRow + 1
    		currentPrintToRow = currentPrintToRow + 1
	Loop

End Sub

ORGANIZE INVOICE DATA FROM MULTIPLE WORKBOOKS PROGRAM

Sub Assembler()

	Dim externalWorkbookName As String

	Dim currentAssemblerRow As Integer
	Dim externalWorkbookRow As Integer
	Dim assembledRow As Integer
	Dim externalWorkbookTotal As Integer
	Dim totalRowsPrinted As Integer

	Dim sum As Integer
	Dim totalsRow As Integer

	'Dim dataConsolidationRow As Integer

	'dataConsolidationRow = 2

	currentAssemblerRow = 2
	assembledRow = 2

	totalRowsPrinted = 0

	Do Until Sheets(3).Cells(currentAssemblerRow, 2) = 0
    		externalWorkbookName = Sheets(3).Cells(currentAssemblerRow, 2)
   		currentAssemblerRow = currentAssemblerRow + 1
    
        	externalWorkbookRow = 2

        	Do Until Workbooks(externalWorkbookName).Sheets(1).Cells(externalWorkbookRow, 1) = 0
            		
			Sheets(4).Cells(assembledRow, 1) = Workbooks(externalWorkbookName).Sheets(1).Cells(externalWorkbookRow, 36) ' Print order #
            		Sheets(4).Cells(assembledRow, 5) = Workbooks(externalWorkbookName).Sheets(1).Cells(externalWorkbookRow, 19) ' Print K2 item code
            		Sheets(4).Cells(assembledRow, 10) = Workbooks(externalWorkbookName).Sheets(1).Cells(externalWorkbookRow, 24)
            		Sheets(4).Cells(assembledRow, 8) = Workbooks(externalWorkbookName).Sheets(1).Cells(externalWorkbookRow, 21)
            		Sheets(4).Cells(assembledRow, 4) = Workbooks(externalWorkbookName).Sheets(1).Cells(externalWorkbookRow, 3)
            		Sheets(4).Cells(assembledRow, 7) = Workbooks(externalWorkbookName).Sheets(1).Cells(externalWorkbookRow, 20)
            		Sheets(4).Cells(assembledRow, 11) = Sheets(4).Cells(assembledRow, 1) & Sheets(4).Cells(assembledRow, 5) ' Print order # + K2 item code combination
                        
                        
           	'Do Until Sheets(2).Cells(dataConsolidationRow, 1) = 0
           		 'If Workbooks(externalWorkbookName).Sheets(1).Cells(externalWorkbookRow, 36) & Workbooks(externalWorkbookName).Sheets(1).Cells(externalWorkbookRow, 19) = Sheets(2).Cells(dataConsolidationRow, 1) Then
            				'    Sheets(4).Cells(assembledRow, 14) = Sheets(2).Cells(dataConsolidationRow, 2)
            				'    End If
           		 'Loop
            		'dataConsolidationRow = 2
            
            
            		externalWorkbookTotal = externalWorkbookTotal + Sheets(4).Cells(assembledRow, 8)
            
            		externalWorkbookRow = externalWorkbookRow + 1
            		assembledRow = assembledRow + 1
            		totalRowsPrinted = totalRowsPrinted + 1
        	Loop
        
       		assembledRow = assembledRow - 1
        	Sheets(4).Cells(assembledRow, 9) = externalWorkbookTotal
        	externalWorkbookTotal = 0
        	assembledRow = assembledRow + 1
	Loop

	totalRowsPrinted = totalRowsPrinted + 1

	'Add results

	totalsRow = assembledRow

	Sheets(4).Cells(totalsRow, 7) = "TOTAL"

	assembledRow = 2
	sum = 0

	Do Until assembledRow = totalRowsPrinted + 1
    		Sheets(4).Cells(totalsRow, 8) = Sheets(4).Cells(totalsRow, 8) + Sheets(4).Cells(assembledRow, 8)
    		assembledRow = assembledRow + 1
	Loop

End Sub

PRINT INVOICES AS HEADERS WITH ASSOCIATED ORDER NUMBERS AND LINE NUMBERS BELOW THEM, AS AN HTML-FRIENDLY MINIATURE REPORT

Sub printLinesByInvoiceNumber()

	Dim currentDataFromRow As Integer
	Dim currentDataToRow As Integer
	Dim previousDataFromRow As Integer
	Dim currentInvoiceHeaderRow As Integer

	currentDataFromRow = 2
	currentDataToRow = 2

	dataFromWorkbook = Sheets(1).Cells(2, 1)
	dataToWorkbook = Sheets(1).Cells(2, 2)

	Sheets(5).Cells(currentDataToRow, 1) = Sheets(4).Cells(currentDataFromRow, 4)
	currentInvoiceHeaderRow = currentDataToRow
	currentDataToRow = currentDataToRow + 1
	Sheets(5).Cells(currentDataToRow, 1) = Sheets(4).Cells(currentDataFromRow, 1)
	Sheets(5).Cells(currentDataToRow, 2) = "Lines: " & Sheets(4).Cells(currentDataFromRow, 3)
	previousDataFromRow = currentDataFromRow
	currentDataFromRow = currentDataFromRow + 1

	Do While Sheets(4).Cells(currentDataFromRow, 3) <> 0

		If Sheets(4).Cells(currentDataFromRow, 4) = Sheets(4).Cells(previousDataFromRow, 4) And Sheets(4).Cells(currentDataFromRow, 1) = Sheets(4).Cells(previousDataFromRow, 1) Then
    			Sheets(5).Cells(currentDataToRow, 2) = Sheets(5).Cells(currentDataToRow, 2) & ", " & Sheets(4).Cells(currentDataFromRow, 3)
    			previousDataFromRow = currentDataFromRow
    			currentDataFromRow = currentDataFromRow + 1

		ElseIf Sheets(4).Cells(currentDataFromRow, 4) = Sheets(4).Cells(previousDataFromRow, 4) And Sheets(4).Cells(currentDataFromRow, 1) <> 						Sheets(4).Cells(previousDataFromRow, 1) Then
    			currentDataToRow = currentDataToRow + 1
    			Sheets(5).Cells(currentDataToRow, 1) = Sheets(4).Cells(currentDataFromRow, 1)
    			Sheets(5).Cells(currentDataToRow, 2) = "Lines: " & Sheets(4).Cells(currentDataFromRow, 3)

    			previousDataFromRow = currentDataFromRow
    			currentDataFromRow = currentDataFromRow + 1
		Else
    			Sheets(5).Cells(currentInvoiceHeaderRow, 1) = Sheets(5).Cells(currentInvoiceHeaderRow, 1) & " - " & Sheets(4).Cells(currentDataFromRow - 1, 9) & " " & "units, "
    			currentDataToRow = currentDataToRow + 2
    			currentInvoiceHeaderRow = currentDataToRow
    			Sheets(5).Cells(currentDataToRow, 1) = Sheets(4).Cells(currentDataFromRow, 4)
    			currentDataToRow = currentDataToRow + 1
    			Sheets(5).Cells(currentDataToRow, 1) = Sheets(4).Cells(currentDataFromRow, 1)
    			Sheets(5).Cells(currentDataToRow, 2) = "Lines: " & Sheets(4).Cells(currentDataFromRow, 3)
    			previousDataFromRow = currentDataFromRow
    			currentDataFromRow = currentDataFromRow + 1
		End If

	Loop

End Sub

Back to menu (top)

Website design, writings, photographs and other content copyright (©) 2013 - 2026 Aescreta, D.B.A. Aescreta Research and Design. Personal and research use of these materials is permitted. Please contact us concerning commercial interests.