access如何讀取外部數(shù)據(jù)加密的excel數(shù)據(jù)
用access自動獲取excel里的數(shù)據(jù)的方法就是通過代碼的方式導(dǎo)入。如果是對手動導(dǎo)入比較清楚的話，這個實現(xiàn)起來不難,。請參考下面的介紹來實現(xiàn)：首先，新建一個工程,，在工程中引用如下對象ADO對象（用于連接ACCESS數(shù)據(jù)庫，可用其它方式）添加一個窗體（from1），在窗體上添加如下控件：兩個文本框，用來顯示excel文件路徑和ACCESS路徑,；四個扭鈕，兩個用來游覽,，另兩個是導(dǎo)入和退出,；兩個通用對話框控件，用來打開ACCESS和EXCEL文件,，一個進(jìn)度條控件，用來顯示導(dǎo)入進(jìn)程,。該實例的導(dǎo)入是將ACCESS數(shù)據(jù)庫中表的字段名單獨存放在另外一個表中,導(dǎo)入時按表中所存字段名的順序進(jìn)行導(dǎo)入,不是按EXCEL表的順序?qū)?請大家注意.這樣在實際中是很實用的.因為好多時候EXCEL表中字段順序和ACCESS中字段順序有可能不是一致的.代碼如下：Dim vOption ExplicitPrivate Sub cmdLoad_Click()Dim excel_app As ObjectDim excel_sheet As ObjectIf txtexcelFile.Text = "" ThenMsgBox "請選擇EXCEL表"ElseDim new_value As StringLabel2.Caption = "正在導(dǎo)入,請稍候..."Screen.MousePointer = vbHourglassDoEvents'' Create the excel application.Set excel_app = CreateObject("excel.Application")'' Uncomment this line to make excel visible.excel_app.Visible = True'' Open the excel spreadsheet.excel_app.Workbooks.open FileName:=txtexcelFile.Text'' Check for later versions.If Val(excel_app.Application.Version) >= 8 ThenSet excel_sheet = excel_app.ActiveSheetElseSet excel_sheet = excel_appEnd IfDim u ''求EXCEL表中記錄的條數(shù),以便控制進(jìn)度條u = 1DoIf Trim$(excel_sheet.Cells(u, 1)) = "" Then Exit Dou = u + 1Loopbar.Max = u - 1strSQL = "select * from TestValues"yourRecord.open strSQL, myConn, adOpenDynamic, adLockOptimistic ''打開記錄集Dim sql As Stringsql = "select * from fields order by xue"myRecord.open sql, myConn, adOpenDynamic, adLockBatchOptimistic ''打開字段記錄集myRecord.MoveFirst'' Get data from the excel spreadsheet and insert'' it into the TestValues table.Dim v ''導(dǎo)入記錄,用了兩層循環(huán)v = 1DoIf Trim$(excel_sheet.Cells(v, 1)) = "" Then Exit Do ''外層,如果EXCEL表中讀取到空行,結(jié)束yourRecord.AddNewDim iFor i = 1 To myRecord.RecordCount'' Get the next value.new_value = Trim$(excel_sheet.Cells(v, i))'' See if it''s blank.''If Len(new_value) = 0 Then Exit Do'' Insert the value into the database.Dim bb As Stringbb = myRecord("name")yourRecord(bb) = new_valuemyRecord.MoveNextNext ibar.Value = vv = v + 1myRecord.MoveFirstLoopyourRecord.Update'' Comment the rest of the lines to keep'' excel running so you can see it.'' Close the workbook without saving.excel_app.ActiveWorkbook.Close False'' Close excel.excel_app.QuitSet excel_sheet = NothingSet excel_app = NothingmyRecord.CloseyourRecord.CloseSet myRecord = NothingSet yourRecord = NothingLabel2.Caption = "導(dǎo)入完畢"Screen.MousePointer = vbDefaultMsgBox "共導(dǎo)入" & Format$(v - 1) & "條記錄"End IfEnd SubPrivate Sub Command1_Click()Unload MeEnd SubPrivate Sub Command2_Click(Index As Integer)''尋找ACCESS數(shù)據(jù)庫CommonDialog1.Filter = "ACCESS 文件(*.mdb)|*.mdb"CommonDialog1.CancelError = TrueCommonDialog1.ShowOpentxtAccessFile.Text = CommonDialog1.FileNameEnd SubPrivate Sub Command3_Click()''尋找excel數(shù)據(jù)庫CommonDialog2.Filter = "excel 文件(*.xls)|*.xls"CommonDialog2.CancelError = TrueCommonDialog2.ShowOpentxtexcelFile.Text = CommonDialog2.FileNameEnd SubPrivate Sub Form_Load()Call Module1.lianjietxtAccessFile.Text = datapathEnd Sub模塊（module1）中的代碼如下：Public myConn As New ADODB.Connection ''定義連接字符串Public myRecord As New ADODB.Recordset ''定義記錄集(字段)Public yourRecord As New ADODB.Recordset ''定義記錄集(數(shù)據(jù))Public cntoad As Boolean ''是否正常連接Public ml ''姓名字段所在列Public strSQL ''查詢字符串Public MyDatabase As Database ''定義數(shù)據(jù)庫名Public MyTable As TableDef, MyField As Field ''定義表名和字段名Public xuehao ''讀取字段序號Public goshiRecord As New ADODB.Recordset ''定義記錄集(公式)Public hxfyn As BooleanPublic hxfbds '' 公式或條件Public duan ''要統(tǒng)計的字段Public islinshi As Boolean ''是否為臨時公式Public leiRecord As New ADODB.Recordset ''定義記錄集(工資類別)Public datapath As String ''數(shù)據(jù)庫路徑及名Public table As String ''工資表名Public lei As String '' 工資類別Public Sub lianjie() ''打開數(shù)據(jù)庫On Error Resume NextmyConn.CloseDim mysql As String''設(shè)定連接字符串mysql = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;"mySQL = mySQL + "Data Source=" & datapathmyConn.ConnectionString = mySQL''設(shè)定連接myConn.open ''打開連接myRecord.ActiveConnection = myConn''設(shè)定RecordSeet的連接對象為ConnectionmyRecord.CursorLocation = adUseClientgoshiRecord.ActiveConnection = myConn''設(shè)定RecordSeet的連接對象為ConnectiongoshiRecord.CursorLocation = adUseClientyourRecord.ActiveConnection = myConn''設(shè)定RecordSeet的連接對象為ConnectionyourRecord.CursorLocation = adUseClientEnd Sub