How To Work With Two Open Excel Files Using VBA

It might be troublesome utilizing VBA to work with only a single worksheet, however Excel builders regularly have to entry two or extra information as a part of the identical process. Examples embrace utilizing one file to replace one other or evaluating a number of Excel workbooks to seek out duplicate entries.

This article exhibits you the best way to cope with two information with a number of easy strains of VBA code. Our instance will create a variety in every of the 2 workbooks and allow you to modify between information with out repeatedly activating totally different worksheets.

Opening A Second Excel File

The solely difficulty with opening the second file is defining the trail. We’ll assume the second file is known as file2.xls and is situated in a folder beneath the present listing referred to as “information.”

file2=activeWorkBook.path & "filesfile2.xls"

Workbooks.Open Filename:=file2

Now you’ve got obtained two open workbooks, the present one you are writing the VBA code in, and the brand new one, file2.xls.

Next, you possibly can arrange ranges in every of the workbooks so that you need not change between the 2 information. For this state of affairs our code will assume the info we would like is in column B.

' declare the workbooks and the ranges we're about to arrange.

Dim a As Workbook, b As Workbook

Dim r1 As Range, r2 As Range
' VBA ignores any file identify extensions.

Set a = Workbooks("present")

Set b = Workbooks("file2")
' create the 2 ranges

a.Sheets(M).Activate

Set r1 = Range("a1").CurrentRegion.Columns(B)
b.Sheets(B).Activate

Set r2 = Range("a1").CurrentRegion.Columns(M)

We’ve now received two ranges in separate workbooks our code can reference. As an instance the next code snippet writes to the fast window the values in every of the 2 ranges.

 For Each c In r1.Rows
Debug.Print c
Next
For Each c In r2.Rows
Debug.Print c
Next

Our code now has the pliability to maneuver between the 2 workbooks simply and with out complication. Once any procedures have been accomplished it is a easy matter to shut the second workbook:

Workbooks("file2").Close

Summary

This is an easy instance of utilizing VBA to work with a number of information however the vary object is simply as helpful when utilizing a number of ranges inside a single worksheet. It’s additionally a sensible choice to put in your code library for the subsequent time an identical concern must be addressed.