 |
|
By Sergey Skudaev
Here you find an example of using Visual Basic for Application (VBA) to connect Excell to MySQL. Also you will learn how to create table in MySQL and how to write SQL query.
First you must to install MySQL ODBC driver in PC where MS Excel is installed. It may be the same PC where your web server is running or PC connected to Web server machine. Follow the link to download MySQL ODBC driver:
Use http://dev.mysql.com/downloads/connector/odbc/3.51.html link.
Use http://dev.mysql.com/doc/mysql/en/odbc-connector.html link to find instruction for Installation
Create in MySQL a table from Data Model and Database free tutorial.
create table credit_card(
cardid INT NOT NULL AUTO_INCREMENT,
name varchar(20),
type varchar(20),
expired date,
card_num INT,
credit float,
phone varchar(12),
address varchar(100),
city varchar(20),
state varchar(2),
zip varchar(10),
PRIMARY KEY(cardid)
);
Read One hour MySQL tutorial to learn how to use command prompt to start MySQL server and client, and create database and table
Open command prompt and go to MySQL user interface.
Type: Create database credit; and Press enter. Database is created
Create credit_card table in credit database. Copy script for credit_card table, paste it in notepad and save file as card.sql in C:\mysql\bin directory. Then type source card.sql and press enter. See picture below. Table created.
Fill the credit_card table with data.
Open notepad and paste in it the insert query for three records:
Insert into credit_card (cardid, name,type,expired,card_num,credit,phone,address,city,state,zip) values (0, 'CHASE','Visa','2006-01-01', 123456789, 20000.00 ,'123-123-1234', '12 West 13th Street','New York','NY','12345-4321');
Insert into credit_card (cardid, name,type,expired,card_num,credit,phone,address,city,state,zip) values (0, 'Bank One','MasterCard','2006-01-07', 987654321, 10000.00 ,'123-123-9876', '16 West 17th Street','New York','NY','12345-4321');
Insert into credit_card (cardid, name,type,expired,card_num,credit,phone,address,city,state,zip) values (0, 'Chase Platinum','MasterCard','2006-09-07', 999996789, 15000.00 ,'123-876-9876', '19 West 19th Street','New York','NY','12999-4321');
Save file in MySQL\bin directory as insert.sql.
To insert records type source insert.sql as in example below
Records are inserted in the credit_card table. Note, that in Windows database, table, and field names are not case sensitive. In Unix or Linux they are case sensitive.
Open MS Excel spread sheet. Click Tools, macros, VB editor. VB editor opens.
Click Tools, references and select "Microsoft ActiveX Data Objects 2.8 Library"
Click OK
Open code window and type VB code in General declarations:Public conn As ADODB.Connection
Public rsCard As ADODB.Recordset
Select General drop down list and select Worksheet object. Select Sheet1 in VBA project.
Type VB code that will be executed on Worksheet Activate event. Worksheet activate event occurs when you select a worksheet and it displays on the screen.
Save changes. Open spreadsheet and you will see a table with data from MySQL.
If you have any question or comments please post them in forum
Password Keeper VB Application
Please rate the tutorial