Excel as a Data Source

How to Connect and Get Data from Excel Using Macro

Often we receive several Excel files containing some data and we have to collapse them all in just one Excel file to produce some output.

If we speak about a huge amount of data then it looks very complex and like a lot of work if we are linking them all. But if we write some easy VBA code we can get it done in a very short time.

Note: this guide doesn't cover the basic VBA syntax.

First of all, to be able to access the VBA commands to connect to an Excel file we need to include to our project the follow reference: Microsofr Excel 11.0 Object Library. Once we have this reference included we can start working with other Excel files.

Object Declaration

The first thing to do in order to be able to connect to an other Excel file is to create some object to contain the different parts composing such kind of files.

Therefore we have the following kind of declaration needed:

- Dim WExcel as Excel.Application

^{This declaration creates a Excel application object that will permit to open evey kind of workbook}

- Dim WBook as Workbook

^{This declaration creates a Workbook object that will permit to open a specific workbook}

- Dim WSheet as Worksheet

^{This declaration creates a Worksheet object that will permit to open a specific worksheet of an open workbook}

Opening an Existing Excel File

Once we declared all the needed objects we just need to assign to every of these the correct value to be able to manage data contained in the file.

- Set WExcel = New Excel.Application

^{This instruction creates a new Excel Application environment, in this we can open our files}

- Set WBook = WExcel.Workbooks.Open(link)

^{This instruction opens an existing Workbook, in other words open an existing file inside the Excel environment already created}

- Set WSheet = WBook.ActiveSheet

Or

- Set WSheet =

Wbook.Sheets(ws_number).Select

^{This instruction opens a specific worksheet of the opened file. With the first instruction it opens the active worksheet and with the second a specified worksheet}

Create a New Excel File