Send information to Excel database

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:

  1. Database.xlsx (the file that collects all the information from 2.)
  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.

Database

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:

Schermafbeelding 2017-01-06 om 20.59.21.png

Remember the name of your database workbook (“Database.xlsx”) and the location where you saved it (“Desktop”).

User interface 

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.

Schermafbeelding 2017-01-06 om 21.09.32.png

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.

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.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s