Welcome to Office College, xiaoyang visit here waiting for you!
Today, we continue to learn together with you in all commonly used Excel spreadsheet formulas (functions) of the detailed usage and master the formula, many of our work will greatly improve the efficiency, we must learn yo, there do not understand can go to the Office College Questions (ask.officexy.com) here to ask Oh, we have progress, I will be pleased got:)
This lecture, we learn about Vlookup
The number of columns is not required, it is only described to facilitate the inconvenience
Column 1 column 2 3 4 5 6
元月 1020304050
February 8090100110120
March 9769455177
Enter search Month: March
Need to pick out the columns: 4
The result: 45
= VLOOKUP (G11, C6: H8, G12, FALSE)
Features
This function is the line in the table on the left to find specific content of the title
When found, it then pick out the line corresponding to the specified column of the cell contents.
Syntax
VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
Lookup_value for the need to find the first column of the array values. Lookup_value can be a value, reference or text string.
Table_array for the need to find data in which the data sheet. You can use the name of a reference area or areas, such as a database or data list.
If range_lookup is TRUE, then the first column of table_array value must be in ascending order: ..., -2, -1,0,1,2, ...,-Z, FALSE, TRUE; otherwise, VLOOKUP function can not return the correct value. If range_lookup is FALSE, table_array do not have to be sorted.
Through the "Data" menu "Sort" and select "Ascending", can be sorted in ascending numerical order.
Table_array values in the first column can be text, numbers or logical values.
Text is not case sensitive.
Col_index_num be returned to table_array matching value in column number. Col_index_num to 1, the return value in the first column table_array; col_index_num 2, the return value table_array the second column, and so on. If col_index_num less than 1, VLOOKUP returns the value of the # VALUE!; If col_index_num greater than the number of columns table_array, VLOOKUP returns the # REF!.
Range_lookup is a logical value that specifies the function VLOOKUP returns an exact match or approximate match. If TRUE or omitted, an approximate match is returned, that is, if you can not find an exact match, it returns the maximum value of less than lookup_value; if range_value is FALSE, VLOOKUP function will return an exact match. If not, it returns the error value # N / A.
Help
If the function VLOOKUP can not find the lookup_value, and range_lookup is TRUE, then use the maximum value less than or equal lookup_value.
If the first column of table_array lookup_value less than the minimum value, VLOOKUP returns the # N / A.
If the function VLOOKUP can not find the lookup_value and range_lookup is FALSE, VLOOKUP function returns the error value # N / A.
Format
There is no specific format
Example 1
The following examples are specified under the name and find a value based on the month.
= VLOOKUP () is used to look down along the first column name specified.
Difficulty is how to find the right month specified.
Solve this problem is to use = MATCH () function.
Function = MATCH () list of names by using the search to find the corresponding month. And projections for the month position in the list.
Unfortunately, because of the month search and find a list of the range of values ranging from wide.
Function = MATCH () function returns a number less than the number we need 1, so the +1 in the formula used to adjust.
Function = VLOOKUP () now use the function = MATCH () get adjusted n, in the name of the line corresponding to the line of the right to find the corresponding n-column input cell.
Function = VLOOKUP () in the final use of FALSE, so the left side do not sort the header row.
January February March
Cheng Hong-zhou 108097
Liu 209069
Cheng Long 3010045
Cheng-kun 4011051
chengxiang 50 120 77
Enter search name: Cheng Long
Enter search Month: March
The result: 45
= VLOOKUP (F60, C54: F58, MATCH (F61, D53: F53, 0) +1, FALSE)
3 = MATCH (F61, D53: F53, 0)
Example 2 This example uses the function = VLOOKUP () to find different car manufacturers in different parts of the value.
Function = VLOOKUP () scan down the column header row F and find the corresponding name in the C line of accessories.
Find the parts, the function VLOOKUP MATCH function to find the location according to the corresponding components to find the price.
Use absolute references in formulas, copying formulas in order to ensure that the function moves = HLOOKUP () and = MATCH () does not change the scope of reference.
Accessories manufacturers to find the value of the form
Toyota Spark Plug Toyota Ford Mercedes-Benz £ 50
£ 600 Mercedes-Benz transmission gearbox 500 450 600
Ford engine, the engine 10001200800 £ 1,200
Mercedes-Benz steering wheel 250 350 275 £ 275
Ford Spark Plug spark plug 507045 £ 70
Ford brake pads brake pads £ 290 300 290 310
Toyota gearbox £ 500
£ 1,200 Ford engine
= VLOOKUP (C80, F74: I78, MATCH (B80, G73: I73, 0) +1, FALSE)
Example 3
The following example is a building materials dealers offer a discount rate of the number of different procurement
Price list shows the brick, wood and glass unit.
Discount table provides a number of different products with different purchasing discount rate.
Procurement is the procurement budget table.
All results are displayed in the procurement budget table.
Name list in the C column.
The unit price is obtained from the price list.
FALSE option that product name in the table is not sorted in price order.
FALSE forced to use an exact match search. If not found, the function displays an error.
Discount is available from the discount table
If the purchase quantity discount table with a value match, the function = VLOOKUP will find in the discount table, the correct match discount.
TRUE option indicates the number of purchases through a discount table in ascending order sorting.
Use TRUE to allow fuzzy matching. If the purchase amount of the discount table does not find a match value, below it a smaller value will be used.
Such as the purchase quantity of 125 and 100 will be down to match the corresponding column and use a discount rate of 100.
Discount Table
Brick Wood Glass Price List
Brick £ 2 1 0% 0% 0%
Wood £ 1 100 6% 3% 12%
Glass £ 3 300 8% 5% 15%
Procurement Table
Project Procurement Quantity Unit Price Discount Total
Brick 125 £ 2 6% £ 235
Wood 200 £ 1 3% £ 194
Glass 150 £ 3 12% £ 396
Brick 225 £ 2 6% £ 423
Wood 50 £ 1 0% £ 50
Glass 500 £ 3 15% £ 1,275
The formula is:
Unit E118: = VLOOKUP (C118, C106: D108, 2, FALSE)
Discount F118: = VLOOKUP (D118, F106: I108, MATCH (C118, G105: I105, 0) +1, TRUE)
Total G118: = (D118 * E118) - (D118 * E118 * F118)
Example 4
The example uses an atmospheric pressure of the air value.
Density, viscosity, temperature,
0.457 3.55 500
0.525 3.25 400
0.616 2.93 300
0.675 2.75 250
0.746 2.57 200
0.835 2.38 150
0.946 2.17 100
1.09 1.95 50
1.29 1.71 0
Formula Description (Result)
2.17 in the A column to find 1 and B from the same row column return value (2.17) = VLOOKUP (1, B128: D136, 2)
100 in the A column to find 1, and C from the same row column return value (100) = VLOOKUP (1, B128: D136, 3, TRUE)
# N / A in the A column to find 0.746. A column because there is no exact match, it returns an error value (# N / A) = VLOOKUP (0.7, B128: D136, 3, FALSE)
# N / A in the A column to find 0.1. A column for the minimum value is less than 0.1, so return an error value (# N / A) = VLOOKUP (0.1, B128: D136, 2, TRUE)
1.71 Find in the A column 2 and column B from the same line in the return value (1.71) = VLOOKUP (2, B128: D136, 2, TRUE)
2010年12月16日星期四
How to use of timers in the Excel
Excel 97 used in the Add-Ins "time saving" it? Unfortunately, its source is encrypted, and now introduced to upload a document to achieve it.
There is a way in Office is application.ontime, specific functions are as follows:
expression.OnTime (EarliestTime, Procedure, LatestTime, Schedule)
If you would like further information, please see the Excel help.
This function is used to arrange for a process run at specific times in the future, (the date for a specified period of time, but also after the specified time period). Through this function in Excel where we can write your own regular program. Here are two examples to illustrate it.
1. In the afternoon, when a dialog box 17:00:00.
Sub Run_it ()
Application.OnTime TimeValue ("17:00:00"), "Show_my_msg"
'Set the timer activated in 17:00:00, activated running Show_my_msg.
End Sub
Sub Show_my_msg ()
msg = MsgBox ("Now is 17:00:00!", vbInformation, "Custom Information")
End Sub
2. Imitate Excel 97 in the "auto save the macro", where once in 5 seconds time
Sub auto_open ()
MsgBox "Welcome, in this document, once every 5 seconds to save the tips!", VbInformation, "Please pay attention!"
Call runtimer 'run automatically when you open a document
End Sub
Sub runtimer ()
Application.OnTime Now TimeValue ("00:00:05"), "saveit"
'Now TimeValue ("00:15:00") specified in the current time more than 5 seconds to start running Saveit the process.
End Sub
Sub SaveIt ()
msg = MsgBox ("Friends, you've worked a long time, save you now?" & Chr (13) _
& "Choice: immediately save" & Chr (13) _
& "Select No: not to save" & Chr (13) _
& "Choose to cancel: no longer appears this prompt", vbYesNoCancel 64, "take a break now!")
'Prompt the user to save the current active document.
If msg = vbYes Then ActiveWorkbook.Save Else If msg = vbCancel Then Exit Sub
Call runtimer 'If the user does not choose to cancel once again call Runtimer
End Sub
These are just two simple examples are interested, you can use Application.Ontime this function more useful time to write more programs.
There is a way in Office is application.ontime, specific functions are as follows:
expression.OnTime (EarliestTime, Procedure, LatestTime, Schedule)
If you would like further information, please see the Excel help.
This function is used to arrange for a process run at specific times in the future, (the date for a specified period of time, but also after the specified time period). Through this function in Excel where we can write your own regular program. Here are two examples to illustrate it.
1. In the afternoon, when a dialog box 17:00:00.
Sub Run_it ()
Application.OnTime TimeValue ("17:00:00"), "Show_my_msg"
'Set the timer activated in 17:00:00, activated running Show_my_msg.
End Sub
Sub Show_my_msg ()
msg = MsgBox ("Now is 17:00:00!", vbInformation, "Custom Information")
End Sub
2. Imitate Excel 97 in the "auto save the macro", where once in 5 seconds time
Sub auto_open ()
MsgBox "Welcome, in this document, once every 5 seconds to save the tips!", VbInformation, "Please pay attention!"
Call runtimer 'run automatically when you open a document
End Sub
Sub runtimer ()
Application.OnTime Now TimeValue ("00:00:05"), "saveit"
'Now TimeValue ("00:15:00") specified in the current time more than 5 seconds to start running Saveit the process.
End Sub
Sub SaveIt ()
msg = MsgBox ("Friends, you've worked a long time, save you now?" & Chr (13) _
& "Choice: immediately save" & Chr (13) _
& "Select No: not to save" & Chr (13) _
& "Choose to cancel: no longer appears this prompt", vbYesNoCancel 64, "take a break now!")
'Prompt the user to save the current active document.
If msg = vbYes Then ActiveWorkbook.Save Else If msg = vbCancel Then Exit Sub
Call runtimer 'If the user does not choose to cancel once again call Runtimer
End Sub
These are just two simple examples are interested, you can use Application.Ontime this function more useful time to write more programs.
Macros in Excel is what stuff it in the end
A macro is a set of instructions that tell EXCEL to complete the action specified by the user. Macro is similar to a computer program, but it is being fully operational in EXCEL, we can use a macro to complete the tedious, often repetitive work. Macro to complete the action faster than users themselves do much faster. For example, we can create a macro for each row of the worksheet, enter a date and in each cell within the center-aligned date and format of this border-line application. We can also create a macro, in the "Page Setup" dialog box, specify the print settings and print the document.
Because the impact of macro viruses and the fear of mental programming, many people dare not use the "macro", or do not know when to look for a macro to help. In fact, you may rest assured bold to use, if you only use the "record macro" approach, there are not any hard, just as some operations as recorded by tape recorder, to the use of the time, just run the macro, the system will put the operation and run it again.
Here are the macro applications, as long as the "Record Macro" to help you complete the task, without the need for programming. If you want the recorded macro and then edit it, we should have some knowledge of VBA.
* Set a table for each job requires a fixed form of the header;
* Set the cell into a form has its own style;
* Each print page setup are fixed;
* Frequently or repeatedly enter some fixed content, such as a good format for exclusive business address, personnel lists, etc.;
* Create a formatted table;
* Insert a worksheet or workbook and so on.
Should be noted that, EXCEL and WORD macros in the macro some differences, for the recording operation, it will remember the coordinates of the cell (ie, all references are absolute), so when it comes to position- The operation, to be extra attention. If the phase by using relative references, can be Offset by means of methods, such as the following statement: ActiveCell.Offset (1,0). Range ("A1"). Select the application macro is very wide, just a little bit above, If used together, you will find it to have more content and more flexible application methods.
Because the impact of macro viruses and the fear of mental programming, many people dare not use the "macro", or do not know when to look for a macro to help. In fact, you may rest assured bold to use, if you only use the "record macro" approach, there are not any hard, just as some operations as recorded by tape recorder, to the use of the time, just run the macro, the system will put the operation and run it again.
Here are the macro applications, as long as the "Record Macro" to help you complete the task, without the need for programming. If you want the recorded macro and then edit it, we should have some knowledge of VBA.
* Set a table for each job requires a fixed form of the header;
* Set the cell into a form has its own style;
* Each print page setup are fixed;
* Frequently or repeatedly enter some fixed content, such as a good format for exclusive business address, personnel lists, etc.;
* Create a formatted table;
* Insert a worksheet or workbook and so on.
Should be noted that, EXCEL and WORD macros in the macro some differences, for the recording operation, it will remember the coordinates of the cell (ie, all references are absolute), so when it comes to position- The operation, to be extra attention. If the phase by using relative references, can be Offset by means of methods, such as the following statement: ActiveCell.Offset (1,0). Range ("A1"). Select the application macro is very wide, just a little bit above, If used together, you will find it to have more content and more flexible application methods.
Improve the efficiency of Excel in VBA
Microsoft Office office suite as a wide range of applications, and continuously upgrade the software version, features continuous improvement platform office suite in the Office of the VBA to develop more and more applications, and VBA is a macro language, the operating speed There are very limited. VBA programming method thus directly related to the efficiency of VBA program is running, the paper cited a number of VBA procedures to improve operating efficiency.
Method 1: Try to use the original VBA properties, methods, and the Worksheet Functions
As many as hundred or more Excel objects, object properties, methods, events and more numerous, for starters, they may not all understand, which often resulted in the preparation of programming and Excel object properties, methods, functions the same VBA code, and the operating efficiency of the code is clear and Excel object properties, methods, task speed vary considerably. For example, with the Range property to return CurrentRegion Range object that represents the current area. (Refer to the current area any blank lines and blank columns on the composition of the border area). VBA code for the same functions to be dozens of lines. Therefore, before programming Excel objects as much as possible to understand the properties and methods.
Worksheet function is to increase take full advantage of the speed run extremely effective method. If the average wage demand example:
For Each c In
Worksheet (1). Range ("A1: A1000")
TotalValue = TotalValue + c.Value
Next
AverageValue = TotalValue / Worksheet (1). Range ("A1: A1000"). Rows.Count
The following example code much faster than the above procedures:
AverageValue = Application.WorksheetFunction.Average (Worksheets (1). Range ("A1: A1000"))
Other functions such as Count, Counta, Countif, Match, Lookup, etc., can replace the VBA code the same functionality to improve the running speed.
Method 2: minimize the use of object references, especially in the loop
The properties of each Excel object, method calls through the OLE interface requires one or more calls, the OLE calls takes time, reduce the use of object references to speed up VBA code to run. For example,
1. Use With statement.
Workbooks (1). Sheets (1). Range ("A1: A1000"). Font.Name = "Pay"
Workbooks (1). Sheets (1). Range ("A1: A1000"). Font.FontStyle = "Bold" ...
The following statement is faster than the above
With Workbooks (1). Sheets (1). Range ("A1: A1000"). Font
. Name = "Pay"
. FontStyle = "Bold"
...
End With
2. Using the object variable.
If you find an object reference to be used multiple times, then you can use this object to set the object variable Set to reduce access to the object. Such as:
Workbooks (1). Sheets (1). Range ("A1"). Value = 100
Workbooks (1). Sheets (1). Range ("A2"). Value = 200
The following code is faster than the above:
Set MySheet = Workbooks (1). Sheets (1)
MySheet.Range ("A1"). Value = 100
MySheet.Range ("A2"). Value = 200
3. In the loop to minimize access to the object.
For k = 1 To 1000
Sheets ("Sheet1"). Select
Cells (k, 1). Value = Cells (1,1). Value
Next k
The following code is faster than the above:
Set TheValue = Cells (1,1). Value
Sheets ("Sheet1"). Select
For k = 1 To 1000
Cells (k, 1). Value = TheValue
Next k
Method 3: Reduce the object's activation and selection
If you have to learn VBA by recording a macro, then your VBA program in a certain object activation and full of options, such as Workbooks (XXX). Activate, Sheets (XXX). Select, Range (XXX). Select and so on, but In fact in most cases these operations are not required. For example,
Sheets ("Sheet3"). Select
Range ("A1"). Value = 100
Range ("A2"). Value = 200
Can be changed to:
With Sheets ("Sheet3")
. Range ("A1"). Value = 100
. Range ("A2"). Value = 200
End With
Method 4: Turn off screen updates
If your VBA procedures done in front of three relatively poor, then close the VBA program is to increase the screen update speed of the most effective way to shorten the running time of 2 / 3. Way off the screen updates:
Application.ScreenUpdate = False
Please do not forget the end of VBA and then run back to set the value:
Application.ScreenUpdate = True
VBA is to improve the operating efficiency over the more effective of several methods.
Method 1: Try to use the original VBA properties, methods, and the Worksheet Functions
As many as hundred or more Excel objects, object properties, methods, events and more numerous, for starters, they may not all understand, which often resulted in the preparation of programming and Excel object properties, methods, functions the same VBA code, and the operating efficiency of the code is clear and Excel object properties, methods, task speed vary considerably. For example, with the Range property to return CurrentRegion Range object that represents the current area. (Refer to the current area any blank lines and blank columns on the composition of the border area). VBA code for the same functions to be dozens of lines. Therefore, before programming Excel objects as much as possible to understand the properties and methods.
Worksheet function is to increase take full advantage of the speed run extremely effective method. If the average wage demand example:
For Each c In
Worksheet (1). Range ("A1: A1000")
TotalValue = TotalValue + c.Value
Next
AverageValue = TotalValue / Worksheet (1). Range ("A1: A1000"). Rows.Count
The following example code much faster than the above procedures:
AverageValue = Application.WorksheetFunction.Average (Worksheets (1). Range ("A1: A1000"))
Other functions such as Count, Counta, Countif, Match, Lookup, etc., can replace the VBA code the same functionality to improve the running speed.
Method 2: minimize the use of object references, especially in the loop
The properties of each Excel object, method calls through the OLE interface requires one or more calls, the OLE calls takes time, reduce the use of object references to speed up VBA code to run. For example,
1. Use With statement.
Workbooks (1). Sheets (1). Range ("A1: A1000"). Font.Name = "Pay"
Workbooks (1). Sheets (1). Range ("A1: A1000"). Font.FontStyle = "Bold" ...
The following statement is faster than the above
With Workbooks (1). Sheets (1). Range ("A1: A1000"). Font
. Name = "Pay"
. FontStyle = "Bold"
...
End With
2. Using the object variable.
If you find an object reference to be used multiple times, then you can use this object to set the object variable Set to reduce access to the object. Such as:
Workbooks (1). Sheets (1). Range ("A1"). Value = 100
Workbooks (1). Sheets (1). Range ("A2"). Value = 200
The following code is faster than the above:
Set MySheet = Workbooks (1). Sheets (1)
MySheet.Range ("A1"). Value = 100
MySheet.Range ("A2"). Value = 200
3. In the loop to minimize access to the object.
For k = 1 To 1000
Sheets ("Sheet1"). Select
Cells (k, 1). Value = Cells (1,1). Value
Next k
The following code is faster than the above:
Set TheValue = Cells (1,1). Value
Sheets ("Sheet1"). Select
For k = 1 To 1000
Cells (k, 1). Value = TheValue
Next k
Method 3: Reduce the object's activation and selection
If you have to learn VBA by recording a macro, then your VBA program in a certain object activation and full of options, such as Workbooks (XXX). Activate, Sheets (XXX). Select, Range (XXX). Select and so on, but In fact in most cases these operations are not required. For example,
Sheets ("Sheet3"). Select
Range ("A1"). Value = 100
Range ("A2"). Value = 200
Can be changed to:
With Sheets ("Sheet3")
. Range ("A1"). Value = 100
. Range ("A2"). Value = 200
End With
Method 4: Turn off screen updates
If your VBA procedures done in front of three relatively poor, then close the VBA program is to increase the screen update speed of the most effective way to shorten the running time of 2 / 3. Way off the screen updates:
Application.ScreenUpdate = False
Please do not forget the end of VBA and then run back to set the value:
Application.ScreenUpdate = True
VBA is to improve the operating efficiency over the more effective of several methods.
SAMPLE: ExcelADO Demonstrates How to Use ADO to Read and Write Data in Excel Workbooks
The information in this article applies to:
Microsoft Excel 2000
ActiveX Data Objects (ADO) 2.5
ActiveX Data Objects (ADO) 2.6
Microsoft Visual Basic Professional Edition for Windows 6.0
Microsoft Visual Basic Enterprise Edition for Windows 6.0
Microsoft Active Server Pages
This article was previously published under Q278973
SUMMARY
The ExcelADO.exe sample illustrates how you can use ActiveX Data Objects (ADO) with the Microsoft Jet OLE DB 4.0 Provider to read and write data in Microsoft Excel workbooks.
MORE INFORMATION
The following file is available for download from the Microsoft Download Center:
ExcelADO.exe
Release Date: December 12, 2000
For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:
119591 How to Obtain Microsoft Support Files from Online Services
Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.
Why Use ADO?
The use of ADO to transfer data to or retrieve data from an Excel workbook gives you, the developer, several advantages over Automation to Excel:
Performance. Microsoft Excel is an out-of-process ActiveX server. ADO runs in-process, and saves the overhead of costly out-of-process calls.
Scalability. For Web applications, it is not always desirable to automate Microsoft Excel. ADO presents you with a more scaleable solution to handle data in a workbook.
ADO can be used strictly to transfer raw data to a workbook. You cannot use ADO to apply formats or formulas to cells. However, you can transfer data to a workbook that is pre-formatted and the format is maintained. If you require "conditional" formatting after the data is inserted, you can accomplish this formatting with Automation or with a macro in the workbook.
Jet OLE DB Provider Specifics for Excel Workbooks
The Microsoft Jet database engine can be used to access data in other database file formats, such as Excel workbooks, through installable Indexed Sequential Access Method (ISAM) drivers. In order to open external formats supported by the Microsoft Jet 4.0 OLE DB Provider, you specify the database type in the extended properties for the connection. The Jet OLE DB Provider supports the following database types for Microsoft Excel workbooks:
Excel 3.0
Excel 4.0
Excel 5.0
Excel 8.0
NOTE: Use the Excel 5.0 source database type for Microsoft Excel 5.0 and 7.0 (95) workbooks and use the Excel 8.0 source database type for Microsoft Excel 8.0 (97) and 9.0 (2000) workbooks. The ExcelADO.exe sample uses Excel workbooks in the Excel 97 and Excel 2000 format.
The following samples demonstrate an ADO connection to an Excel 97 (or 2000) workbook:
Dim oConn As New ADODB.Connection
With oConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties").Value = "Excel 8.0"
.Open "C:\Book1.xls"
'....
.Close
End With
-or-
Dim oConn As New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;" & _
"Extended Properties=""Excel 8.0;"""
oConn.Close
Table Naming Conventions
There are several ways you can reference a table (or range) in an Excel workbook:
Use the sheet name followed by a dollar sign (for example, [Sheet1$] or [My Worksheet$]). A workbook table that is referenced in this manner consists of the entire used range of the worksheet.
oRS.Open "Select * from [Sheet1$]", oConn, adOpenStatic
Use a range with a defined name (for example, [Table1]).
oRS.Open "Select * from Table1", oConn, adOpenStatic
Use a range with a specific address (for example, [Sheet1$A1:B10]).
oRS.Open "Select * from [Sheet1$A1:B10]", oConn, adOpenStatic
Table Headers
With Excel workbooks, the first row in a range is considered to be the header row (or field names) by default. If the first range does not contain headers, you can specify HDR=NO in the extended properties in your connection string. If the first row does not contain headers, the OLE DB provider automatically names the fields for you (where F1 would represent the first field, F2 would represent the second field, and so forth).
Data Types
Unlike a traditional database, there is no direct way to specify the data types for columns in Excel tables. Instead, the OLE DB provider scans a limited number of rows in a column to "guess" the data type for the field. The number of rows to scan defaults to eight (8) rows; you can change the number of rows to scan by specifying a value between one (1) and sixteen (16) for the MAXSCANROWS setting in the extended properties of your connection string.
Files Included with the Sample
The ExcelADO.exe file contains a Visual Basic Standard EXE project, Active Server Pages (ASP), Excel 97 and Excel 2000 Workbooks that act as templates, and a Microsoft Access 2000 database. The files included are as follows:
Visual Basic Standard EXE Project Files
ExcelADO.vbp
Form1.frm
Form1.frx
Active Server Pages
EmpData.asp
Orders.asp
Microsoft Excel Workbooks
OrdersTemplate.xls
EmpDataTemplate.xls
ProductsTemplate.xls
SourceData.xls
Microsoft Access Database
Data.mdb
How to Use the Sample
Extract the contents of the .exe file to a folder.
To use the Visual Basic project:
In Visual Basic, open the ExcelADO.vbp file.
On the Project menu, select References, and then set references to Microsoft ADO Ext. for DDL and Security and Microsoft ActiveX Data Objects Library. This sample code works with both ADO 2.5 and ADO 2.6, so select the version appropriate to your computer.
Press the F5 key to run the program. A form for the demonstration appears.
Click Sample 1. This sample creates a copy of OrdersTemplate.xls. It then uses ADO to connect to the workbook and opens a Recordset on a table that is a defined range in the workbook. The name of the range is Orders_Table. It uses ADO AddNew/Update methods to add records (or rows) to the defined range in the workbook. When the row additions are complete, the ADO Connection is closed and the workbook is displayed in Microsoft Excel. Follow these steps to do this:
On the Insert menu in Excel, select Names, and then select Define.
In the list of defined names, select Orders_Table. Note that the defined name has grown to include the newly added records. The defined name is used, in conjunction with Excel's OFFSET function, to compute a total on the data added to the worksheet.
Quit Microsoft Excel and return to the Visual Basic application.
Click Sample 2. This sample creates a copy of EmpDataTemplate.xls. It uses ADO to connect to the workbook and uses the Execute method of the ADO connection to insert data (INSERT INTO in SQL) into the workbook. Data is added at defined ranges (or tables) in the workbook. When the data is transferred, the connection is closed and the workbook that results is displayed in Excel. After you examine the workbook, quit Microsoft Excel, and then return to the Visual Basic application.
Click Sample 3. This sample creates a copy of ProductsTemplate.xls. It uses Microsoft ADO Extensions 2.1 for DDL and Security object library (ADOX) to add a new table (or a new worksheet) to the workbook. An ADO Recordset is then obtained for the new table and data is added by using the AddNew/Update methods. When the row additions are complete, the ADO Connection is closed and the workbook is displayed in Excel. The workbook contains Visual Basic for Applications (VBA) macro code in the Open event for the Workbook. The macro runs when the workbook opens; if the new "Products" worksheet exists in the workbook, the macro code formats the worksheet and then the macro code is deleted. This technique presents a way for the Web developer to move formatting code away from the Web server and onto the client. A Web application could stream a formatted workbook that contains data to the client and allow macro code that would perform any "conditional" formatting that might not be possible in a template alone to run at the client.
NOTE: To examine the macro code, view the ThisWorkbook module in the VBAProject for ProductsTemplate.xls.
Click Sample 4. This sample produces the same results as Sample 1, but the technique that is used to transfer the data is slightly different. In Sample 1, records (or rows) are added to the worksheet one at a time. Sample 4 adds the records in bulk by attaching the Excel table to an Access database and running an append query (or INSERT INTO..SELECT FROM) to append records from a table in the Access table to the Excel table. Once the transfer is complete, the Excel table is detached from the Access database and the workbook that results is displayed in Excel. Quit Excel, and return to the Visual Basic application.
The last sample illustrates how you can read data from an Excel workbook. Select a table in the drop-down list, and then click Sample 5. The Immediate window displays the contents of the table that you selected. If you select an entire worksheet ("Sheet1$" or "Sheet2$") for the table, the Immediate window displays the contents of the used range for that worksheet. Note that the used range does not necessarily begin on row 1, column 1 of the worksheet. The used range starts at the upper left-most cell in the worksheet that contains data.
If you select a specific range address or a defined range, the Immediate window displays the contents of only that range on the worksheet.
To use the Active Server Pages (ASP):
Create a new folder named ExcelADO in the home directory of your Web server. Note that the default path for the home directory is C:\InetPut\WWWRoot.
Copy the following files to the folder you created in the previous step:
EmpData.asp
Orders.asp
Data.mdb
EmpDataTemplate.xls
OrdersTemplate.xls
The ASP scripts in this sample create copies of the workbook templates with the Copy method of the FileSystemObject. For the Copy method to succeed, the client that is accessing the script must have Write access to the folder that contains the ASP.
Navigate to Orders.asp (that is, http://YourServer/ExcelADO/Orders.ASP), and note that the browser displays an Excel workbook similar to the one in Sample 1 of the Visual Basic application.
Navigate to EmpData.asp (that is, http://YourServer/ExcelADO/EmpData.ASP), and note that the browser displays an Excel workbook similar to the one in Sample 2 of the Visual Basic application.
(c) Microsoft Corporation 2000, All Rights Reserved. Contributions by Lori B. Turner, Microsoft Corporation.
REFERENCES
For more information, see the white paper "Automating Office 97 and Office 2000" available on the following Microsoft Web site at:
http://support.microsoft.com/support/officedev/automation.asp
For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
195951 HOWTO: Query and Update Excel Data Using ADO From ASP
194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset
193998 HOWTO: Read and Display Binary Data in ASP
247412 INFO: Methods for Transferring Data to Excel from Visual Basic
257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA
Last Reviewed: 5/20/2003
Keywords: kbAutomation kbfile kbProgramming KB278973
Microsoft Excel 2000
ActiveX Data Objects (ADO) 2.5
ActiveX Data Objects (ADO) 2.6
Microsoft Visual Basic Professional Edition for Windows 6.0
Microsoft Visual Basic Enterprise Edition for Windows 6.0
Microsoft Active Server Pages
This article was previously published under Q278973
SUMMARY
The ExcelADO.exe sample illustrates how you can use ActiveX Data Objects (ADO) with the Microsoft Jet OLE DB 4.0 Provider to read and write data in Microsoft Excel workbooks.
MORE INFORMATION
The following file is available for download from the Microsoft Download Center:
ExcelADO.exe
Release Date: December 12, 2000
For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:
119591 How to Obtain Microsoft Support Files from Online Services
Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.
Why Use ADO?
The use of ADO to transfer data to or retrieve data from an Excel workbook gives you, the developer, several advantages over Automation to Excel:
Performance. Microsoft Excel is an out-of-process ActiveX server. ADO runs in-process, and saves the overhead of costly out-of-process calls.
Scalability. For Web applications, it is not always desirable to automate Microsoft Excel. ADO presents you with a more scaleable solution to handle data in a workbook.
ADO can be used strictly to transfer raw data to a workbook. You cannot use ADO to apply formats or formulas to cells. However, you can transfer data to a workbook that is pre-formatted and the format is maintained. If you require "conditional" formatting after the data is inserted, you can accomplish this formatting with Automation or with a macro in the workbook.
Jet OLE DB Provider Specifics for Excel Workbooks
The Microsoft Jet database engine can be used to access data in other database file formats, such as Excel workbooks, through installable Indexed Sequential Access Method (ISAM) drivers. In order to open external formats supported by the Microsoft Jet 4.0 OLE DB Provider, you specify the database type in the extended properties for the connection. The Jet OLE DB Provider supports the following database types for Microsoft Excel workbooks:
Excel 3.0
Excel 4.0
Excel 5.0
Excel 8.0
NOTE: Use the Excel 5.0 source database type for Microsoft Excel 5.0 and 7.0 (95) workbooks and use the Excel 8.0 source database type for Microsoft Excel 8.0 (97) and 9.0 (2000) workbooks. The ExcelADO.exe sample uses Excel workbooks in the Excel 97 and Excel 2000 format.
The following samples demonstrate an ADO connection to an Excel 97 (or 2000) workbook:
Dim oConn As New ADODB.Connection
With oConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties").Value = "Excel 8.0"
.Open "C:\Book1.xls"
'....
.Close
End With
-or-
Dim oConn As New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;" & _
"Extended Properties=""Excel 8.0;"""
oConn.Close
Table Naming Conventions
There are several ways you can reference a table (or range) in an Excel workbook:
Use the sheet name followed by a dollar sign (for example, [Sheet1$] or [My Worksheet$]). A workbook table that is referenced in this manner consists of the entire used range of the worksheet.
oRS.Open "Select * from [Sheet1$]", oConn, adOpenStatic
Use a range with a defined name (for example, [Table1]).
oRS.Open "Select * from Table1", oConn, adOpenStatic
Use a range with a specific address (for example, [Sheet1$A1:B10]).
oRS.Open "Select * from [Sheet1$A1:B10]", oConn, adOpenStatic
Table Headers
With Excel workbooks, the first row in a range is considered to be the header row (or field names) by default. If the first range does not contain headers, you can specify HDR=NO in the extended properties in your connection string. If the first row does not contain headers, the OLE DB provider automatically names the fields for you (where F1 would represent the first field, F2 would represent the second field, and so forth).
Data Types
Unlike a traditional database, there is no direct way to specify the data types for columns in Excel tables. Instead, the OLE DB provider scans a limited number of rows in a column to "guess" the data type for the field. The number of rows to scan defaults to eight (8) rows; you can change the number of rows to scan by specifying a value between one (1) and sixteen (16) for the MAXSCANROWS setting in the extended properties of your connection string.
Files Included with the Sample
The ExcelADO.exe file contains a Visual Basic Standard EXE project, Active Server Pages (ASP), Excel 97 and Excel 2000 Workbooks that act as templates, and a Microsoft Access 2000 database. The files included are as follows:
Visual Basic Standard EXE Project Files
ExcelADO.vbp
Form1.frm
Form1.frx
Active Server Pages
EmpData.asp
Orders.asp
Microsoft Excel Workbooks
OrdersTemplate.xls
EmpDataTemplate.xls
ProductsTemplate.xls
SourceData.xls
Microsoft Access Database
Data.mdb
How to Use the Sample
Extract the contents of the .exe file to a folder.
To use the Visual Basic project:
In Visual Basic, open the ExcelADO.vbp file.
On the Project menu, select References, and then set references to Microsoft ADO Ext. for DDL and Security and Microsoft ActiveX Data Objects Library. This sample code works with both ADO 2.5 and ADO 2.6, so select the version appropriate to your computer.
Press the F5 key to run the program. A form for the demonstration appears.
Click Sample 1. This sample creates a copy of OrdersTemplate.xls. It then uses ADO to connect to the workbook and opens a Recordset on a table that is a defined range in the workbook. The name of the range is Orders_Table. It uses ADO AddNew/Update methods to add records (or rows) to the defined range in the workbook. When the row additions are complete, the ADO Connection is closed and the workbook is displayed in Microsoft Excel. Follow these steps to do this:
On the Insert menu in Excel, select Names, and then select Define.
In the list of defined names, select Orders_Table. Note that the defined name has grown to include the newly added records. The defined name is used, in conjunction with Excel's OFFSET function, to compute a total on the data added to the worksheet.
Quit Microsoft Excel and return to the Visual Basic application.
Click Sample 2. This sample creates a copy of EmpDataTemplate.xls. It uses ADO to connect to the workbook and uses the Execute method of the ADO connection to insert data (INSERT INTO in SQL) into the workbook. Data is added at defined ranges (or tables) in the workbook. When the data is transferred, the connection is closed and the workbook that results is displayed in Excel. After you examine the workbook, quit Microsoft Excel, and then return to the Visual Basic application.
Click Sample 3. This sample creates a copy of ProductsTemplate.xls. It uses Microsoft ADO Extensions 2.1 for DDL and Security object library (ADOX) to add a new table (or a new worksheet) to the workbook. An ADO Recordset is then obtained for the new table and data is added by using the AddNew/Update methods. When the row additions are complete, the ADO Connection is closed and the workbook is displayed in Excel. The workbook contains Visual Basic for Applications (VBA) macro code in the Open event for the Workbook. The macro runs when the workbook opens; if the new "Products" worksheet exists in the workbook, the macro code formats the worksheet and then the macro code is deleted. This technique presents a way for the Web developer to move formatting code away from the Web server and onto the client. A Web application could stream a formatted workbook that contains data to the client and allow macro code that would perform any "conditional" formatting that might not be possible in a template alone to run at the client.
NOTE: To examine the macro code, view the ThisWorkbook module in the VBAProject for ProductsTemplate.xls.
Click Sample 4. This sample produces the same results as Sample 1, but the technique that is used to transfer the data is slightly different. In Sample 1, records (or rows) are added to the worksheet one at a time. Sample 4 adds the records in bulk by attaching the Excel table to an Access database and running an append query (or INSERT INTO..SELECT FROM) to append records from a table in the Access table to the Excel table. Once the transfer is complete, the Excel table is detached from the Access database and the workbook that results is displayed in Excel. Quit Excel, and return to the Visual Basic application.
The last sample illustrates how you can read data from an Excel workbook. Select a table in the drop-down list, and then click Sample 5. The Immediate window displays the contents of the table that you selected. If you select an entire worksheet ("Sheet1$" or "Sheet2$") for the table, the Immediate window displays the contents of the used range for that worksheet. Note that the used range does not necessarily begin on row 1, column 1 of the worksheet. The used range starts at the upper left-most cell in the worksheet that contains data.
If you select a specific range address or a defined range, the Immediate window displays the contents of only that range on the worksheet.
To use the Active Server Pages (ASP):
Create a new folder named ExcelADO in the home directory of your Web server. Note that the default path for the home directory is C:\InetPut\WWWRoot.
Copy the following files to the folder you created in the previous step:
EmpData.asp
Orders.asp
Data.mdb
EmpDataTemplate.xls
OrdersTemplate.xls
The ASP scripts in this sample create copies of the workbook templates with the Copy method of the FileSystemObject. For the Copy method to succeed, the client that is accessing the script must have Write access to the folder that contains the ASP.
Navigate to Orders.asp (that is, http://YourServer/ExcelADO/Orders.ASP), and note that the browser displays an Excel workbook similar to the one in Sample 1 of the Visual Basic application.
Navigate to EmpData.asp (that is, http://YourServer/ExcelADO/EmpData.ASP), and note that the browser displays an Excel workbook similar to the one in Sample 2 of the Visual Basic application.
(c) Microsoft Corporation 2000, All Rights Reserved. Contributions by Lori B. Turner, Microsoft Corporation.
REFERENCES
For more information, see the white paper "Automating Office 97 and Office 2000" available on the following Microsoft Web site at:
http://support.microsoft.com/support/officedev/automation.asp
For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
195951 HOWTO: Query and Update Excel Data Using ADO From ASP
194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset
193998 HOWTO: Read and Display Binary Data in ASP
247412 INFO: Methods for Transferring Data to Excel from Visual Basic
257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA
Last Reviewed: 5/20/2003
Keywords: kbAutomation kbfile kbProgramming KB278973
EXCEL was time to leave the cell values automatically * 100
Question:
In a cell enter a number, leave this cell was automatically when the number * 100, how to program solution?
Answer:
Use SelectionChange event, leave the cell to capture the value of the specified * 100 after the re-write
In a cell enter a number, leave this cell was automatically when the number * 100, how to program solution?
Answer:
Use SelectionChange event, leave the cell to capture the value of the specified * 100 after the re-write
EXCEL Tips: Batch modify data
In the EXCEL spreadsheet data have been completed, how easy for any one column (row) of data to modify it?
For instance, we do an EXCEL table, fill the data, and now want to modify a column (row), for example: like in the A column on the basis of the original data plus 8, there is no such formula? Not have to manually one by one, add the data to live? For this we naturally think of the use of formula, you use the public input A1 = A1 8, you will get a warning EXCEL: "MICROSOFT EXCEL the formula can not be calculated ... ..."Only we ourselves think of a way out, and here a Kinds of simple methods:
The first step:
You want to modify the column (assumed to be A column) next to insert a temporary new column (for the B column), and the first column in the B cell (B1), enter 8.
Step two:
B1 or the mouse on the bottom corner, wait until after the live cross down into the data until the desired length, then all of the data column B 8.
The third step:
In the B column, click the right mouse button, "Copy"B column.
Step four:
A column in the right-mouse click in the pop-up dialog box, click "Paste"in the pop-up dialog box, select "operation" in the operator you need, here we choose "add" This is the key to this method.
Step five:
The B column removed.
How? A column with each of the data is not all 8 it? The same approach can be achieved on a column (row) of multiplication, addition, subtraction, and other computing operations. The format of the original form has not changed.
At this point the end of the work, the use of skilled, it will take less than ten seconds.
For instance, we do an EXCEL table, fill the data, and now want to modify a column (row), for example: like in the A column on the basis of the original data plus 8, there is no such formula? Not have to manually one by one, add the data to live? For this we naturally think of the use of formula, you use the public input A1 = A1 8, you will get a warning EXCEL: "MICROSOFT EXCEL the formula can not be calculated ... ..."Only we ourselves think of a way out, and here a Kinds of simple methods:
The first step:
You want to modify the column (assumed to be A column) next to insert a temporary new column (for the B column), and the first column in the B cell (B1), enter 8.
Step two:
B1 or the mouse on the bottom corner, wait until after the live cross down into the data until the desired length, then all of the data column B 8.
The third step:
In the B column, click the right mouse button, "Copy"B column.
Step four:
A column in the right-mouse click in the pop-up dialog box, click "Paste"in the pop-up dialog box, select "operation" in the operator you need, here we choose "add" This is the key to this method.
Step five:
The B column removed.
How? A column with each of the data is not all 8 it? The same approach can be achieved on a column (row) of multiplication, addition, subtraction, and other computing operations. The format of the original form has not changed.
At this point the end of the work, the use of skilled, it will take less than ten seconds.
订阅：
博文 (Atom)