《Excel培训 VBA操作数据库实例》由会员分享，可在线阅读，更多相关《Excel培训 VBA操作数据库实例（14页珍藏版）》请在金锄头文库上搜索。
1、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“ThisWorkboo
2、k.SaveEnd 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
3、FillData sht, strSqllineJin shtcolorSheet shtpercentJin sht, strPercentColumns, “0.00%“percentJin sht, strDecimalColumns, “0.00“FontSheet sht, “Calibri“, 10Columns(“A:Z“).AutoFitEnd Sub整合子过程片段注释：定义私有过程unify，并 定义四个参数sht、strSql、 strPercentColums、 strDecimalColumns。调用子过程clearSht、 FillData、lineJin、 colo
4、rSheet、percentJin、 percentJin、FontSheet、 Columns。由于需要向子过 程片段中传递一些相同的参 数，并且每次都要执行些子 过程，那么我们在编程时可 以整合它们在一起，减少编 译次数，节省执行时间。Private Function getRowCount(ByVal sht As Worksheet) As LongDim lRowsNum As LongDim i As LongFor i = 3 To 65535If sht.Cells(i, 1).Value = “ ThenlRowsNum = i - 1Exit ForEnd IfNextge
5、tRowCount = lRowsNumEnd Function记录表格中的数据行数注释：这里i定义为表格的行号，列号1（A）不变，从第三行开始循环一直 到65535行，如果Ai中的值为空就会跳出循环，那么行数: getRowCount=i-1Ai=“”?i否i+1RowCount=i-1是记录表格中的数据列数Private Function getColumnCount(ByVal sht As Worksheet) As LongDim lColumnsNum As LongDim i As IntegerFor i = 1 To 255If sht.Cells(2, i).Value =
6、 “ ThenlColumnsNum = i - 1Exit ForEnd IfNextgetColumnCount = lColumnsNum End Function注释：这里i定义为表格的列号，行号2不变，从第一列开始循环一直到255 列，如果i2中的值为空就会跳出循环，那么列数: getColumnCount=i-1清除表格中记录Private Sub clearSht(ByVal sht As Worksheet)Dim columnChr As StringcolumnChr = Split(Cells(1, getColumnCount(sht).Address, “$“)(1)
7、Dim rowCnt As LongrowCnt = getRowCount(sht)sht.Range(“A3:“ ” _ ” _定义变量conn、rs、CnStr给数据库连接串赋值打开数据库连接 最大连接时间500s 查询并读取数据到rs复制rs中的数据并在表sht的A3位置填充（如下图： ） 关闭rs，conn释放连接conn在内存中所占的空间Private Sub lineJin(ByVal sht As Worksheet) 添加边框Dim rng As RangeDim strTemp As StringDim i As Integersht.ActivatestrTemp = S
8、plit(Cells(1, getColumnCount(sht).Address, “$“)(1) & getRowCount(sht)Set rng = sht.Range(“A3:“ & strTemp)rng.Select 选中需要边框的范围For i = 7 To 12 Step 1With Selection.Borders(i).LineStyle = xlContinuous.ColorIndex = 0.TintAndShade = 0.Weight = xlThinEnd WithNext iSet rng = NothingEnd Subi=7添加左边框i=8添加顶边框i
9、=9添加底边框i=10添加右边框i=11添加内垂直线i=12添加内水平线添加边框填充背景色Private Sub colorSheet(ByVal sht As Worksheet)Dim columnChr As StringcolumnChr = Split(Cells(1, getColumnCount(sht).Address, “$“)(1)Dim i As LongDim rowCnt As LongrowCnt = getRowCount(sht)For i = 3 To rowCntIf i Mod 2 = 0 Thensht.Range(“A“ & i & “:“ & col
10、umnChr & i).Interior.Color = RGB(240, 246, 206)End IfNextEnd Sub注释：填充背景色。色 彩由RGB红、绿、蓝三 色配比而成，单色的最 大值是255。在此实例 中为隔行填充颜色，如 下：修改字体Private Sub FontSheet(ByVal sht As Worksheet, _ ByVal fontStyle As String, ByVal fontSize As Integer)Dim columnChr As StringcolumnChr = Split(Cells(1, getColumnCount(sht).Ad
11、dress, “$“)(1)Dim rowCnt As LongrowCnt = 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 = xlUnderlineSty
12、leNone.ThemeColor = xlThemeColorLight1.TintAndShade = 0.ThemeFont = xlThemeFontNoneEnd WithEnd Sub在该实例中字体名为 Calibri ，大小为10。修改文字格式Private Sub percentJin(ByVal sht As Worksheet, _ ByVal strColumns As String, ByVal strFormat As String)sht.ActivateDim lRowsCount As LonglRowsCount = getRowCount(sht)Dim x
13、 As VariantDim obj As Variantx = Split(strColumns, “,“)For Each obj In xsht.Range(obj & “3:“ & obj & lRowsCount).Selectsht.Range(obj & lRowsCount).Activate Selection.Style = “Percent“文字格式名Selection.NumberFormat = strFormat 数字格式Nextsht.Range(obj & “2:“ & obj & 2).SelectEnd Sub例：E、F列格式名为percent，数字格式为0.00%例：L列格式名为percent，数字格式为0.00
