Access與Excel結(jié)合使用常用代碼

1、Excel 使用靈活但是處理數(shù)據(jù)量小(數(shù)據(jù)一多就會(huì)卡)Access 能數(shù)據(jù)量大(相對(duì)于 Excel)但是使用不靈活。Excel 與 Access 結(jié)合使用能相互彌補(bǔ)不足。如用 Access 用為后臺(tái)數(shù)據(jù)庫(kù)對(duì)表進(jìn)行篩選、匯總等一系列處理后再以 Excel 為前臺(tái)導(dǎo)出數(shù)據(jù)，會(huì)比直接用 Excel 表刷選、匯總一個(gè)上萬(wàn)行的數(shù)據(jù)表快的多。以下是幾種 access 與 Excel 結(jié)合使用常用方法與代碼。Access 導(dǎo)入表格代碼Sub導(dǎo)入材料出庫(kù)表()DoCmd.SetWarnings False取消警告DoCmd.RunSQL DELETE FROM 材料出庫(kù)表刪除材料出庫(kù)表DoCmd.Transf

2、erSpreadsheet acImport, 8, 材料出庫(kù)表, _“F:材料出庫(kù)明細(xì).xls, _True, sheet1!a2:ag20000導(dǎo)入材料出庫(kù)明細(xì)表中的 sheet1 表 a2:ag20000 區(qū)域的數(shù)據(jù)DoCmd.SetWarnings TrueEnd Sub恢復(fù)警告注： DoCmd.TransferSpreadsheet acImport, 8, 材料出庫(kù)表 , “F:材料出庫(kù)明細(xì) .xls, True, sheet1!a2:ag20000 此句代碼中的材料出庫(kù)表是表明導(dǎo)入到 Access 中的材料出庫(kù)表中， 參數(shù) true 表明以導(dǎo)入數(shù)據(jù)的第一行作為列字段如將 true

3、 改 false 數(shù)據(jù)導(dǎo)入 access 中后表的列字段為“F1、F2、F3. ”。創(chuàng)建 Access 窗體與按鈕創(chuàng)建窗體點(diǎn)創(chuàng)建窗體設(shè)計(jì)即調(diào)出窗體設(shè)計(jì)窗體界面。創(chuàng)建按鈕點(diǎn)設(shè)計(jì)按扭即可用鼠標(biāo)在窗體中插入按鈕。按鈕做好后會(huì)自動(dòng)彈出“命令按鈕向?qū)А敝苯狱c(diǎn)取消。 右擊按鈕事件生成器代碼生成器即進(jìn)入此按鈕的代碼編輯區(qū)。將光標(biāo)放到 Private Sub Command3_Click() 下面粘貼以下代碼DoCmd.SetWarnings FalseDoCmd.RunSQL DELETE FROM 材料出庫(kù)表 DoCmd.TransferSpreadsheet acImport, 8, 材料出庫(kù)表, _

4、“F:材料出庫(kù)明細(xì).xls, _True, sheet1!a2:ag20000DoCmd.SetWarnings True關(guān) 閉 代 碼 界 面 回 到 窗 體 設(shè) 計(jì) 界 面 ， 點(diǎn) 右 下 角 窗 體 視 圖 ,再點(diǎn)剛做好的按鈕，“2成本ERP 導(dǎo)出表導(dǎo)入 access材料出庫(kù)明細(xì).xls”的數(shù)據(jù)就會(huì)導(dǎo)入到 access 中的材料出庫(kù)表中，將不同的代代碼放入按鈕中就能實(shí)現(xiàn)不同功能的按鈕。Access 導(dǎo)出到 Excel 表Access 導(dǎo)出 Excel 表方法很多(最簡(jiǎn)單的方法直接在 Access 點(diǎn)導(dǎo)出)這里介紹以 Excel作為前臺(tái) Access 作為后臺(tái)取數(shù)據(jù)的

5、方法。新建一個(gè) Excel 打開 Alt+F11 打開VBE 窗口點(diǎn)右鍵插入模塊這時(shí)會(huì)彈出模塊 1 的界面并且光標(biāo)在此界面閃。將以下代碼貼入此模塊，按F5 即可運(yùn)行此模塊就可從 Access 導(dǎo)出數(shù)據(jù)(注運(yùn)行時(shí)出現(xiàn)警告“用戶類型未定義”這是沒有對(duì) Microsoft ActiveX Data Object X Library 2.8 引用點(diǎn)工具引用將 Microsoft ActiveX Data Object X Library2.8 打下勾即可)Sub 導(dǎo)出材料匯總()Dim mydata$, SQL$, hh% Dim rs As ADODB.RecordsetDim cnn As ADO

6、DB.Connection Range(d2:l10000).Clearmydata = 2成本ERP 導(dǎo)出表基礎(chǔ)數(shù)據(jù).accdb Set cnn = New ADODB.ConnectionWith cnn.Provider = Microsoft.ACE.OLEDB.12.0.Open mydata End WithSQL = SELECT DISTINCT 材料出庫(kù)表.工單號(hào), 材料出庫(kù)表.倉(cāng)庫(kù), 材料出庫(kù)表.領(lǐng)料部門, 材料出庫(kù)表.物料類型, _& 材料出庫(kù)表.物料名稱, 材料出庫(kù)表.單位, Sum(材料出庫(kù)表.實(shí)發(fā)數(shù)量) AS 實(shí)發(fā)數(shù)量之總計(jì), Sum(材料出庫(kù)

7、表.金額) _& AS 金額之總計(jì), 材料出庫(kù)表.領(lǐng)料用途 FROM 材料出庫(kù)表 GROUP BY 材料出庫(kù)表. 領(lǐng)料部門, 材料出庫(kù)表.倉(cāng)庫(kù), 材料出庫(kù)表.工單號(hào), _& 材料出庫(kù)表.物料類型, 材料出庫(kù)表.物料名稱,材料出庫(kù)表.單位, 材料出庫(kù)表.領(lǐng)料用途Set rs = cnn.Execute(SQL)With Range(Cells(1, 1), Cells(1, rs.Fields.Count).HorizontalAlignment = xlCenter End WithRange(d2).CopyFromRecordset rs Range(A1:l10000).Font.Siz

8、e = 10 Columns(g:k).Style = Comma rs.Closecnn.CloseSet rs = Nothing Set cnn = NothingEnd Sub注：Set cnn = New ADODB.Connection With cnn.Provider = Microsoft.ACE.OLEDB.12.0.Open mydata End With這段代碼是建立與 Access 文件的連接。(access 文件不用打開通過后臺(tái)連接 Access 的數(shù)據(jù))SQL = SELECT DISTINCT 材料出庫(kù)表.工單號(hào), 材料出庫(kù)表.倉(cāng)庫(kù), 材料出庫(kù)表.領(lǐng)料部門, 材

9、料出庫(kù)表.物料類型, _& 材料出庫(kù)表.物料名稱, 材料出庫(kù)表.單位, Sum(材料出庫(kù)表.實(shí)發(fā)數(shù)量) AS 實(shí)發(fā)數(shù)量之總計(jì), Sum(材料出庫(kù)表.金額) _& AS 金額之總計(jì), 材料出庫(kù)表.領(lǐng)料用途 FROM 材料出庫(kù)表 GROUP BY 材料出庫(kù)表. 領(lǐng)料部門, 材料出庫(kù)表.倉(cāng)庫(kù), 材料出庫(kù)表.工單號(hào), _& 材料出庫(kù)表.物料類型, 材料出庫(kù)表.物料名稱,材料出庫(kù)表.單位, 材料出庫(kù)表.領(lǐng)料用途這段代碼是 SQL 查詢命令，修改這段代碼可將各種所需的表導(dǎo)出。代碼 Range(d2).CopyFromRecordset rs 是表導(dǎo)出的起始位置，Range(d2)就是以 d2 單元格開始如改 Range(“a2”)則從 a2 單元格開始。復(fù)雜的 SQL 查詢語(yǔ)句可以在