SELECT Field name FROM Table name

On the spot, I whispered... That the keyword FROM indicates the table name to obtain the field information. If the data source is an Excel table, you need to add the dollar sign $after the table name and wrap it in square brackets, such as [Sheet1 $]

In fact, the above example is the simplest case where SQL In Excel refers to a worksheet, that is, the whole table reference; In addition, there are cell range references, cross workbook references, etc.

So let's focus on the Excel table in SQL statement today.

 1. Regional tabulation

There are many differences between Excel worksheets and database datasheets. The most significant thing is that the database datasheet can be understood as composed of rows and columns, while Excel worksheets are composed of one cell after another, and these cells have a unique address expression method, that is, A1 or R1C1. They can also form a cell area connected with data, such as A2:H8.

So the question is, if we only need to calculate some areas of an Excel worksheet, how should SQL express it?

This problem is very common.

For example, many people's Excel Title row is not in the first row of the table, but in the second row

As shown in the figure below ▼

          

At this point, we want to calculate the cell range of column A2:F, so that we can easily use the field name to process the data instead of the whole Excel worksheet

For another example, there are two or more "tables" in a table... What does this sentence mean?

See figure ▼

          

In the table shown in the figure, there is both a "teacher table" and a "student table"; If we only want SQL to reference the teacher table data of A2:D8

Er, it's easy for young people to be angry

... SQL in Excel actually supports the use of the cell range of the worksheet as a "table".

For the problem shown in the figure above, the SQL can be written as:

SELECT full name,subject FROM [data sheet $A2:D8]

The query results are as follows:

          

In the first case, we know that the data starts in cell A2, but we don't know which cell ends in column F. the SQL can be written as:

SELECT full name,hobby FROM [Student list $A2:F]

In addition, if we need SQL to reference the data of the entire column D:G of the calculation table, the SQL can be written as:

SELECT * FROM [Student list $D:G]

Summarize the expressions of the above Excel worksheet ranges, that is, the worksheet name + dollar sign $+ cell address in the relative reference state, and finally wrap it in square brackets.

Just soy sauce purple.

💡 Tips in this section:

[student table $A2:F], we said that this statement can refer to the cell range with data at the end of columns A2 to F, but there is a restrictive precondition, that is, non self connected state. The so-called self connection refers to the application of SQL to the workbook linking itself. In the self link state, the expression mode of A2:F is A2:F65536 lines at most; If more than 65536 reference rows are needed at this time, please use the whole table mode.

 2. Table across workbooks

A well-known problem is that Excel functions are very tired when dealing with cross workbook data. Except for a few search reference functions (such as VLOOKUP), most functions can be calculated and used only after opening relevant workbooks.

Yes, the VLOOKUP function can be used across workbooks without opening related workbooks. After the VLOOKUP formula is written, even if you delete the workbook it refers to, it will not hinder its calculation. This is because it has cached the relevant data into the workbook where the formula is located. However, VLOOKUP mode does not support complex nesting of functions... Ring your fingers, On this point, if you are interested, let's talk alone another day.

... well, back to SQL~~

... the SQL statements we shared before are all tables that process the current workbook. If the data we need to process is located in other workbooks, how should SQL be expressed?

For example, if you get all the data of the "grade sheet" in the Workbook "student sheet. xlsx" located in the "EH primary school" folder on disk D of the computer - you have to admire it.

If it is OLE DB method (refer to Chapter 1 of this series of tutorials for this method), the SQL statement is as follows ▼

SELECT * FROM [D:\EH primary school\Student list.xlsx].[Transcript $] 

The specified table string after FROM consists of two parts. The first square bracket is the storage path of the specified workbook + the full workbook name with suffix, the last square bracket is the worksheet name, and the English dot (.) is used between the two square brackets to be connected.

If you use SQL statements through VBA+ADO

Knock on the warning in front of the bookcase: Children's shoes with poor VBA foundation, please skip the following contents by yourself

Compared with OLE DB method, VBA+ADO method is much more flexible. It can use ADO to directly create and open the link with the specified workbook, so the SQL statement does not need to specify the full name of the workbook.

The code reference is as follows ▼
 

Sub ADO_SQL()    'Applicable to higher versions except 2003 version Excel    Dim cnn As Object, rst As Object    Dim strPath As String, strCnn As String, strSQL As String    Dim i As Long    Set cnn = CreateObject("adodb.connection")    strPath = "D:\EH primary school\Student list.xlsx" 'Specify Workbook    strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & strPath    cnn.Open strCnn 'Creates and opens a link to the specified workbook    strSQL = "SELECT * FROM [Transcript $]" 'strSQL Statement to query all the data in the grade table    Set rst = cnn.Execute(strSQL) 'implement strSQL    Cells.ClearContents    For i = 0 To rst.Fields.Count - 1        Cells(1, i + 1) = rst.Fields(i).Name    Next    Range("a2").CopyFromRecordset rst    cnn.Close    Set cnn = NothingEnd Su

Line 7 of the above code directly specifies the full name of the workbook to be connected, so special processing is no longer required in the SQL statement.


But more often, the link created by ADO is a workbook, and the data to be obtained is in another or more workbooks, such as data query statistics between two workbooks. The codes commonly used at this time are as follows ▼

Sub ADO_SQL2()    'Applicable to higher versions except 2003 version Excel    Dim cnn As Object, rst As Object    Dim strPath As String, strCnn As String, strSQL As String    Dim i As Long    Set cnn = CreateObject("adodb.connection")    strPath = ThisWorkbook.FullName 'The full name of the workbook where the code resides    strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & strPath    cnn.Open strCnn 'Create a link to the workbook where the code resides    strSQL = "SELECT * FROM [Excel 12.0;DATABASE=D:\EH primary school\Student list.xlsm].[Transcript $]"    Set rst = cnn.Execute(strSQL) 'implement SQL    Cells.ClearContents    For i = 0 To rst.Fields.Count - 1        Cells(1, i + 1) = rst.Fields(i).Name    Next    Range("a2").CopyFromRecordset rst    cnn.Close    Set cnn = NothingEnd Sub

Line 7 in the code creates a link to the current workbook, and the SQL statement specifies a link to another workbook. The SQL statement is as follows ▼

SELECT * FROM [Excel 12.0;DATABASE=D:\EH primary school\Student list.xlsx].[Transcript $]

The string FROM the specified table consists of two parts. In the first bracket, Excel 12.0 is the version number of the target workbook. As we said in Chapter 2, Excel 12.0 is applicable to all Excel versions except 2003. DATABASE specifies the path and name of the data source workbook. The second square bracket is the worksheet name. The two brackets are connected by an English dot.

It seems that the SQL statement of VBA+ADO method is more complex than OLE DB method? Indeed, but the former is also more powerful. For example, it can conditionally filter workbooks and worksheets through VBA object properties, methods, loops and judgment statements... In contrast, SQL statements in OLE DB are pure manual constant mode. Of course, more importantly, the former can not only query data, but also add, modify and delete data, while the latter is limited to query.