Excel培训 VBA操作实凝据库实例.pptVIP

VBA入门范例精解;;引用类;主过程;Private Sub unify(ByVal sht As Worksheet, _ ByVal strSql As String, _ ByVal strPercentColumns As String, _ ByVal strDecimalColumns As String) clearSht sht FillData sht, strSql lineJin sht colorSheet sht percentJin sht, strPercentColumns, 0.00% percentJin sht, strDecimalColumns, 0.00 FontSheet sht, Calibri, 10 Columns(A:Z).AutoFit End Sub;Private Function getRowCount(ByVal sht As Worksheet) As Long Dim lRowsNum As Long Dim i As Long For i = 3 To 65535 If sht.Cells(i, 1).Value = Then lRowsNum = i - 1 Exit For End If Next getRowCount = lRowsNum End Function;记录表格中的数据列数;清除表格中记录;Sub FillData(ByVal sht As Worksheet, _ ByVal Sql As String) Dim conn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim CnStr As String CnStr = “Provider=SQLOLEDB;” _ “Data Source=;” _ “Integrated Security=SSPI conn.Open CnStr conn.CommandTimeout = 500 rs.Open Sql, conn, 1 sht.[A3].CopyFromRecordset rs rs.Close conn.Close Set conn = Nothing End Sub;Private Sub lineJin(ByVal sht As Worksheet) ‘添加边框 Dim rng As Range Dim strTemp As String Dim i As Integer sht.Activate strTemp = Split(Cells(1, getColumnCount(sht)).Address, $)(1) getRowCount(sht) Set rng = sht.Range(A3: strTemp) rng.Select ‘选中需要边框的范围 For i = 7 To 12 Step 1 With Selection.Borders(i) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Next i Set rng = Nothing End Sub;填充背景色;修改字体;修改文字格式;Thank You !