Excel培训 VBA操作数据库实例课件

VBA入门范例精解Excel培训VBA操作数据库实例范例简介在本次学习之前，各人要对VBA编程基础有一定程度的了解、学习。

在后面的实例中，我们将可以学习VBA程序对SQLServer数据库的简单操作，对Excel中的表格进行边框、背景颜色、文字大小、文字类型、文字格式等等一系列的设置更改。Excel培训VBA操作数据库实例引用类引用一个连接数据库的类Excel培训VBA操作数据库实例主过程SubMain()unifySheets("X-SELL"),_"SELECT*FROM[AO_CN].[hcc_risk].[V_CASH_CX]ORDERBYPROD_CODE",_"D,E,F,H,K,M,N,O,P,Q,R,S,T,U,V,W,X",_"L"

ThisWorkbook.Save

EndSub注意：通常在我们编写程序时，主程序应越简单越好，这样不仅自己容易维护、应用，而且能使其它用户易读懂。注释：在过程main()中，调用子过程unify,并向unify中传递四个参数；再调用VBA内部过程Thisworkbook.Save保存电子表格(执行完成如下：)。Excel培训VBA操作数据库实例PrivateSubunify(ByValshtAsWorksheet,_ByValstrSqlAsString,_ByValstrPercentColumnsAsString,_ByValstrDecimalColumnsAsString)

clearShtshtFillDatasht,strSqllineJinshtcolorSheetshtpercentJinsht,strPercentColumns,"0.00%"percentJinsht,strDecimalColumns,"0.00"FontSheetsht,"Calibri",10Columns("A:Z").AutoFit

EndSub整合子过程片段注释：定义私有过程unify，并定义四个参数sht、strSql、strPercentColums、strDecimalColumns。调用子过程clearSht、FillData、lineJin、colorSheet、percentJin、percentJin、FontSheet、Columns。由于需要向子过程片段中传递一些相同的参数，并且每次都要执行些子过程，那么我们在编程时可以整合它们在一起，减少编译次数，节省执行时间。Excel培训VBA操作数据库实例PrivateFunctiongetRowCount(ByValshtAsWorksheet)AsLongDimlRowsNumAsLongDimiAsLong

Fori=3To65535Ifsht.Cells(i,1).Value=""ThenlRowsNum=i-1ExitForEndIfNext

getRowCount=lRowsNum

EndFunction记录表格中的数据行数注释：这里i定义为表格的行号，列号1（A）不变，从第三行开始循环一直到65535行，如果Ai中的值为空就会跳出循环，那么行数:getRowCount=i-1Ai=“”?i否i+1RowCount=i-1是Excel培训VBA操作数据库实例记录表格中的数据列数PrivateFunctiongetColumnCount(ByValshtAsWorksheet)AsLongDimlColumnsNumAsLongDimiAsInteger

Fori=1To255Ifsht.Cells(2,i).Value=""ThenlColumnsNum=i-1ExitForEndIfNext

getColumnCount=lColumnsNumEndFunction注释：这里i定义为表格的列号，行号2不变，从第一列开始循环一直到255列，如果i2中的值为空就会跳出循环，那么列数:getColumnCount=i-1Excel培训VBA操作数据库实例清除表格中记录PrivateSubclearSht(ByValshtAsWorksheet)

DimcolumnChrAsStringcolumnChr=Split(Cells(1,getColumnCount(sht)).Address,"$")(1)

DimrowCntAsLongrowCnt=getRowCount(sht)

sht.Range("A3:"&columnChr&rowCnt+5).Clear

EndSub注释：调函数获取列号（由数字转换成字母）获取行数调用VBA内部过程指定范围并清除数据清除范围内的数据（包括格式、文字、字体、背景色、边框等等）Excel培训VBA操作数据库实例SubFillData(ByValshtAsWorksheet,_ ByValSqlAsString)DimconnAsNewADODB.ConnectionDimrsAsNewADODB.RecordsetDimCnStrAsString

CnStr=“Provider=SQLOLEDB;”_ &“DataSource=;”_ &“IntegratedSecurity=SSPI"conn.OpenCnStrmandTimeout=500rs.OpenSql,conn,1

sht.[A3].CopyFromRecordsetrsrs.Closeconn.CloseSetconn=NothingEndSub操作SQLServer数据库注释：定义过程FillData;定义变量conn、rs、CnStr给数据库连接串赋值打开数据库连接最大连接时间500s查询并读取数据到rs复制rs中的数据并在表sht的A3位置填充（如下图：）关闭rs，conn释放连接conn在内存中所占的空间Excel培训VBA操作数据库实例PrivateSublineJin(ByValshtAsWorksheet)‘添加边框DimrngAsRangeDimstrTempAsStringDimiAsInteger

sht.ActivatestrTemp=Split(Cells(1,getColumnCount(sht)).Address,"$")(1)&getRowCount(sht)Setrng=sht.Range("A3:"&strTemp)

rng.Select ‘选中需要边框的范围

Fori=7To12Step1WithSelection.Borders(i).LineStyle=xlContinuous.ColorIndex=0.TintAndShade=0.Weight=xlThinEndWithNextiSetrng=Nothing

EndSubi=7添加左边框i=8添加顶边框i=9添加底边框i=10添加右边框i=11添加内垂直线i=12添加内水平线添加边框Excel培训VBA操作数据库实例填充背景色PrivateSubcolorSheet(ByValshtAsWorksheet)DimcolumnChrAsStringcolumnChr=Split(Cells(1,getColumnCount(sht)).Address,"$")(1)

DimiAsLongDimrowCntAsLongrowCnt=getRowCount(sht)Fori=3TorowCntIfiMod2=0Thensht.Range("A"&i&":"&columnChr&i).Interior.Color=RGB(240,246,206)EndIfNext

EndSub注释：填充背景色。色彩由RGB红、绿、蓝三色配比而成，单色的最大值是255。在此实例中为隔行填充颜色，如下：Excel培训VBA操作数据库实例修改字体PrivateSubFontSheet(ByValshtAsWorksheet,_ByValfontStyleAsString,

ByValfontSizeAsInteger)DimcolumnChrAsStringcolumnChr=Split(Cells(1,getColumnCount(sht)).Address,"$")(1)

DimrowCntAsLongrowCnt=getRowCount(sht)sht.Range(“A3:”&columnChr&rowCnt).Select

‘选择范围

WithSelection.Font.Name=fontStyle ‘字体名称（传递参数）.Size=fontSize ‘字体大小（传递参数）.Strikethrough=False.Superscript=False.Subscript=False.OutlineFont=False.Shadow=False.Underline=xlUnderlineStyleNone.ThemeColor=xlThemeColorLight1.TintAndShade=0.ThemeFont=xlThemeFontNoneEndWith

EndSub在该实例中字体名为Calibri，大小为10。Excel培训VBA操作数据库实例修改文字格式PrivateSubpercentJin(ByValshtAsWorksheet,_ByValstrColumnsAsString,ByValstrFormatAsString)sht.Activate

DimlRowsCountAsLonglRowsCount=getRowCount(sht)

DimxAsVariantDimobjAsVariant

x=Split(strColumns,",")

ForEachobjInxsht.Range(obj&"3:"&obj&lRowsCount).Selectsht.Range(obj&lRowsCount).ActivateSelection.Style