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.