excelvba编程24学时教程20（）

1、excelvba编程24学时教程20 ()this paper is contributed by water335pdf document may at the wap end of the browsing experience poor suggest you select txt, or download the source file to the machine v i ew.introduction of 20 hour class data accessin this session, we will be ready to use the loading macro call

2、ed ms query, and use ms query to create queries to import data from an external data source the biggest advantage of the ms query tool is that you can use excet s macro recorder to record the entire process the focus of this class include:? some of the discussion to retrieve data database format? to

3、 outline some methods to retrieve external data? how to use the ms query to retrieve the data change recorded ms query macro?20.1 available databasesexcel can be used in almost all types of database, enabling access to the data in the database the following is a list of some of them to be able to us

4、e the database format: microsoft? access? microsoft sql server? microsoft foxpro? oracle?paradox? dbase? ascii text f订e? sylk you might consider, why need to put the data into the excel database when dealing with certain things, excel can do better than the database, which includes computing, analyz

5、ing and plotting charts after the data into the excel, can process excel data using these advantages20.2 data access methodin fact, you've applied a database access method that can be used in e x c e l, and pivottable can be used to import external data into exce 1. the external data into other

6、methods of excel include: ms? query? ado (activex data objects, activex data object (data)? dao access objects, data access object (open)? odbc database connectivity, open database connectivity) quick and effective access to the best method of microsoft access and other data in the database is used

7、ado. the main support dao for backward compatibility with earlier versions of a c c e s s. odbc can be used to connect a variety of databases, including microsoft sql and 0 r a c l e this course will focus on the use of microsoft query to the external data intointroduction and application of twentie

8、th hour class data accessone hundred and sixty-ninemethods in excel20.3 use ms query to access datafrom the most simple method of e x c e l is used to access external data called ms query add ms query is a tool that helps users connect to data sources and retrieve data from a data source, and the re

9、trieved data is placed into the worksheet for excel developers, ms query has a major advantage: it can use macro recorder to record the entire processing process, which can save time, but also have to pay the price you may have already guessed, performanee of ms query in the data access method is th

10、e slowest you，re going to decide: which of the three factors is most important to you: speed, simple development process, and better application performance? if you decide to use ms query as the application of a part of the solution, you need to install ms query to a user's computer, in a typica

11、l excel installation is not installed ms query components when fully installed, will install ms query. any time can also be installed in the excel after the re install ms query. the database that we're going to use in this class is northwind, which is an example database that is installed with m

12、icrosoft access to create a macro for retrieving data from a northwind database, perform the following steps: 1) close all workbooks and open a new workbook2) starts recording a macro named msqueryexample, storing this macro in the current workbook 3) select "data", "get external data

13、", new database query", "select data source" dialog box display, and, as shown in figure 20t. users can either choose from a defined data source or create a new data sourcefigure 20t "select the data source" dialog box lists the data sources that have been defined on th

14、e system4) select the n o r t h w i n d, and then click the ok button, and the query wizard select column dialog box is displayed, as shown in figure 20-2figure 20-2 when connecting to the data source, the next step is to select the column5) a list of queries and tables is displayed. to see the avai

15、lable columns, click the + sign (plus) next to the options in the available table and column list box to find and select the product click the product next to the +" mark to seeone hundred and seventyusing chinese excel 2000 programming 24 hours tutorialwww. 6ago. netlist of columns select the

16、product i d, and then click add button (» to add the column to the list of columns in the query results. add the "product name", "unit price", "inventory", and click the next button. the query wizard filter data dialog box is displayed, as shown in figure 20-3figur

17、e 20-3 screen allows users to retrieve only the records you want6) to be screened from the column list box select" price greater than or equal to "from the first drop-down list box, in a list box type 2 0, this will create a screening, it only shows the price of more than $2 0 those items

18、click the "next" button, and the query wizard sort order dialog box is displayed, as shown in figure 20-4.figure 20-4 selected after screening, you can choose according to the sort of7) select the "unit price" from the "main keyword" drop-down list box, click the "

19、next" button, "query wizard completion" dialog box display, as shown in figure 20-5figure 20-5 ms query in the treatment of the retrieved data, provides several options for the userintroduction and application of twentieth hour class data accessone hundred and seventy-one8) click the

20、finish button to return to the excel, and return the external data to the microsoft excel dialog box, as shown in figure 20-6figure 20-6 the last step before importing external data into e x c e l is to tell e x c e l to place the data location9) to place data on the current sheet of a 1 cells if a

21、1 is not listed in this dialog box, select it and click ok a little later, according to the work table, can be used. as shown in figure 20-7 10) stop recording macros now, we are ready to view the recorded code look at the msqueryexampl e with process in the visual basic editor, which may surprise y

22、ou that the code is a huge statement listing 20-1 provides a complete codefigure 207 retrieval data back to the user in the workbookprogram listing 20-1 msqueryexample process 1: sub msqueryexample (2:) 3: with activesheet querytables .add (connection:=array (array (_ 4: "odbc dbq=c:program fil

23、esmicrosoft office0fficesamples, northwind, mdb; defaultdir=c:program files, microsoft officeofficesamples;，z), and array (，zdriver= microsoft access driver (*mdb); driverld=281; fil二ms access;f implicitcomniitsync 二是的；maxbuffersize = 512； max scanrows =8；页”)、阵列(“超时=5； -safetransactions = 0；螺纹=3； ui

24、d二管理；一百七十二使用中文excel 2000编程24学吋教程www. 6ago. net5： 6： 7： 8： 9:10 11:12 13:14 15:16： 17:18 19:20 21：-usercommitsync =是的；”)，目的地：二范围f (al)。commandtext 二阵歹!j ("选择products. productid, products productnamef 产 品单价、产品。unitsinstock chr (13) && chr (10)和"c：程序一文件微软office办公样本f northwind”产品chr (1

25、3) ->&” “&chr (10) &” (产品。unitpri ce> = 15) ” & chr (13) && chr (10)和一”订单的产品。单价”)。名称=“northwind” 查询，字段名二真。rownumbers = falseofi 1 ladjacentformulas 二 falseo preserveformatting 二真。 refreshonf订eopen 二 falseo backgroundquery 二真。refreshstyle 二 xlinsertdeleteceliso savepassw

26、ord 二真。存档二真。adjustcolum nwidth 二真。refreshperiod 二 0。preservecolumn info 二真。刷新、数据志跟踪sh backgroundquery：二端子假结束执行的第一个主要语句是添加方法，这条语句包含了有关连接数据源 的信息，其中包括文件位置和驱动程序信息： 在当前工作表的querytableso添加(连接：二数组(数组(_ “odbc； dbq二c： 程序文件微软办公室办公样品f northwind .mdb； defaultdir二c： 程序文件微软办公室办公样品；”)，数组(_f ” 司机二微软 access driver (*

27、.mdb) ； -driverid = 281； 费尔二 ms access； -implicitcommitsync 二是的；maxbuffersize 二 512； maxscanrows 二8；页”)、阵列(“超时二 5； -safetransactions 二 0； 螺纹二 3； uid 二管理；-usercommitsync 二是的；”)，冃的地：二范围(a1)当建立连接以后，接着设置了 query table的command te x t属性，这实际上是一个s elec t语句，它根据用户在” 查询向导”中的选择来筛选合适的数据：(阵列。cominandtext 二“选择 prod

28、ucts, productid,产品。产品 名称，productsunitprice, f 产品。unitsinstock” & chr (13) & “”& chr (10)和一”从“c： 程序文件微软c eoff我ice 的 mplesnorthwin d-。产品” & chr (13) && chr(10)和“十”(产品单价 = 15)和 chr (13) &&-chr (10)和“为了产品的单价。“)过程中其余代码设置了各种各样的属性，现在已经可以看到笔者谈论 到的ms query的主第20学时数据访问介绍使用 一百七十三

29、 要好处了：通过录制宏，开发工作就是”小菜一碟”！你很可能会问 怎样修改这种类型的宏，修改这种类型宏的一个完美示例就是设置宏 来提示用户止在查找的是什么价格。要实现这一点，please modify the procedure according to the code given in listing 20 - 2 of the program the modified code is marked in bold blacklisting msqueryexample modified 20-2 process1: sub msqueryexample (2:) dim sngprice

30、as single 3: dim smessage as string 4: 5: worksheets add 6: smessage 二"you wish to see prices greater than: 7: sngprice 二 application. inputbox (smessage,，z enter price, "type:二 1 8: 9: with(connection:=array) activesheet querytables add (array ( odbc dbq=c:program fi.1 esmicrosoft 10:offi

31、ce0fficesamples, northwind.mdb; defaultdir=c:program filesmicrosoft, office0fficesamples; array ("driver二), and microsoft access driver (* mdb) ; driverld=281 - fil二ms; access; implicitcommitsyncyes; maxbuffersize=512;maxscanrows=8; page), and array (timeout=5; safetransactionso; threads二3; uid

32、=admin; usercommitsync=yes; destination:=range) “)，(“ al )11 ： cominandtext 二 array ( select products.prod uctid, products.productname,productsunitprice, and productsunitsinstock & chr (13)& chr & fr0m，c: program (10), "filesmicrosoft off i c e 0 ff i c e samplesnorthwind'produc

33、ts, products & chr (13), & & chr (10) where (products. unitprice>=, " & "“)& sngprice & & chr (13) & & chr & order (10), by products. unitprice ) 12:.name query from northwind，/ 二 13:fieldnames 二 true 14:rownumbers 二 false 15：filladjacentformulas

34、二 false16:preserveformatting = true 17:.refreshonf i1eopen = false 18backgroundquery 二 true 19:.refreshstyle 二 xlinsertdeletecells 20：savepassword 二 true 21:.savedata 二 true 22adjustcolumnwidth 二 true 23：refreshperiod 二 0 24：preservecolumninfo 二 true 25：refresh backgroundquery:=false 26: end with 27

35、: end subin the modified example, the code first creates several variables, the first variable stores the value entered by the user, and the second variable, smessage, is simply used to save the text of the input boxone hundred and seventy-fourusing chinese excel 2000 programming 24 hours tutorialww

36、w. 6ago. netthe real process starts with adding a blank worksheet, which allows the user to ensure that the data doesn't cover anything accidentally. next, the procedure prompts the user to enter the filter condition, and then uses the input value in the sei e c t statement to replace the locati

37、on where the original number 15 is. the process of running, when prompted, in the price of 20 input box, you can see the workbook to add a worksheet, and return to record prices are greater than or equal to $20. a simple and useful is to modify the recording code ms query is not limited to the use o

38、f a single table, it can also be based on multiple linked tables to create queries the use of ms query as a development tool in another way is to use ms query to create all the necessary query, then all queries to a query file, in this process will be used to save the database query. to see an examp

39、le of using a saved database query, select the a 1 cell in the data returnedif the cell has not been displayed, display the '"external data" toolbar, click the button, click the next button displayed by the query wizard until the "finish" button appears click save query, ente

40、r prices as the name of the query and click the "save" button. then click the finish button to close the query wizard dialog box now, we' re going to use storage queries add a new worksheet to a workbook, recording a macro named pricesexample, select theand "get external dataz，run

41、saved query “，and front run the query dialog box to select p rices, and click the "data", when the prompt location returns the data, click ok. after the completion of data retrieval, stop recording the macro. switch to the visual basic code editor view record listing 20-3 gives the recorde

42、d macro:program listing 20-3 pricesexample process1: sub pricesexample (2:) 3: with activesheet querytables .add (connection:二 _ 4: "finder;c:winntprofilesadministratorapplication, datamicrosoftqueriespricesdqy _ 5:, destination:=range (al) 6：name 二 prices "7：fieldnames 二 true 8：rownumbers

43、 二 false 9：filladjacentfonnulas 二 falsa 10:. preserveformatting 二 true 11:. refreshonfileopen 二 false 12:. backgroundquery 二 true 13:. refreshstyle 二 xllnsertdeletecells 14:. savepassword 二 true 15：savedata 二 true 16：adjustcolumnwidth 二 true 17:.refreshperiod 二 0 18：preservecolumninfo 二 true19:.refr

44、esh backgroundquery:=false 20: end with 21: end subthe main difference between this process and the msqueryexample process is that the add method and the pricesexample process do not set the c o m m a d n te x t property the drawback of this approach is not able to prompt the user to insert informat

45、ion query. however, if the user does not need to input the information query of the standard, it is still a good way.introduction and application of twentieth hour class data accessone hundred and seventy-fivesummary of 20. 4 hoursnow, we already know two ways to use external data, as discussed in t

46、he nineteenth class, using pivottable to retrieve external data. in this school, learning how to use ms query to retrieve data method both of these two methods can use macro recorders to record the entire processing process, which is a boon for excel developers who have time pressure in the next cla

47、ss, we will learn to use a d 0 to access data, which is the method that provides the most control and best performance in the approach discussed in this book20.5 experts answer questionsquestion: can ms query to use access to create the query? answer: not necessarily. we can use ms query to create a new query based on a c c e s s on the existing query. however, access queries cannot be run from data, access to external data, run query query menu command. question: if you wnt to modify, and use ms to create query query, how to do? answer: in