Excel培訓(xùn) VBA操作數(shù)據(jù)庫(kù)實(shí)例課件

VBA入門范例精解Excel培訓(xùn)VBA操作數(shù)據(jù)庫(kù)實(shí)例范例簡(jiǎn)介在本次學(xué)習(xí)之前，各人要對(duì)VBA編程基礎(chǔ)有一定程度的了解、學(xué)習(xí)。

在后面的實(shí)例中，我們將可以學(xué)習(xí)VBA程序?qū)QLServer數(shù)據(jù)庫(kù)的簡(jiǎn)單操作，對(duì)Excel中的表格進(jìn)行邊框、背景顏色、文字大小、文字類型、文字格式等等一系列的設(shè)置更改。Excel培訓(xùn)VBA操作數(shù)據(jù)庫(kù)實(shí)例引用類引用一個(gè)連接數(shù)據(jù)庫(kù)的類Excel培訓(xùn)VBA操作數(shù)據(jù)庫(kù)實(shí)例主過(guò)程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注意：通常在我們編寫程序時(shí)，主程序應(yīng)越簡(jiǎn)單越好，這樣不僅自己容易維護(hù)、應(yīng)用，而且能使其它用戶易讀懂。注釋：在過(guò)程main()中，調(diào)用子過(guò)程unify,并向unify中傳遞四個(gè)參數(shù)；再調(diào)用VBA內(nèi)部過(guò)程Thisworkbook.Save保存電子表格(執(zhí)行完成如下：)。Excel培訓(xùn)VBA操作數(shù)據(jù)庫(kù)實(shí)例PrivateSubunify(ByValshtAsWorksheet,_ByValstrSqlAsString,_ByValstrPercentColumnsAsString,_ByValstrDecimalColumnsAsString)

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

EndSub整合子過(guò)程片段注釋：定義私有過(guò)程unify，并定義四個(gè)參數(shù)sht、strSql、strPercentColums、strDecimalColumns。調(diào)用子過(guò)程clearSht、FillData、lineJin、colorSheet、percentJin、percentJin、FontSheet、Columns。由于需要向子過(guò)程片段中傳遞一些相同的參數(shù)，并且每次都要執(zhí)行些子過(guò)程，那么我們?cè)诰幊虝r(shí)可以整合它們?cè)谝黄穑瑴p少編譯次數(shù)，節(jié)省執(zhí)行時(shí)間。Excel培訓(xùn)VBA操作數(shù)據(jù)庫(kù)實(shí)例PrivateFunctiongetRowCount(ByValshtAsWorksheet)AsLongDimlRowsNumAsLongDimiAsLong

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

getRowCount=lRowsNum

EndFunction記錄表格中的數(shù)據(jù)行數(shù)注釋：這里i定義為表格的行號(hào)，列號(hào)1（A）不變，從第三行開始循環(huán)一直到65535行，如果Ai中的值為空就會(huì)跳出循環(huán)，那么行數(shù):getRowCount=i-1Ai=“”?i否i+1RowCount=i-1是Excel培訓(xùn)VBA操作數(shù)據(jù)庫(kù)實(shí)例記錄表格中的數(shù)據(jù)列數(shù)PrivateFunctiongetColumnCount(ByValshtAsWorksheet)AsLongDimlColumnsNumAsLongDimiAsInteger

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

getColumnCount=lColumnsNumEndFunction注釋：這里i定義為表格的列號(hào)，行號(hào)2不變，從第一列開始循環(huán)一直到255列，如果i2中的值為空就會(huì)跳出循環(huán)，那么列數(shù):getColumnCount=i-1Excel培訓(xùn)VBA操作數(shù)據(jù)庫(kù)實(shí)例清除表格中記錄PrivateSubclearSht(ByValshtAsWorksheet)

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

DimrowCntAsLongrowCnt=getRowCount(sht)

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

EndSub注釋：調(diào)函數(shù)獲取列號(hào)（由數(shù)字轉(zhuǎn)換成字母）獲取行數(shù)調(diào)用VBA內(nèi)部過(guò)程指定范圍并清除數(shù)據(jù)清除范圍內(nèi)的數(shù)據(jù)（包括格式、文字、字體、背景色、邊框等等）Excel培訓(xùn)VBA操作數(shù)據(jù)庫(kù)實(shí)例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數(shù)據(jù)庫(kù)注釋：定義過(guò)程FillData;定義變量conn、rs、CnStr給數(shù)據(jù)庫(kù)連接串賦值打開數(shù)據(jù)庫(kù)連接最大連接時(shí)間500s查詢并讀取數(shù)據(jù)到rs復(fù)制rs中的數(shù)據(jù)并在表sht的A3位置填充（如下圖：）關(guān)閉rs，conn釋放連接conn在內(nèi)存中所占的空間Excel培訓(xùn)VBA操作數(shù)據(jù)庫(kù)實(shí)例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添加內(nèi)垂直線i=12添加內(nèi)水平線添加邊框Excel培訓(xùn)VBA操作數(shù)據(jù)庫(kù)實(shí)例填充背景色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紅、綠、藍(lán)三色配比而成，單色的最大值是255。在此實(shí)例中為隔行填充顏色，如下：Excel培訓(xùn)VBA操作數(shù)據(jù)庫(kù)實(shí)例修改字體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 ‘字體名稱（傳遞參數(shù)）.Size=fontSize ‘字體大?。▊鬟f參數(shù)）.Strikethrough=False.Superscript=False.Subscript=False.OutlineFont=False.Shadow=False.Underline=xlUnderlineStyleNone.ThemeColor=xlThemeColorLight1.TintAndShade=0.ThemeFont=xlThemeFontNoneEndWith

EndSub在該實(shí)例中字體名為Calibri，大小為10。Excel培訓(xùn)VBA操作數(shù)據(jù)庫(kù)實(shí)例修改文字格式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