[Vb/VBA] SQL operation Excel

Source: Internet
Author: User

[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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.