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.Range(obj&lRowsCount).Activate Selection.Style=“Percent“文字格式名 Selection.NumberFormat=strFormat 数字格式 Next sht.Range(obj&2:&obj&2).Select End Sub,例：E、F列格式名为percent，数字格式为0.00%,例：L列格式名为percent，数字格式为0.00,Thank You!,