"VBA Study" reads Excel data routines with SQL statements

Iamlaosong

The data in the Excel worksheet can be processed directly, but it is best to read into the array processing if you want to be quick. As mentioned earlier, read-in arrays can be read in loops, or they can be read directly to an array. Although this method is fast, it is only suitable for reading contiguous columns, which is not feasible for separate columns, but can be read into the recordset with SQL statements and then processed by the recordset to see the following routines:

Sub tt ()    Dim cnn2, Rst2, cmd as Object    Dim sqls, SQL1, stname as String        Set cnn2 = CreateObject ("ADODB". Connection ")    Set Rst2 = CreateObject (" ADODB. Recordset ")    ' Set cmd = CreateObject (" ADODB. Command ")       datfile =" ID message name address 1.xls "                         ' file name   datfullname = thisworkbook.path &" \ "& Datfile   ' Cnnstr = "Provider=Microsoft.Jet.OLEDB.4.0; Jet oledb:database password=; Extended properties=excel 8.0;data source= "& datfullname cnnstr   =" provider=microsoft.jet.oledb.4.0; Extended properties=excel 8.0;data source= "& Datfullname cnn2      . Open cnnstr   Windows (datfile). Activate   stname = activesheet.name   sqls = "Select message number, recipient name from [" & Stname & "$]"   Debug.Print sql s   Set rst2 = cnn2. Execute (SQLS) while not   Rst2. EOF       Debug.Print rst2 (0) & Rst2 (1)       rst2.movenext   wendend Sub

A few notes:

1. The data file "ID address 1.xls", which is read in this routine, is open for the purpose of fetching the name of the worksheet. In terms of reading the data itself, do not need to open the Excel file, if the worksheet name is OK, you can not open;

2. The first row of the default worksheet is the field name, which is used to locate the field names in the SQL statement, such as "Mail Number", "Recipient name", etc. in the program. If there is no field name, add Hdr=no to the connection parameter ;

3, the connection parameter "Extended Properties=excel 8.0;" Can also be written as "Extended Properties=excel 5.0;", other morning numbers are not, this is the version number of Excel, corresponding to the EXCEL97, for Excel 97 or more versions are used in Excel 8.0. This extended attribute is used in conjunction with the previous provider, and the different versions correspond to different provider. My environment is Excel2007 version, version number is 12.0, if directly changed to 12.0 will error, hint: Can not find the installable ISAM, the correct wording is:

Cnnstr = "provider=microsoft.ace.oledb.12.0; Extended properties=excel 12.0;data source= "& Datfullname

4, there are some other parameters, as follows:

Value of the parameter HDR:
Hdr=yes, this means that the first row is the title, not as data use, if used Hdr=no, then the first row is not the title, as data to use. The system default is Yes
IMEX (IMport EXport mode) settings:
There are three modes of IMEX:
0 is Export mode
1 is Import mode
2 is Linked mode (full update capabilities)
What I want to highlight here is the IMEX parameter, because different patterns represent different reading and writing behaviors:
When imex=0 is "Export mode", the Excel file opened by this mode can only be used for "write" purposes.
When Imex=1 is "Import Mode", the Excel file opened in this mode can only be used for "read" purposes.
When imex=2 is "connected mode", the Excel file opened in this mode can support both "read" and "write" purposes.
The meanings are as follows:
0---output mode;
1---input mode;
2----link mode (full update capability)

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

"VBA Study" reads Excel data routines with SQL statements