VBA Excel programming three-day discussion (3)

VBA Excel programming three-day discussion (1)

VBA Excel programming three-day discussion (2)

VBA Excel programming three-day discussion (3)

This article explains how to connect VBA to the database to read and update data, and how to create a perspective Using VBA.

VBA connects to the database to read and update data

With the datasource provided by the Windows system, you can connect to various databases and perform crud operations. you can view and add/modify datasource through the control panel> Administrative Tools> datasource (ODBC. the following example shows how to add a datasource to Oracle, provided that the Oracle client is installed locally.

Note that the TNS service name must be the name of a connection defined in % oracle_client_home %/Network/admin/tnsnames. ora.

You can also add datasource to an Access database file. for example, we have. MDB's access database file, which has several classic tables: Student table student (ID, name, gender), teacher table teacher (ID, name), Course table course (ID, name, t_id), student course detail table SC (s_id, c_id, score ). it is located in C:/1/1. MDB. add the data source as shown in:

Because oracle or sqlserver database may not be installed, the following example uses the data source school as an example. because ADODB is used, you must first introduce the dependent libraries, tools-> references, and select the dependent libraries:

The following program demonstrates how to connect to the database to obtain data and update data:

Sub getstudentinfo () <br/> dim RS as ADODB. recordset <br/> dim conn as ADODB. connection <br/> dim I as integer <br/> dim connectionstring as variant <br/> dim maxrow & <br/> set conn = new ADODB. connection <br/> set rs = new ADODB. recordset <br/> set xlws = activeworkbook. worksheets ("sheet1") <br/> xlws. select <br/> maxrow = [a65536]. end (xlup ). row <br/> 'dns specifies the name of the data source <br/> connectionstring = "DSN = school; uid =; Pwd =" <br/> Conn. open connectionstring <br/> 'First clears sheet <br/> xlws. range ("A1: F" & maxrow + 1 ). clearcontents <br/> Rs. open "select. name,. gender, B. name, C. name, D. score from student A, course B, teacher C, SC d Where B. t_id = C. ID and. id = D. s_id and B. id = D. c_id ", Conn, adopenstatic, adlockreadonly <br/> 'Copy result set to sheet, starting from the specified range <br/> xlws. range ("A1 "). copyfromrecordset RS <br/> Rs. close <br/> set rs = nothing <br/> Conn. close <br/> set conn = nothing <br/> exit sub <br/> errhandler: <br/> 'just exit <br/> set connn = nothing <br/> msgbox "problems with database connection ", vbokonly <br/> end sub </P> <p> 'process the resultset row by row and filter out male students. <br/> sub getstudentinfo1 () <br/> dim RS as ADODB. recordset <br/> dim conn as ADODB. connection <br/> dim I as integer <br/> dim connectionstring as variant <br/> dim maxrow & <br/> set conn = new ADODB. connection <br/> set rs = new ADODB. recordset <br/> set xlws = activeworkbook. worksheets ("sheet1") <br/> xlws. select <br/> maxrow = [a65536]. end (xlup ). row <br/> connectionstring = "DSN = school; uid =; Pwd =" <br/> Conn. open connectionstring </P> <p> xlws. range ("A1: F" & maxrow ). clearcontents <br/> msgbox 1 <br/> Rs. open "select. name,. gender, B. name, C. name, D. score from student A, course B, teacher C, SC d Where B. t_id = C. ID and. id = D. s_id and B. id = D. c_id ", Conn, adopenstatic, adlockreadonly <br/> 'process the record set row by row <br/> do while not Rs. EOF <br/> If RS. fields (1 ). value = "male" then <br/> xlws. cells (maxrow + 1, 1 ). value = Rs. fields (0 ). value <br/> xlws. cells (maxrow + 1, 2 ). value = Rs. fields (1 ). value <br/> xlws. cells (maxrow + 1, 3 ). value = Rs. fields (2 ). value <br/> xlws. cells (maxrow + 1, 4 ). value = Rs. fields (3 ). value <br/> xlws. cells (maxrow + 1, 5 ). value = Rs. fields (4 ). value <br/> Rs. movenext <br/> maxrow = maxrow + 1 <br/> else <br/> Rs. movenext <br/> end if <br/> loop <br/> 'Update record <br/> Conn. execute "insert into student (name, gender) values ('hahahaha', 'male')" <br/> 'conn. execute "delete from student where id = 2" <br/> Rs. close <br/> set rs = nothing <br/> Conn. close <br/> set conn = nothing <br/> exit sub <br/> errhandler: <br/> 'just exit <br/> set connn = nothing <br/> msgbox "problems with database connection", vbokonly <br/> end sub <br/>

This example and database file can be downloaded here: adodb.zip.

Manually create a pivot table

Why do we need a pivot table? The results produced by the above program are used as an example. It is an ordinary result set that only objectively shows the students' course scores.

However, the problem can be better reflected if it is displayed as follows:

Some people may like this:

That is to say, for the same data, different people are based on different starting points and want to look at it from different angles. Such a requirement Pivot table can be achieved.

First, we will demonstrate the student course Score Table generated by the above program as the base to generate a pivot table, First insert a row in the first row, add the header: Name gender course instructor score. then switch the menu to insert-> export tTable (office2003 in data-> export tTable and analyze tchart report ...):

Step 1: select the data of the perspective you want to create, and step 2 select the position in the sheet where the perspective is placed.

Then try to drag and drop the corresponding data columns to different positions as you imagine, and try to set the display style of each field, subtotal, and so on. the Pivot table shown above can be created.

Procedure creation perspective

The following program demonstrates how to use VBA to create a sequence. I have commented out all the notes.

Sub createtwopivot () <br/> createpivottable <br/> createpivottable1 <br/> end sub </P> <p> sub createpivottable () <br/> dim connectionstring as string <br/> 'Connection string <br/> connectionstring = "ODBC; DSN = school; uid =; Pwd =; "<br/> activeworkbook. worksheets ("sheet2 "). select <br/> 'first clear the sheet data <br/> cells. select <br/> selection. clearcontents <br/> 'create External tables using external data <br/> with activeworkbook. using tcaches. add (sourcet Ype: = xlexternal) <br/>. connection = connectionstring <br/>. commandtype = xl1_ SQL <br/>. commandtext = array (_ <br/> "select. name as sname,. gender as gender, B. name as cname, C. name as tname, D. score as score from student A, course B, teacher C, SC d Where B. t_id = C. ID and. id = D. s_id and B. id = D. c_id "_ <br/>) <br/>. createpivottable tabledestination: = activesheet. cells (1, 1), tablename: = "pi Vottable2 "<br/> end with <br/> 'statement required <br/> activesheet. cells (1, 1 ). select <br/> activesheet. export ttablewizard tabledestination: = activesheet. cells (2, 1) <br/> 'put the sname column in rowfield <br/> with activesheet. required Ttables ("required ttable2 "). required tfields ("sname") <br/>. orientation = xlrowfield <br/>. position = 1 <br/>. caption = "name" <br/> end with <br/> 'place gender in rowfield <br/> with activesheet. pivotTables ("pi Vottable2 "). effectfields ("gender") <br/>. orientation = xlrowfield <br/>. position = 2 <br/>. caption = "gender" <br/> end with <br/> 'put the tname in columnfield <br/> with activesheet. required Ttables ("required ttable2 "). required tfields ("tname") <br/>. orientation = xlcolumnfield <br/>. position = 1 <br/>. caption = "instructor" <br/> end with <br/> 'place the cname in columnfield <br/> with activesheet. required Ttables ("required ttable2 "). effectfield S ("cname") <br/>. orientation = xlcolumnfield <br/>. position = 2 <br/>. caption = "Course" <br/> end with <br/> 'place score in datafield <br/> activesheet. required Ttables ("required ttable2 "). effectfields ("score "). orientation = xldatafield <br/> 'prohibit subtotals of name columns <br/> activesheet. required Ttables ("required ttable2 "). required tfields ("name "). subtotals = array (false, False) <br/> 'disallow subtotals in the course column <br/> activesheet. required Ttables ("required ttable2 "). extends tfields ("Course "). subtotals = array (false, false) <br/> 'do not list data columns <br/> activeworkbook. showshortttablefieldlist = false <br/> 'restrict column width <br/> activesheet. columns. columnwidth = 10 <br/> exit sub </P> <p> errhandler: <br/> msgbox "problems with database connection ", Vbokonly <br/> end sub </P> <p> sub createpivottable1 () <br/> dim connectionstring as string <br/> '<br/> connectionstring = "ODBC; DSN = school; uid =; Pwd =; "<br/> activeworkbook. worksheets ("sheet2 "). select </P> <p> with activeworkbook. using tcaches. add (sourcetype: = xlexternal) <br/>. connection = connectionstring <br/>. commandtype = xl1_ SQL <br/>. commandtext = array (_ <br/> "select. name as sname,. gen Der as gender, B. name as cname, C. name as tname, D. score as score from student A, course B, teacher C, SC d Where B. t_id = C. ID and. id = D. s_id and B. id = D. c_id "_ <br/>) <br/>. createpivottable tabledestination: = activesheet. cells (1, 9), tablename: = "audio ttable1" <br/> end with <br/> 'are required; otherwise, an error is returned! <Br/> activesheet. cells (1, 9 ). select <br/> activesheet. export ttablewizard tabledestination: = activesheet. cells (2, 9) <br/> '<br/> with activesheet. required Ttables ("required ttable1 "). required tfields ("tname") <br/>. orientation = xlrowfield <br/>. position = 1 <br/>. caption = "instructor" <br/> end with <br/> '<br/> with activesheet. required Ttables ("required ttable1 "). required tfields ("cname") <br/>. orientation = xlrowfield <br/>. position = 2 <br/>. caption = "Course" <br/> end with <br/> '<br/> with activesheet. required Ttables ("required ttable1 "). required tfields ("sname") <br/>. orientation = xlcolumnfield <br/>. position = 1 <br/>. caption = "name" <br/> end with <br/> '<br/> with activesheet. required Ttables ("required ttable1 "). effectfields ("gender") <br/>. orientation = xlcolumnfield <br/>. position = 1 <br/>. caption = "gender" <br/> end with <br/> '<br/> activesheet. required Ttables ("required ttable1 "). effectfields ("score "). orientation = xldatafield <br/> '<br/> activesheet. required Ttables ("required ttable1 "). pivotfields (" "). subtotals = array (false, false) '<br/> activesheet. required Ttables ("required ttable1 "). pivotfields ("gender "). subtotals = array (false, false) '<br/> activeworkbook. showemedittablefieldlist = false <br/> '<br/> activesheet. columns. columnwidth = 10 <br/> exit sub </P> <p> errhandler: <br/> msgbox "problems with database connection ", vbokonly <br/> end sub </P> <p>

 

At this point, VBA Excel programming has ended on the third day. The content listed below basically meets the daily needs. I hope it will be helpful for people who are new to VBA today. For more details, Google.