About connecting VBS to MySQL and Excel, vbsmysqlexcel

Source: Internet
Author: User

About connecting VBS to MySQL and Excel, vbsmysqlexcel

Objective: To connect to MySQL and find data in the new Excel table

Implementation process:

1. Connect to the database

First, go to the MySQL website to download an ODBC data source.

After installation, you can view the MySQL driver name in Control Panel-Administrative Tools-ODBC Data Source-add:


'Connection database StrCnn = "Provider = MSDASQL.1; Persist Security Info = True; Extended Properties = 'driver = MySQL ODBC 5.3 Unicode Driver; SERVER = 188.1.1.132; UID = grute; PWD = grute; DATABASE = grute; PORT = 3307 '"Set Cnn = CreateObject (" ADODB. connection ") Cnn. open strCnn 'check whether the connection is successful. The successful status value is 1If Cnn. state = 0 Then <span style = "white-space: pre"> </span> msgbox "database connection failed" <span style = "white-space: pre "> </span> wscript. quit End If

2. Connect to Excel

'Connect to Exceldim oExcel, oWb, oSheet Set oExcel = CreateObject ("Excel. Application") oexcel. Workbooks. Add ()

3. Enter an SQL statement to search for data from MySQL.

strQuery = "select * from test"Set rs = Cnn.Execute(strQuery)

4. Loop MySQL query results into Excel

<Pre name = "code" class = "vb"> <pre name = "code" class = "vb"> 'I is the Excel row number, k is the column number <pre name = "code" class = "vb"> Dim ii = 0arr_column = array ("Serial Number", "name", "username", "password ", "permission") If Not rs. BOF ThenDo While Not rs. EOFi = I + 1For k = 1 To 5' loop 5 times oExcel. cells (I, k ). value = rs (arr_column (k-1) Nextrs. moveNextLoopElsewscript. echo "failed" End If

 
 
5. Save the Excel file 

oexcel.ActiveWorkbook.SaveAs("C:\Users\Administrator\Desktop\test.xlsx")

6. Exit

OExcel. WorkBooks. Close oExcel. Quit rs. CloseCnn. CloseSet Cnn = Nothingmsgbox "imported"

Above,

The complete code is as follows. The MySQL Data source must be installed on the local machine. The MySQL user name on the machine 188.1.1.132 is grute, the password is grute, the database name is grute, and the table name is test and test, name, user name, password, and permission), and save it as the suffix VBS to run.

'Define the variable Dim CnnDim RstDim strCnnDim ii = 0arr_column = array ("Serial Number", "name", "username", "password", "permission ") 'Connection database StrCnn = "Provider = MSDASQL.1; Persist Security Info = True; Extended Properties = 'driver = MySQL ODBC 5.3 Unicode Driver; SERVER = 188.1.1.132; UID = grute; PWD = grute; DATABASE = grute; PORT = 3307 '"Set Cnn = CreateObject (" ADODB. connection ") Cnn. open strCnn 'check whether the connection is successful. The successful status value is 1If Cnn. state = 0 Thenmsgbox "failed to connect to Database" wscript. quit End If 'connect to Exceldim oExcel, oWb, oSheet Set oExcel = CreateObject ("Excel. application ") oexcel. workbooks. add () 'input SQL statement strQuery = "select * from test" Set rs = Cnn. execute (strQuery) 'I is the Excel row number and k is the column number arr_column = array ("Serial Number", "name", "username", "password", "permission ") if Not rs. BOF ThenDo While Not rs. EOFi = I + 1For k = 1 To 5' loop 5 times oExcel. cells (I, k ). value = rs (arr_column (k-1) Nextrs. moveNextLoopElsewscript. echo "failed" End Ifoexcel. activeWorkbook. saveAs ("C: \ Users \ Administrator \ Desktop \ test.xlsx") oExcel. workBooks. close oExcel. quit rs. closeCnn. closeSet Cnn = Nothingmsgbox "imported"


Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.

Related Article

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.