用access自動獲取excel裡的資料的方法就是透過程式碼的方式匯入。如果是對手動匯入比較清楚的話，這個實現起來不難。請參考下面的介紹來實現：首先，新建一個工程，在工程中引用如下物件ADO物件（用於連線ACCESS資料庫，可用其它方式） 新增一個窗體（from1），在窗體上新增如下控制元件： 兩個文字框，用來顯示EXCEL檔案路徑和ACCESS路徑； 四個扭鈕，兩個用來遊覽，另兩個是匯入和退出； 兩個通用對話方塊控制元件，用來開啟ACCESS和EXCEL檔案，一個進度條控制元件，用來顯示匯入程序。 該例項的匯入是將ACCESS資料庫中表的欄位名單獨存放在另外一個表中,匯入時按表中所存欄位名的順序進行匯入,不是按EXCEL表的順序匯入,請大家注意.這樣在實際中是很實用的.因為好多時候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 = "正在匯入,請稍候..." Screen.MousePointer = vbHourglass DoEvents "" 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 Then Set excel_sheet = excel_app.ActiveSheet Else Set excel_sheet = excel_app End If Dim u ""求EXCEL表中記錄的條數,以便控制進度條u = 1Do If Trim$(excel_sheet.Cells(u, 1)) = "" Then Exit Do u = u + 1 Loop bar.Max = u - 1 strSQL = "select * from TestValues" yourRecord.open strSQL, myConn, adOpenDynamic, adLockOptimistic ""開啟記錄集 Dim sql As String sql = "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 ""匯入記錄,用了兩層迴圈 v = 1 Do If Trim$(excel_sheet.Cells(v, 1)) = "" Then Exit Do ""外層,如果EXCEL表中讀取到空行,結束yourRecord.AddNew Dim i For 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 String bb = myRecord("name") yourRecord(bb) = new_value myRecord.MoveNext Next i bar.Value = v v = v + 1 myRecord.MoveFirst Loop yourRecord.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.Quit Set excel_sheet = Nothing Set excel_app = Nothing myRecord.CloseyourRecord.CloseSet myRecord = NothingSet yourRecord = NothingLabel2.Caption = "匯入完畢" Screen.MousePointer = vbDefault MsgBox "共匯入" & Format$(v - 1) & "條記錄" End If End SubPrivate Sub Command1_Click()Unload MeEnd SubPrivate Sub Command2_Click(Index As Integer)""尋找ACCESS資料庫CommonDialog1.Filter = "ACCESS 檔案(*.mdb)|*.mdb"CommonDialog1.CancelError = TrueCommonDialog1.ShowOpentxtAccessFile.Text = CommonDialog1.FileNameEnd SubPrivate Sub Command3_Click()""尋找excel資料庫CommonDialog2.Filter = "excel 檔案(*.xls)|*.xls"CommonDialog2.CancelError = TrueCommonDialog2.ShowOpentxtExcelFile.Text = CommonDialog2.FileNameEnd SubPrivate Sub Form_Load()Call Module1.lianjietxtAccessFile.Text = datapath End Sub模組（module1）中的程式碼如下：Public myConn As New ADODB.Connection ""定義連線字串Public myRecord As New ADODB.Recordset ""定義記錄集(欄位)Public yourRecord As New ADODB.Recordset ""定義記錄集(資料)Public cntoad As Boolean ""是否正常連線Public ml ""姓名欄位所在列Public strSQL ""查詢字串Public MyDatabase As Database ""定義資料庫名Public MyTable As TableDef, MyField As Field ""定義表名和欄位名Public xuehao ""讀取欄位序號Public goshiRecord As New ADODB.Recordset ""定義記錄集(公式)Public hxfyn As BooleanPublic hxfbds "" 公式或條件Public duan ""要統計的欄位Public islinshi As Boolean ""是否為臨時公式Public leiRecord As New ADODB.Recordset ""定義記錄集(工資類別)Public datapath As String ""資料庫路徑及名Public table As String ""工資表名Public lei As String "" 工資類別Public Sub lianjie() ""開啟資料庫On Error Resume NextmyConn.CloseDim mySQL As String ""設定連線字串 mySQL = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;" mySQL = mySQL + "Data Source=" & datapath myConn.ConnectionString = mySQL ""設定連線 myConn.open ""開啟連線 myRecord.ActiveConnection = myConn ""設定RecordSeet的連線物件為Connection myRecord.CursorLocation = adUseClientgoshiRecord.ActiveConnection = myConn ""設定RecordSeet的連線物件為ConnectiongoshiRecord.CursorLocation = adUseClient yourRecord.ActiveConnection = myConn ""設定RecordSeet的連線物件為Connection yourRecord.CursorLocation = adUseClientEnd Sub
用access自動獲取excel裡的資料的方法就是透過程式碼的方式匯入。如果是對手動匯入比較清楚的話，這個實現起來不難。請參考下面的介紹來實現：首先，新建一個工程，在工程中引用如下物件ADO物件（用於連線ACCESS資料庫，可用其它方式） 新增一個窗體（from1），在窗體上新增如下控制元件： 兩個文字框，用來顯示EXCEL檔案路徑和ACCESS路徑； 四個扭鈕，兩個用來遊覽，另兩個是匯入和退出； 兩個通用對話方塊控制元件，用來開啟ACCESS和EXCEL檔案，一個進度條控制元件，用來顯示匯入程序。 該例項的匯入是將ACCESS資料庫中表的欄位名單獨存放在另外一個表中,匯入時按表中所存欄位名的順序進行匯入,不是按EXCEL表的順序匯入,請大家注意.這樣在實際中是很實用的.因為好多時候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 = "正在匯入,請稍候..." Screen.MousePointer = vbHourglass DoEvents "" 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 Then Set excel_sheet = excel_app.ActiveSheet Else Set excel_sheet = excel_app End If Dim u ""求EXCEL表中記錄的條數,以便控制進度條u = 1Do If Trim$(excel_sheet.Cells(u, 1)) = "" Then Exit Do u = u + 1 Loop bar.Max = u - 1 strSQL = "select * from TestValues" yourRecord.open strSQL, myConn, adOpenDynamic, adLockOptimistic ""開啟記錄集 Dim sql As String sql = "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 ""匯入記錄,用了兩層迴圈 v = 1 Do If Trim$(excel_sheet.Cells(v, 1)) = "" Then Exit Do ""外層,如果EXCEL表中讀取到空行,結束yourRecord.AddNew Dim i For 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 String bb = myRecord("name") yourRecord(bb) = new_value myRecord.MoveNext Next i bar.Value = v v = v + 1 myRecord.MoveFirst Loop yourRecord.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.Quit Set excel_sheet = Nothing Set excel_app = Nothing myRecord.CloseyourRecord.CloseSet myRecord = NothingSet yourRecord = NothingLabel2.Caption = "匯入完畢" Screen.MousePointer = vbDefault MsgBox "共匯入" & Format$(v - 1) & "條記錄" End If End SubPrivate Sub Command1_Click()Unload MeEnd SubPrivate Sub Command2_Click(Index As Integer)""尋找ACCESS資料庫CommonDialog1.Filter = "ACCESS 檔案(*.mdb)|*.mdb"CommonDialog1.CancelError = TrueCommonDialog1.ShowOpentxtAccessFile.Text = CommonDialog1.FileNameEnd SubPrivate Sub Command3_Click()""尋找excel資料庫CommonDialog2.Filter = "excel 檔案(*.xls)|*.xls"CommonDialog2.CancelError = TrueCommonDialog2.ShowOpentxtExcelFile.Text = CommonDialog2.FileNameEnd SubPrivate Sub Form_Load()Call Module1.lianjietxtAccessFile.Text = datapath End Sub模組（module1）中的程式碼如下：Public myConn As New ADODB.Connection ""定義連線字串Public myRecord As New ADODB.Recordset ""定義記錄集(欄位)Public yourRecord As New ADODB.Recordset ""定義記錄集(資料)Public cntoad As Boolean ""是否正常連線Public ml ""姓名欄位所在列Public strSQL ""查詢字串Public MyDatabase As Database ""定義資料庫名Public MyTable As TableDef, MyField As Field ""定義表名和欄位名Public xuehao ""讀取欄位序號Public goshiRecord As New ADODB.Recordset ""定義記錄集(公式)Public hxfyn As BooleanPublic hxfbds "" 公式或條件Public duan ""要統計的欄位Public islinshi As Boolean ""是否為臨時公式Public leiRecord As New ADODB.Recordset ""定義記錄集(工資類別)Public datapath As String ""資料庫路徑及名Public table As String ""工資表名Public lei As String "" 工資類別Public Sub lianjie() ""開啟資料庫On Error Resume NextmyConn.CloseDim mySQL As String ""設定連線字串 mySQL = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;" mySQL = mySQL + "Data Source=" & datapath myConn.ConnectionString = mySQL ""設定連線 myConn.open ""開啟連線 myRecord.ActiveConnection = myConn ""設定RecordSeet的連線物件為Connection myRecord.CursorLocation = adUseClientgoshiRecord.ActiveConnection = myConn ""設定RecordSeet的連線物件為ConnectiongoshiRecord.CursorLocation = adUseClient yourRecord.ActiveConnection = myConn ""設定RecordSeet的連線物件為Connection yourRecord.CursorLocation = adUseClientEnd Sub