Vbs operation Exel

Source: Internet
Author: User

'Author
'Shili
Option explicit
Dim date_y, date_m
Dim excelapp
Dim oexcel, countte, count

Class partner_rules

Public performance_excelpath
Public performance_excelmodel
Public oargs
Dim sdate
Public sub Init ()

Dim file_path, client_name, file_name
Export _excelpath = grtctx. FSO. getparentfoldername (wscript. scriptfullname)
Export _excelpath = left (export _excelpath, limit Rev (export _excelpath, "\")-1)
Set oargs = wscript. Arguments
File_path = oargs (0)
Client_name = oargs (1)
Sdate = oargs (2)
File_name = oargs (3)
Pai_excelmodel = file_path & "\" & file_name
'Cfg _ excelpath = getcurpath & "\ template \ lumingwen.xlsx"
'Call movemailfiles (pai_excelpath, pai_excelmodel)
'Error control, switch to off
Set oexcel = Createobject ("Excel. application ")
Oexcel. Visible = true
Set excelapp = oexcel. workbooks. Open (pai_excelmodel)
Oexcel. displayalerts = false
End sub
'Execution Function
Public sub process ()

Call Init ()
'Call class
 
'Connect to the database

Dim mobjdbselect
Db_name = "lmw_reportmanagemntsystemtest"
Username = "root"
Password = "cordys"
Server = "192.168.100.173"
If getmysqlconnection (Conn, server, db_name, username, password) then
'Connection successful
'Msgbox "connection success"
Else
'Connection failed
End if
Excelapp. worksheets ("message"). cells (1, 1). Font. Name = "ms p has been found successfully"
Excelapp. worksheets ("message"). Rows (1). Font. size = 12
Excelapp. worksheets ("message"). Rows (1). Font. Bold = true
Excelapp. worksheets ("message"). cells (1, 1). value = "base partition Parameters"
Excelapp. worksheets ("message "). cells (1, 2 ). value = "[full spectrum of zookeeper] zookeeper"
Excelapp. worksheets ("message"). cells (). value = "change"
Excelapp. worksheets ("message"). cells (). value = date_y & "year" & date_m & "month"
Excelapp. worksheets ("message"). cells (). value = ""
Excelapp. worksheets ("message"). cells (1, 1). Select
 
Call edit_report ()
 
'Your website has been saved. The Excel worksheet has been saved.
Excelapp. saveas performance_excelmodel
Oexcel. Quit
Set oexcel = nothing
 
End sub

Function getcurpath ()
Dim objfs
Dim strcurpath
Set objfs = Createobject ("scripting. FileSystemObject ")
Getcurpath = objfs. getparentfoldername (wscript. scriptfullname)
Set objfs = nothing
End Function

End Class
Function getmysqlconnection (byref tmpcon, byval tmpsvname, byval tmpdbname, byval tmpuid, byval tmppwd)

Set tmpcon = Createobject ("ADODB. Connection ")

On Error resume next
'Mysql uses mongoado to achieve zookeeper.
Tmpcon. Open "driver = {MySQL ODBC 5.1 Driver };"_
& "Server =" & tmpsvname &";"_
& "Database =" & tmpdbname &";"_
& "Uid =" & tmpuid & "; Pwd =" & tmppwd &";"
'Zookeeper was wrongly formed when the final result was judged
If err. Number = 0 then
Getmysqlconnection = true 'transaction success
Else
Fig
End if

End Function
Sub edit_report ()
Dim I, res_ SQL, count1, count2, countt, J
Dim objdata, objdata1, I2
Set res_ SQL = new table_class
Count = res_ SQL .select_count ()
I2 = 2
For I = 0 to (count-1) Step 1
'To get the total number corresponding to no objct_cd, which is the total number that does not correspond to a specific number. You can use this to insert it to it.
Count1 = res_ SQL .select_count1 ("0" & I &"")
Count2 = res_ SQL .select_count1 ("0" & (I + 1 )&"")

Countte = countte + count1
Objdata1 = res_ SQL .select_data1 ("0" & (I + 1 )&"")
Objdata = res_ SQL .select_data ()

Dim myarrary, Y, I1
########## Control how many rows the values in the second column are displayed
'If is not empty

If "" & objdata (I, 1) <> "then
I1 = 0
Myarrary = Split (objdata (I, 1), "<br> ")
For each Y in myarrary
Excelapp. worksheets ("message"). Rows (countte + I2 + I1 + 1). Interior. Color = RGB (255,255,255)
Excelapp. worksheets ("message"). cells (countte + I2 + I1), 1). value = objdata (I, 0)
Excelapp. worksheets ("message"). cells (countte + I2 + I1), 7). value = y
####### Set the font #####
 
Excelapp. worksheets ("message"). Rows (countte + I2 + I1). Font. Name = "ms p has been deleted successfully"
Excelapp. worksheets ("message"). Rows (countte + I2 + I1). Font. size = 11
Excelapp. worksheets ("message"). Rows (countte + I2 + I1). Font. Bold = true
'Excelapp. worksheets ("message"). Rows (countte + I2 + I1). Font. Underline = true
'##################
Excelapp. worksheets ("message"). cells (countte + I2 + I1, 7). horizontalalignment = 2
Excelapp. worksheets ("message"). cells (countte + I2 + I1, 7). verticalignment = 1
Excelapp. worksheets ("message "). range ("A" & (countte + I2) & "", "F" & (countte + I2 + I1 )&""). merge
I1 = I1 + 1
Msgbox ("#####" & I1)
Next
I2 = I2 + I1
Msgbox ("I2 ####" & I2)
Elseif objdata (I, 1) = "null" then
Msgbox ("null *******")
Excelapp. worksheets ("message"). cells (countte + I2), 1). value = objdata (I, 0)
I2 = I2 + 1
Else
Excelapp. worksheets ("message"). cells (countte + I2), 1). value = objdata (I, 0)
I2 = I2 + 1
End if
If count2 <> 0 then
Msgbox ("count2" & count2)
'####################################### #######
Msgbox ("I2 =" & I2)
'Output content
'##### Set the background color
Excelapp. worksheets ("message"). Rows (countte + I2). Interior. Color = RGB (191,191,191)
Excelapp. worksheets ("message"). Rows (countte + I2). Font. Name = "ms p has been deleted successfully"
Excelapp. worksheets ("message"). Rows (countte + I2). Font. Bold = true
'Excelapp. worksheets ("message"). Rows (countte + I2 + I1). Font. Color = RGB (192,192,192)
Excelapp. worksheets ("message"). cells (countte + I2), 1). value = "too many tables have been created successfully"
Excelapp. worksheets ("message"). cells (countte + I2), 2). value = "文"
Excelapp. worksheets ("message"). cells (countte + I2), 3). value = "content"
Excelapp. worksheets ("message"). cells (countte + I2), 4). value = "too many tables have been created"
Excelapp. worksheets ("message"). cells (countte + I2), 5). value = "too many tables have been created"
Excelapp. worksheets ("message"). cells (countte + I2), 6). value = "residential renewal"
Excelapp. worksheets ("message"). cells (countte + I2), 7). value = "too many tables have been created successfully"
'Excelapp. worksheets ("message"). cells (countte + 2 * I + 2), 1). value = objdata (I, 0)

'Excel. activesheet. range ("A" & (countte + 2 * I + 4) & "", "G" & (countte + 2 * I + 4 + count2 )&""). value = objdata1
'Cyclically retrieve the values below each entry and place them in the table.
For j = 1 to count2
Excelapp. worksheets ("message"). Rows (countte + I2 + J). Font. Name = "ms p has been deleted successfully"
Excelapp. worksheets ("message"). cells (countte + J + I2), 1). value = objdata1 (J, 0)
Excelapp. worksheets ("message"). cells (countte + J + I2), 2). value = objdata1 (J, 1)
Excelapp. worksheets ("message"). cells (countte + J + I2), 3). value = objdata1 (J, 2)
Excelapp. worksheets ("message"). cells (countte + J + I2), 4). value = objdata1 (J, 3)
Excelapp. worksheets ("message"). cells (countte + J + I2), 5). value = objdata1 (J, 4)
Excelapp. worksheets ("message"). cells (countte + J + I2), 6). value = objdata1 (J, 5)
Excelapp. worksheets ("message"). cells (countte + J + I2), 7). value = objdata1 (J, 6)
Next
Else
Excelapp. worksheets ("message"). Rows (countte + I2). Font. Name = "ms p has been deleted successfully"
Excelapp. worksheets ("message "). cells (countte + I2), 1 ). value = "※when determining whether the dynamic route is used, the dynamic route is used, and the dynamic route is retained. "
Excelapp. worksheets ("message"). cells (countte + I2 + 1), 1). value = "※googleadsense, microad contains multiple external objects. "
Excelapp. worksheets ("message "). cells (countte + I2 + 2), 1 ). value = "※there are two major problems during reading and writing; when there is a joint venture, we will introduce and recognize the basis. append a consumer token. "
Excelapp. worksheets ("message "). cells (countte + I2 + 3), 1 ). value = "※this parameter will be used when your website is hosted, and when your website is hosted, and your website is hosted on demand. append a consumer token. "
'Count = count-1
Call ()
End if
Next

End sub

Public sub ()
'To format the content in the Table. countte is from the top of the global variable to record how many records there are.
Msgbox (countte + 2 * count + 1)
Dim I, j, sum, number, number2
Sum = countte + 2 * count + 1
Number = 1
Number2 = 1
For I = 2 to sum
'Determine if the content of the cell in the first column is equal, merge
If excelapp. worksheets ("message"). cells (I, 1). value = excelapp. worksheets ("message"). cells (I + 1, 1). value then
Number = Number + 1
Else
Excelapp. worksheets ("message "). range (excelapp. worksheets ("message "). cells (I-Number + 1, 1), excelapp. worksheets ("message "). cells (I, 1 )). merge
Excelapp. worksheets ("message"). cells (I-Number + 1, 1). horizontalalignment = 2
Excelapp. worksheets ("message"). cells (I-Number + 1, 1). verticalignment = 1
Number = 1
End if
If excelapp. worksheets ("message"). cells (I, 2). value = excelapp. worksheets ("message"). cells (I + 1, 2). value then
Number2 = number2 + 1
Else
Excelapp. worksheets ("message "). range (excelapp. worksheets ("message "). cells (i-number2 + 1, 2), excelapp. worksheets ("message "). cells (I, 2 )). merge
'Set the content to display in the upper-right corner
Excelapp. worksheets ("message"). cells (i-number2 +). horizontalalignment = 2
Excelapp. worksheets ("message"). cells (i-number2 + 1, 2). verticalignment = 1
Number2 = 1
End if
Excelapp. worksheets ("message"). cells (I-Number + 1, 3). horizontalalignment = 2
Excelapp. worksheets ("message"). cells (I-Number + 1, 3). verticalignment = 1
Next
End sub
Public sub dbupload_start ()
Call global_dbuploadsmit
Dim pro
Set pro = new partner_rules
Call pro. Process ()
End sub

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.