Excel培训VBA操作数据库实例.ppt

VBA入门范例精解,范例简介,在本次学习之前，各人要对VBA编程基础有一定程度的了解、学习。 在后面的实例中，我们将可以学习VBA程序对SQL Server数据库的简单操作，对Excel中的表格进行边框、背景颜色、文字大小、文字类型、文字格式等等一系列的设置更改。,引用类,主过程,Sub Main() unify Sheets(“X-SELL“), _ “SELECT * FROM AO_CN.hcc_risk.V_CASH_CX ORDER BY PROD_CODE“, _ “D,E,F,H,K,M,N,O,P,Q,R,S,T,U,V,W,X“, _ “L“ ThisWorkbook.Save End Sub,注意：通常在我们编写程序时，主程序应越简单越好，这样不仅自己容易维护、应用，而且能使其它用户易读懂。,注释：在过程main()中，调用子过程unify,并向unify中传递四个参数；再调用VBA内部过程Thisworkbook.Save保存电子表格(执行完成如下：)。,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,整合子过程片段,注释：定义私有过程unify，并定义四个参数sht、strSql、strPercentColums、strDecimalColumns。,调用子过程clearSht、FillData、lineJin、colorSheet、percentJin、percentJin、FontSheet、Columns。由于需要向子过程片段中传递一些相同的参数，并且每次都要执行些子过程，那么我们在编程时可以整合它们在一起，减少编译次数，节省执行时间。,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,记录表格中的数据行数,注释：这里i定义为表格的行号，列号1（A）不变，从第三行开始循环一直到65535行，如果Ai中的值为空就会跳出循环，那么行数: getRowCount=i-1,记录表格中的数据列数,Private Function getColumnCount(ByVal sht As Worksheet) As Long Dim lColumnsNum As Long Dim i As Integer For i = 1 To 255 If sht.Cells(2, i).Value = “ Then lColumnsNum = i - 1 Exit For End If Next getColumnCount = lColumnsNum End Function,注释：这里i定义为表格的列号，行号2不变，从第一列开始循环一直到255列，如果i2中的值为空就会跳出循环，那么列数: getColumnCount=i-1,清除表格中记录,Private Sub clearSht(ByVal sht As Worksheet) Dim columnChr As String columnChr = Split(Cells(1, getColumnCount(sht).Address, “$“)(1) Dim rowCnt As Long rowCnt = getRowCount(sht) sht.Range(“A3:“ & columnChr & rowCnt + 5).Clear End Sub,注释：调函数获取列号（由数字转换成字母） 获取行数 调用VBA内部过程 指定范围并清除数据,清除范围内的数据（包括格式、文字、字体、背景色、边框等等）,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;” _ ” _ & “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,操作SQL Server数据库,注释：定义过程FillData; 定义变量conn、rs、CnStr 给数据库连接串赋值 打开数据库连接 最大连接时间500s 查询并读取数据到rs 复制rs中的数据并在表sht的A3位置填充（如下图：） 关闭rs，conn 释放连接conn在内存中所占的空间,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,i=7添加左边框,添加边框,填充背景色,Private Sub colorSheet(ByVal sht As Worksheet) Dim columnChr As String columnChr = Split(Cells(1, getColumnCount(sht).Address, “$“)(1) Dim i As Long Dim rowCnt As Long rowCnt = getRowCount(sht) For i = 3 To rowCnt If i Mod 2 = 0 Then sht.Range(“A“ & i & “:“ & columnChr & i).Interior.Color = RGB(240, 246, 206) End If Next End Sub,注释：填充背景色。色彩由RGB红、绿、蓝三色配比而成，单色的最大值是255。在此实例中为隔行填充颜色，如下：,修改字体,Private Sub FontSheet(ByVal sht As Worksheet, _ ByVal fontStyle As String, ByVal fontSize As Integer) Dim columnChr As String columnChr = Split(Cells(1, getColumnCount(sht).Address, “$“)(1) Dim rowCnt As Long rowCnt = getRowCount(sht) sht.Range(“A3:” & columnChr & rowCnt).Select 选择范围 With Selection.Font .Name = fontStyle 字体名称（传递参数） .Size = fontSize 字体大小（传递参数） .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With End Sub,在该实例中字体名为 Calibri，大小为10。,修改文字格式,Private Sub percentJin(ByVal sht As Worksheet, _ ByVal strColumns As String, ByVal strFormat As String) sht.Activate Dim lRowsCount As Long lRowsCount = getRowCount(sht) Dim x As Variant Dim obj As Variant x = Split(strColumns, “,“) For Each obj In x sht.Range(obj & “3:“ & obj & lRowsCount).Select sht.Ran