Import and export data between [Excel] and [any data source] _ temporarily Save the query results

ArticleDirectory

• Range. copyfromrecordset method call format:

Because the SQL query results are also a table, you can write the query results into a prepared worksheet row by row (record.

The following describes three methods to place query results at a specified position.

 

Use the copyfromrecordset Method

Range. copyfromrecordset method call format:

Expression. Copyfromrecordset (Data,Maxrows,Maxcolumns)

Example:

 Sub  Use the copyfromrecordset method to specify the storage location ()  '  Use the cells object to call the copyfromrecordset method in the target cell in the upper left corner of the output table. Activeworkbook. Save  '  First Query Connstr = "  Driver = {Microsoft Excel Driver (*. xls)}; DBQ =  " & Activeworkbook. fullname & "  ;  "      Set Conn = Createobject ( "  ADODB. Connection  " ) Conn. Open connstr into Str = "  Select a. Class, A. name from [sheet1 $] A order by A. Class  "          Set Rs = Createobject ( "  ADODB. recordset  "  ) Rs. Open parameter STR, Conn,  3 , 1 , 1  Sheets. Add With  Activesheet. cells (  1 , 1 ) = "  Class  "  . Cells (  1 , 2 ) = "  Name  "  . Cells (  2 , 1 ). Copyfromrecordset rs  '  Notes:          '  The first date type field is displayed incorrectly.           '  Second, processing binary data of non-Excel databases will fail.           '  Third, the title line will not be copied,      End   With  Conn. Close  End sub 

 

 

Disadvantages of the copyfromrecordset method:

1. When processing the [date type] field, the [number format] is displayed. You must adjust the cell format manually.
2. If it is not an Excel database, this method also fails when it encounters binary data (shape type). The solution is to use a custom method.
3. This method does not display the field name (that is, the title line). You need to manually create the title line before using this method. However, you do not need to use this method.

 

Use the into clause of the SELECT query to specify the storage location

 

This is a "write after query" action.

Example:

 Sub  Add an into clause () When querying using select statements ()  '  The select_into method specifies the storage location. The command object must be used because the write operation is required.      Dim Conn As  ADODB. Connection  Dim CMD As  ADODB. Command activeworkbook. Save connstr = "  Driver = {Microsoft Excel Driver (*. xls)}; DBQ =  " & Activeworkbook. fullname & "  ; Readonly = false  "      Set Conn = Createobject ( "  ADODB. Connection  " ) Conn. Open connstr  '  Explain STR = "select * into [Excel 8.0; database = c: \ result.xls;]. [sheet_result $] from [sheet5 $]"      '  Unable to run: Error prompt table [sheet4 $] is not a valid name          '  First, correct writing      '  * Explain STR = "select * into [Excel 8.0; database = c: \ result.xls;]. [sheet_result] from [sheet5 $]"      '  Successful !!!      '  You can use the select into statement to copy data from any data source that can be read by jet to any data target,     '  Create a new table at the time of creation (a new worksheet in Excel ).      '  [Note] do not use the dollar symbol syntax when the worksheet name is used as the target, for example, [sheet1 $].      '  [Note] the target workbook may or may not exist. However, the target worksheet must not exist .]      '  [Here] [Excel 8.0;] is a "type parameter". Note that the semicolon following it cannot be deleted.      '  Second, correct writing      '  Explain STR = "select * into [sheet1] in'' [Excel 8.0; database = c: \ r1.xls] from [sheet1 $]"      '  Note: Two empty single quotes ''are used to specify the type parameters (Excel 8.0 ;).     '  When this syntax is used, the type parameter is included in the brackets (so it is null single quotes)          '  The third method is the alternative syntax of the second method. Put the type parameter Excel 8.0 below Required STR = "  Select * into [sheet4] In 'C: \ r1.xls ''excel 8.0;' from [sheet5 $]  "      '  Note: In this syntax, the type parameter (Excel 8.0;) is separately listed after the target file path      '  And no parentheses are needed (but single quotation marks are required)              '  [Note] the new Workbook of select into does not have any worksheet, so you can also specify the new worksheet as sheet1, sheet2, and sheet3.             '  Incorrect      '  Explain STR = "select * into OpenRowSet ('Microsoft. jet. oledb.4.0 ', 'excel 8.0; HDR = yes; database = c: \ result.xls;', 'select * from [sheet1 $]) from [sheet5 $]"              Set Cmd = Createobject ( "  ADODB. Command  "  )  Set Cmd. activeconnection = Conn cmd. commandtext =Reverse STR cmd. Execute conn. Close  End sub 

 

 

Notes:

• Because this is the "write after query" operation, do not use ADODB. recordset.ADODB. Command
• The target workbook does not need to exist in advance. When the target worksheet is requiredDoes not exist.
• This method can beExchange data with data in any data source (as long as correct connectionstrings is used)To create a new table.
• Worksheet as the target, requiredDo not use dollar sign syntax $.
• The into clause can be written in three ways:
• Use the Select... into syntax of. (DOT accessors), such as: into [Excel 8.0; database = c: \ result.xls;]. [sheet_result]
• Select... into... in syntax, such as: into [sheet1] in ''[Excel 8.0; database = c: \ r1.xls]
• Select... into... in substitution syntax, such as: into [sheet4] In 'C: \ r1.xls ''excel 8.0 ;'

 

Use the into clause of the insert statement to append the query results to a storage location.

 

You can use the insert into... in statement to attach data from any data source that the jet can read to any data target.

The target workbook and target worksheet must both exist.

Since you currently reference an existing worksheet, you must use the standard dollar sign syntax when using the worksheet name as the target,

The column title must already exist. That is, this statement can only be used to attach to an existing table.

Example:

 '  Use the into clause of the insert statement to specify the same storage location for multiple query results (additional)  Sub  Append query results to the same location ()  '  Required, the output location must exist beforehand          '  Create output location      ' Because the insert... into method is used, the output file must exist in advance. Therefore, you must create a workbook first.  Application. displayalerts = False  Workbooks. Add  '  Add a new Workbook. Use the workbook set object.      '  Set the title line      With  Activeworkbook. activesheet. cells (  1 , 1 ). Value = "  Class  " . Cells (  1 , 2 ). Value = "  Name  "  . Cells (  1 , 3 ). Value = "  Exam No.  "  . Cells (  1 , 4 ). Value ="  Chinese  "  . Cells (  1 , 5 ). Value = "  Algebra  "  . Cells (  1 , 6 ). Value = "  English  "              End  With      '  Save Activeworkbook. saveas filename: = "  C: \ r2.xls  " , Fileformat: = Xlexcel8  '  Note that the table format is xlexcel8.0. Otherwise, the table cannot be inserted (error message: incorrect file format)  Activeworkbook. Close application. displayalerts = True  Activeworkbook. Save connstr = " Driver = {Microsoft Excel Driver (*. xls)}; DBQ =  " & Activeworkbook. fullname & "  ; Readonly = false  "      Set Conn = Createobject ( "  ADODB. Connection  "  ) Conn. Open connstr  Set Cmd = Createobject ( " ADODB. Command  "  )  Set Cmd. activeconnection = Conn  '  [Note] the into clause can be written in the following three ways:  Reverse Str = "  Insert into [Excel 8.0; database = c: \ r2.xls]. [sheet1 $]  " & _  "  Select a. Class, A. Name, A. Examination number, A. Language, A. Algebra, A. English from [sheet1 $] A where a. Class = '20140901' "  Cmd. commandtext = Reverse STR cmd. Execute reverse Str = "  Insert into [sheet1 $] in ''[Excel 8.0; database = c: \ r2.xls]  " & _  "  Select a. Class, A. Name, A. Examination number, A. Language, A. Algebra, A. English from [sheet1 $] A where a. Class = '20140901'  "  Cmd. commandtext = Reverse STR cmd. Execute reverse Str ="  Insert into [sheet1 $] In 'C: \ r2.xls ''excel 8.0 ;'  " & _  "  Select a. Class, A. Name, A. Examination number, A. Language, A. Algebra, A. English from [sheet1 $] A where a. Class = '20140901'  "  Cmd. commandtext = Explain STR cmd. Execute  '  If the error message "target table type problem" appears, set the fileformat to xlexcel8 in saveas.  Conn. Close  End sub 

 

 

Note:

1. The target workbook and target worksheet must both exist.
2. The column title must already exist. That is, this statement can only be used to attach to an existing table.
3. The column title must be consistent with the {select clause Field List} in the insert statement. Otherwise, the message "Incorrect field type" is displayed ".
4. Since you currently reference an existing worksheet, you must use the standard dollar sign syntax when using the worksheet name as the target,
5. Any data source can be appended to excel, as long as the connectionstring is correct
6. The into... in syntax supports three methods, the same as above.
7. [Note] This method is applicableThe format of the target Workbook is required (Xlexcel8 is required) FormatOtherwise, the "worksheet format error" is returned when the insert... into method is used ".

 

Note:

For excel, common databases provideProgramIncluding:

1. Provider = Microsoft. Jet. oledb.4.0
2. Provider = Microsoft. Ace. oledb.12.0 (ACE 12.0 is the latest Access database access engine)
3. Driver = {Microsoft Excel Driver (*. xls)}; (this is Microsoft Excel ODBC driver)
4. . NET Framework data provider for ole db, etc.