[Vb/VBA] SQL operation Excel

[Vb/VBA] SQL operation Excel

1. Add reference

(1) Microsoft Active Data Objects 2.8 Library

(2) Microsoft ADO Ext. 2.8 for DDL and security

2. Use ADOX to connect to an Excel file

 
Dim cat as ADOX. catalog dim TBL as ADOX. table dim Col as ADOX. column dim cmd as ADODB. command dim RS as recordset set cat = new ADOX. catalog cat. activeconnection = "provider = Microsoft. jet. oledb.4.0; "& _" Data Source = "& thisworkbook. path & _ "/book2.xls; extended properties = Excel 8.0"
 

3. Create a sheet and assign values to cells

Set TBL = new ADOX. table TBL. name = "testtable" set Col = new ADOX. column With Col. name = "col1 ". type = addouble end with TBL. columns. append Col set Col = nothing set Col = new ADOX. column With Col. name = "col2 ". type = advarwchar end with TBL. columns. append Col cat. tables. append TBL
 

4. Execute SQL (select, insert, update, and delete are all supported.CodeTake select as an example)

 
Set cmd = new ADODB. command cmd. commandtext = "select TA. *, TB. W from [Table 1 $] Ta, [Table 2 $] TB where TA. A = TB. A and TA. A <'a10' "cmd. commandtype = ad1_text cmd. activeconnection = cat. activeconnection set rs = cmd. execute () while not Rs. EOF debug. print Rs. getstring () Wend