Excel自動(dòng)獲取外部數(shù)據(jù)

1、*采用 Visual Basic 應(yīng)用程序版開發(fā)了一套 “通用報(bào)表分析系統(tǒng)” (界面如圖 1)。 該系統(tǒng)用于擁有 眾多子公司的母公司的每月財(cái)務(wù)報(bào)表合并匯總。所有子公司的統(tǒng)計(jì)報(bào)表如資產(chǎn)負(fù)債表、損益 表是由 FoxBase 編制的財(cái)務(wù)軟件生成的 dbf 文件， 取名為 ATV001xx.dbfxx 月份資產(chǎn)負(fù)債表，ATV002xx.dbfxx 月份損益表等。一個(gè)子公司的所有dbf 文件放在一個(gè)單獨(dú)的目錄中，如C:Tpalm1 ， C:Tpalm2 等。母公司每月份生成的匯總報(bào)表為 TTTyymm.xls(yy 年份， mm月份)，它有“資產(chǎn)負(fù)債表”、 “損益表”等若干工作表組成。每張工作表是由所有

2、子公司相應(yīng)的 dbf 文件的相應(yīng)項(xiàng)目的數(shù)據(jù)相加而成。 只要將 dbf 文件逐一轉(zhuǎn)化到 TTTyymm.xls 中去， 很容 易利用 Excel 的公式設(shè)置功能生成母公司的每張匯總報(bào)表。 這套系統(tǒng)的關(guān)鍵在于如何將所有dbf 文件轉(zhuǎn)換到同一個(gè)Excel 工作簿中。直接通過“文件”菜單中的“打開”項(xiàng), 選擇文件類型為 dBase 文件( *.dbf ) , 可將 dbf 文件轉(zhuǎn)換到 Excel 工作簿中 ,但這工作簿只存轉(zhuǎn)換而來的一張工作表，其他表都自動(dòng)關(guān)閉了。另外,通過“工具”菜單中的“向?qū)А?，選擇“文件轉(zhuǎn)換”后， 只是將一系列 dbf 文件轉(zhuǎn)換為一系列 xls 文件而已。于是 采用建立 ODB

3、C 數(shù)據(jù)源獲取外部數(shù)據(jù)的辦法, 將 dbf 文件逐一轉(zhuǎn)換到一個(gè)Excel 工作簿內(nèi) , 且用 Visual Basic for Application 將轉(zhuǎn)換過程自動(dòng)化。只要按一下圖 1 中的“生成報(bào)表”按鈕, 就能完成所有dbf 文件的轉(zhuǎn)換 , 且利用 Excel 公式自動(dòng)計(jì)算功能完成所有報(bào)表的匯總計(jì)算。 按“顯示報(bào)表”按鈕，選擇表名，可以瀏覽報(bào)表數(shù)據(jù)。 具體的方法是： 一、 建立 ODBC 數(shù)據(jù)源(1) 打開“數(shù)據(jù)”菜單, 選擇“獲取外部數(shù)據(jù)” , 然后單擊“新建查詢” ;(2) 在“選擇數(shù)據(jù)源”對(duì)話框中 , 雙擊“<新數(shù)據(jù)源>” ;(3) 出現(xiàn)“創(chuàng)建新數(shù)據(jù)源”對(duì)話框 , 輸入

4、數(shù)據(jù)源名稱, 選擇驅(qū)動(dòng)程序如 Microsoft dBaseDriver(*.dbf), 單擊“連接” ;(4) 在“ ODBC dBase 安裝” 對(duì)話框中 , 單擊 “使用當(dāng)前工作目錄” 前的復(fù)選框 , 去掉缺省 ( , 單 擊“選定目錄(s)” , 選擇子公司存放 dbf 文件的目錄如 C:Tpalm1, 連按“確定” ；(5) 當(dāng)出現(xiàn) Microsoft Quary 對(duì)話框時(shí) , 單擊“關(guān)閉” , 退出。不要理會(huì)出現(xiàn)的警示信息，因?yàn)榇藭r(shí)只需建立數(shù)據(jù)源 , 并不需要用 Microsoft Query 查詢數(shù)據(jù) ;(6) 重復(fù)上述步驟 , 在 (4) 中改換另一家子公司的目錄 , 就為另一

5、家子公司建立一個(gè)數(shù)據(jù)源。必 須建立所有子公司的數(shù)據(jù)源。二、手動(dòng)獲取外部數(shù)據(jù)(1) 單擊“數(shù)據(jù)” ，選取“獲取外部數(shù)據(jù)”，單擊“新建查詢” ；(2) 出現(xiàn)“選取數(shù)據(jù)源”對(duì)話框，點(diǎn)中“使用查詢向?qū)?chuàng)建/ 編輯查詢”前的復(fù)選框，然后雙擊數(shù)據(jù)源名，如 palm1;(3)在“查詢向?qū)б灰贿x擇列”對(duì)話框中選擇一個(gè)查詢表名，單擊 > 鍵，"查詢中用到的列”框內(nèi)會(huì)出現(xiàn)表中所有列名，單擊“下一步” ；(4)出現(xiàn)“查詢向?qū)б灰贿^濾數(shù)據(jù)”，單擊“下一步”；(5)出現(xiàn)“查詢向?qū)б灰慌判蝽樞颉?，單擊“下一步”?6) 出現(xiàn) “查詢向?qū)瓿伞?， 點(diǎn)中 “將數(shù)據(jù)返回 Microsoft Excel ” 前

6、的單選鈕， 單擊 “完成” ；(7) 出現(xiàn)“將外部數(shù)據(jù)返回到 Excel ”對(duì)話框，選中“新建工作表” ，按“確定” ；(8) 在建立查詢的工作簿內(nèi)新建工作表，并放入轉(zhuǎn)換好的數(shù)據(jù)。這樣就將一個(gè)dbf 文件轉(zhuǎn)換好了。4(9)重復(fù)上述過程，所有子公司的 dbf 文件轉(zhuǎn)換到同一個(gè)工作簿中。(1) 名”的編輯框中鍵入宏名(2)(3)(4)使用 VB 實(shí)現(xiàn) Excel進(jìn)行手動(dòng)獲取外部數(shù)據(jù)的編輯框中鍵入宏名完成手動(dòng)獲取外部數(shù)據(jù)單擊“工具”菜單中的自動(dòng)獲取外部數(shù)據(jù)(1) 步驟前， 單擊 “工具” 菜單中的 “宏” ， 選擇 “錄制新宏” ， 在“宏dbftoxls, 按“確定”鍵；(1)-(8) 步驟；“宏

7、” ， 選擇 “停止錄制” 。 這樣就將獲取外部數(shù)據(jù)的過程記錄為宏。編輯 dbftoxls 宏， 加以修改， 使它作為 Visual Basic 模塊表中的一個(gè)子程序， 并設(shè)置調(diào)用參數(shù)。提供的程序如下： 、設(shè)置初值Const apppath = "c:my documentspalmxls"Const modulefile = apppath + "module.xls"Const staticspre = "TTT"Const dbfpre = "ATV00"、調(diào)用dbftoxls的模塊Private Sub C

8、mdgeneratetable_Click()Dim staticsfile As StringDim s1 As StringDim s2 As StringDim s3 As StringDim idyes As IntegerDim dbfstring As StringOn Error GoTo errhandler1 idyes = 651 = txtyear.Texts1 = Mid(s1, 3, 2)52 = txtmonth.TextIf Len(s2) = 1 Thens2 = "0" + s2End Ifstaticsfile = apppath + s

9、taticspre + s1 + s2 + ".xls"If FileLen(staticsfile) > 0 Then", vbYesNo + vbExclamation +choice = MsgBox(" 該年月報(bào)表已存在，是否重新生成？vbDefaultButton1, "")If choice = idyes ThenWorkbooks.Open FileName:=staticsfileFor i = 0 To companynum - 1For j = 0 To tablenum - 1dbfstring = db

10、fpre + Trim(Str$(j + 1) + s2sqlstring = sqlstringfunc(dbfstring, fieldlist(), tablefieldnum(j)Call dbftoxls(s(i, j), sqlstring)Next j Next iActiveWorkbook.SaveActiveWorkbook.CloseEnd If End IfExit Suberrhandler1:Select Case Err Case 53Workbooks.Open FileName:=modulefile s3 = s1 + "年" + s2

11、+ "月"Sheets("資產(chǎn)負(fù)債表”).Range("e4").FormulaR1C1 =皿 + s3 ActiveWorkbook.SaveAs FileName:=staticsfile, FileFormat _ :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _ False, CreateBackup:=FalseFor i = 0 To companynum - 1For j = 0 To tablen

12、um - 1dbfstring = dbfpre + Trim(Str$(j + 1) + s2sqlstring = sqlstringfunc(dbfstring, fieldlist(), tablefieldnum(j)Call dbftoxls(s(i, j), sqlstring)Next j Next iActiveWorkbook.SaveActiveWorkbook.CloseEnd SelectEnd Sub'dbftoxls子程序Sub dbftoxls(activesheetname, sqlstring)Sheets(activesheetname).Acti

13、vateCells.SelectSelection.ClearRange("a1").SelectWith ActiveSheet.QueryTables.Add(Connection:=Array(Array( _ "ODBC;CollatingSequence=ASCII;DBQ=C:Tpalm1;DefaultDir=C:T palm1;Deleted=1;Driver=Microsoft dBase Driver (*.dbf);DriverId=533;FIL" _ ), Array( _"=dBase III;ImplicitCommitSync=Yes;MaxBufferSize=512;MaxScanRows= 8;PageTimeout=600;SafeTransactions=0;Statistics=0;Threads=3;Use" _ ), Array("rCommitSync=Yes;"), Destination:=Range("A1").Sql = Array( sqlstring).FieldNames = True.RefreshStyle = xlInsertDeleteCells