Excel VBA : Working With Multiple Files
During the last few posts we have been building on our knowledge of Excel VBA Macros. This time we will use what we have already seen, but now we are going to work with more than one file.
The Goal
From this post, we will create a Billing file with a macro.
This macro will update our inventory balance by reducing the quantity on hand by the amount being sold. There are easier and better ways to do this, this is just an example for demonstration purposes.
Creating Our Files
Files Needed
Before beginning, create 2 files.
- The first called “BILLING.xlsm” – make sure this is a .xlsm macro enabled file. This is the file that we will be creating the macro in. The file should be set up like the first screenshot below.
- The second file should be called “Item Master” and should look like the second screenshot below. This file will contain our items, unit cost, number left in stock and value of our stock. It also has two columns that we are going to hide later.
Billing Data Validation
On our Billing file, from cell A2 down to A11, add a Data Validation listing allowing only the 10 items in our list. This then creates a drop-down list in cells A2 to A11 allowing us to select the item being billed.
Billing Formulas
In cells C2 to C11, enter a VLOOKUP formula which will retrieve the unit cost of the item in column A from the Inventory Balance file.
Amend this formula to make it an IFERROR VLOOKUP formula, and if there is an error, leave the cell blank (two double apostrophes does this).
The formula in C2 is =IFERROR(VLOOKUP(A2,'[Inventory Balance.xlsx]Sheet1′!$A:$B,2,0),””)
In cells D2 to D11, add a formula multiplying the quantity in B by the unit cost in C.
Amend this formula to make it an IFERROR and if the multiplication returns an error, leave the cell blank.
The formula in D2 is =IFERROR(B2*C2,””)
Inventory Balance Formulas
On the Inventory Balance file, we are going to create 2 formulas.
From E2 to E11, enter a VLOOKUP looking up the item in column A and retrieving the quantity from the BILLING file.
Amend the formula to add an IFERROR condition – if the VLOOKUP returns an error, then we want 0 (zero).
The formula in E2 is =IFERROR(VLOOKUP(A2,[BILLING.xlsm]Sheet1!$A:$B,2,0),0)
In cells F2 to F11, enter a formula deducting the quantities in column C from the quantities in column E.
The formula in F2 is=C2-E2 .
Inventory Balance Hidden Columns
In the Inventory Balance file, hide columns E and F
Inventory Balance File
Save and close this file before continuing.
Creating Our Macro
In our example, the macro will be created in a module added to the Billing file. If you are uncertain how to do this, start reading the posts from here.
In my case, I am going to call the macro “billingExample”.
Variables
We will need 2 variables
- myBillingFile – this will refer to the BILLING.xlsm file
- myInvBalance – this will refer to the Inventory Balance file
Why are we going to use variables?
Up to now, we have been working on one file, now we are going to work with two files, we need a way to tell Excel what file it should be looking at at any given time. The variables will allow us to do this.
Also, by using variables, if we wanted to recycle this macro in the future we only need to change the file details in the variables once, rather than having to change the filenames everytime they appear in the code.
Change the filepaths to match your files.
Dim myBillingFile As String
Dim myInvBalance As String
myBillingFile = “BILLING.xlsm”
myInvBalance = “C:\Users\JayTray\Documents\sitework\Inventory Balance.xlsx”
Open The Inventory Balance File
This line of code will open the Inventory Balance file (using our variable).
Workbooks.Open Filename:=myInvBalance
Unhide The Hidden Columns
To unhide columns E and F in our Inventory Balance file, we will highlight columns D and G and unhide everything in between.
Columns(“D:G”).Select
Selection.EntireColumn.Hidden = False
Copy The New Balances Over The Old Balances
We will select the quantities in cells F2 to F11 and use paste special values to paste them in the C2 to C11 range. This effectively takes the inventory balance after our billing, and makes that our new inventory balance in column C.
Range(“F2:F11”).Select
Selection.Copy
Range(“C2:C11”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Hide The Hidden Columns
Now we will hide columns E and F in our Inventory Balance file again.
Columns(“E:F”).Select
Selection.EntireColumn.Hidden = True
Save & Close The Inventory Balance File
Now that we have updated our inventory balances, we will want to save the file before closing it.
ActiveWorkbook.Save
ActiveWindow.Close
Back To The Billing File
We will put focus back on our billing file, add a button around column F/G.
Assign the macro to this button.
Windows(myBillingFile).Activate
Add A Button
On the Billings file, add a button around column F/G.
Assign the macro to this button.
Test The Macro
Select some items in column A, enter a quantity in column B and press the button to run the macro.
When you have done this, open the Inventory Balance file and you should see that the balance has been reduced by the amount you have billed.
Using Formula Instead of VBA?
We set up our files with different formulas set in them.
You may be wondering why not be a bit fancier and create the macros only when needed through VBA?
This can be done, and can be done easily, but if you (or your end user) are more comfortable with formulas then why not use them? The less you complicate things the better for yourself and end-user the better!
Other Improvements
This is an extremely basic example of working with more than one files. There are any number of improvements that can be made to it – like adding message boxes if you are trying to bill more than the quantity available for example.
Are there any other improvements you can think of? I challenge you to try making those improvements yourself.
[grwebform url=”http://app.getresponse.com/view_webform.js?wid=2766903&u=CucD” css=”on”/]
