How To Get the job done With Two Open up Excel Information Utilizing VBA

Posted in WordPress on Jul 17, 2014

It can be challenging making use of VBA to do the job with just a solitary worksheet, but Excel builders frequently have to have to obtain two or extra data files as element of the very same technique. Examples include things like utilizing 1 file to update a further or comparing several Excel workbooks to uncover duplicate entries.

This write-up exhibits you how to deal with two data files with a few uncomplicated strains of VBA code. Our illustration will create a variety in each individual of the two workbooks and enable you to swap involving files devoid of regularly activating different worksheets.

Opening A 2nd Excel File

The only problem with opening the next file is defining the path. We will suppose the 2nd file is named file2.xls and is found in a folder beneath the latest directory called “data files.”

file2=activeWorkBook.route & “filesfile2.xls”
Workbooks.Open Filename:=file2

Now you’ve got acquired two open workbooks, the latest 1 you might be crafting the VBA code in, and the new a single, file2.xls.

Future, you can established up ranges in every of the workbooks so you really don’t need to swap among the two files. For this circumstance our code will believe the details we want is in column one.

‘ declare the workbooks and the ranges we’re about to established up.
Dim a As Workbook, b As Workbook
Dim r1 As Selection, r2 As Range

‘ VBA ignores any file identify extensions.
Set a = Workbooks(“present”)
Set b = Workbooks(“file2”)

‘ create the two ranges
a.Sheets(one).Activate
Set r1 = Range(“a1”).CurrentRegion.Columns(1)

b.Sheets(one).Activate
Established r2 = Selection(“a1”).CurrentRegion.Columns(one)

We have now got two ranges in individual workbooks our code can reference. As an case in point the subsequent code snippet writes to the quick window the values in each individual of the two ranges.

For Each c In r1.Rows

Debug.Print c

Upcoming

For Each c In r2.Rows

Debug.Print c

Upcoming

Our code now has the versatility to move amongst the two workbooks simply and devoid of complication. Once any treatments have been concluded it is a very simple subject to near the next workbook:

Workbooks(“file2”).Close

Summary

This is a basic example of applying VBA to operate with a number of data files but the assortment item is just as beneficial when utilizing many ranges within just a single worksheet. It is really also a very good option to area in your code library for the next time a equivalent problem demands to be addressed.

By Andy L Gibson

Leave a Reply

Your email address will not be published. Required fields are marked *