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.

EXCEL LOGO

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.

  1. 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.
  2. 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 FILE SET UP

inventory balance file example

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

  1. myBillingFile – this will refer to the BILLING.xlsm file
  2. 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.

billing after running macro

inv balance after macro

 

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”/]
email
Click Here to Leave a Comment Below 0 comments
Follow

Get every new post delivered to your Inbox

Join other followers:

%d bloggers like this: