Ever wanted to send information from one workbook to another? After reading this article you will understand how to do so by making use of VBA. Two workbooks are used:
- Database.xlsx (the file that collects all the information from 2.)
- Information_send.xlsm (the user file which contains information that has to be send to a (hidden) database.
You can download the files from my dropbox account.
First of all you start by creating a database excel file. This file will not contain a VBA code. Therefore, the file extension is .xlsx. The database will collect all the information which is send by executing the VBA code in the information_send workbook. In this example we want to send three things to the database: the current date, price and the product. Hence, the top of the database contains those three fields. But for your own file you can create as many fields as needed. You will end up with the following:
Remember the name of your database workbook (“Database.xlsx”) and the location where you saved it (“Desktop”).
The database is set, the only part missing is a workbook with information and a VBA code to send the information to the database. Start by opening a new workbook (in this example: “Information_send”). This workbook will have a user interface and a button which is assigned to the VBA code.
The user interface is a simple one but you can adopt it to your own needs.
The interface consists out of the three fields mentioned in the database. B2 (date), B3 (price), B4 (product) are the cells whose values are important. Those references are needed for the VBA code.
The following code is used to send the values to the database:
Sub Button_export() Dim i As Long Dim wb As Workbook Application.ScreenUpdating = False On Error Resume Next Set wb = Workbooks.Open("T:\Desktop\Database.xlsx") Workbooks("Information_send").Sheets("Information").Activate i = Workbooks("Database").Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row wb.Sheets("Data").Cells(i + 1, 1) = Sheets("Information").Range("B2").Value wb.Sheets("Data").Cells(i + 1, 2) = Sheets("Information").Range("B3").Value wb.Sheets("Data").Cells(i + 1, 3) = Sheets("Information").Range("B4").Value wb.Close Savechanges:=True Application.ScreenUpdating = True End Sub
Breaking down the code:
Dim is a statement which is used to determine the data type of a variable. For instance, workbook is defined as “wb”. I is defined as long. “Long” can be used for variables involving greater numbers than integers. It can store values between -2147483648 and + 2147483648. In stead of “Long” there is an other possibility: “Integer”. But this command makes it only possible to store numbers in a range from -32000 to +32000.
Application.screenupdating is used to turn off (“=False”) screen updating. This will speed up the macro because you will not see what the macro is doing. In the end of the code screen updating is turned back to on (“=True”)
On error resume next will lead to a continue of the following statement directly following a statement which caused the error. This is a very simple way in dealing with errors. A better approach is to make use of an error handler.
Set wb = Workbooks.Open(“T:\Desktop\Database.xlsx“) by this the code knows where to find the database. You should change the path to a location where your database is located.
Workbooks(“Information_send”).Sheets(“Information”).Activate the code activates sheet “Information” of workbook “Information send”.
i = Workbooks(“Database”).Sheets(“Data”).Range(“A” & Rows.Count).End(xlUp).Row You don’t want the code to overwrite older rows with information. Therefore, by using this line, the code will check where the bottom of the last row with information is. The end function starts at the last row.
wb.Sheets(“Data”).Cells(i + 1, 1) = Sheets(“Information”).Range(“B2”).Value the sheet “Data” of workbook “Database”, last cell (row, “i”) + 1, 1 (1 = column A) is the place where the value of B2, worksheet: “Information” will be stored. When the database is empty, the first row starts at row number 2 in order to have empty cells for a description of the field, at the top of the of the database.
wb.Sheets(“Data”).Cells(i + 1, 2) = Sheets(“Information”).Range(“B3”).Value 2 is now column B.
wb.Close Savechanges:=True The database workbook will be closed. The information will be saved.