如何将Excel电子表格传输到Access数据库 [英] How to transfer an excel spreadsheet to an access database

问题描述

我正在编写一个程序来跟踪我的体重，一天中所吃的卡路里以及日期，以帮助我减肥.我正在将这些值手动输入到包含这三列(日期，卡路里，重量)的电子表格中.我想将这三列中的信息传输到Access数据库中.

I'm making a program to track my weight, calories I eat in a day, and the date, to help me lose weight. I'm manually putting in these values into a spreadsheet with those three columns (date, calories, weight). I want to transfer the information in these three columns into an access database.

到目前为止的代码:

Sub transferdata()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

connStr = "C:\Users\sachu\Desktop\Assignment 5\CalorieDatabase.mdb"
providerStr = "Microsoft.ACE.OLEDB.12.0"

    With cn
        .ConnectionString = connStr
        .Provider = providerStr
        .Open
    End With

rs.Open sqlStr, cn
rs.Close
cn.Close
End Sub

到目前为止，我的代码仅开始访问和excel之间的连接

So far my code is only starting the connection between access and excel

推荐答案

有很多方法可以做到这一点.让我们看几个案例研究.

There are many ways to do this. Let's look at a couple of case studies.

 Export data from Excel to Access (ADO)

如果要从Excel工作表中将数据导出到Access表，则下面的宏示例显示如何完成此操作.

If you want to export data to an Access table from an Excel worksheet, the macro example below shows how this can be done.

Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=C:\FolderName\DataBaseName.mdb;"
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable  
    ' all records in a table
    r = 3 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0 
    ' repeat until first empty cell in column A
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("FieldName1") = Range("A" & r).Value
            .Fields("FieldName2") = Range("B" & r).Value
            .Fields("FieldNameN") = Range("C" & r).Value
            ' add more fields if necessary...
            .Update ' stores the new record
        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

也..

 Export data from Excel to Access (DAO)

如果要将数据从Excel工作表导出到Access表，则下面的宏示例说明了另一种方法.

If you want to export data to an Access table from an Excel worksheet, the macro example below illustrates another way to do this.

Sub DAOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
    Set db = OpenDatabase("C:\FolderName\DataBaseName.mdb") 
    ' open the database
    Set rs = db.OpenRecordset("TableName", dbOpenTable) 
    ' get all records in a table
    r = 3 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0 
    ' repeat until first empty cell in column A
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("FieldName1") = Range("A" & r).Value
            .Fields("FieldName2") = Range("B" & r).Value
            .Fields("FieldNameN") = Range("C" & r).Value
            ' add more fields if necessary...
            .Update ' stores the new record
        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

也...

Browse to a single EXCEL File and Import Data from that EXCEL File via TransferSpreadsheet (VBA)

这是另一种方式...子TryThis()

Here's yet another way . . . Sub TryThis()

        Dim strPathFile As String
        Dim strTable As String, strBrowseMsg As String
        Dim strFilter As String, strInitialDirectory As String
        Dim blnHasFieldNames As Boolean

        ' Change this next line to True if the first row in EXCEL worksheet
        ' has field names
        blnHasFieldNames = False

        strBrowseMsg = "Select the EXCEL file:"

        ' Change C:\MyFolder\ to the path for the folder where the Browse
        ' window is to start (the initial directory). If you want to start in
        ' ACCESS' default folder, delete C:\MyFolder\ from the code line,
        ' leaving an empty string as the value being set as the initial
        ' directory
        strInitialDirectory = "C:\MyFolder\"

        strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")

        strPathFile = ahtCommonFileOpenSave(InitialDir:=strInitialDirectory, _
              Filter:=strFilter, OpenFile:=False, _
              DialogTitle:=strBrowseMsg, _
              Flags:=ahtOFN_HIDEREADONLY)

        If strPathFile = "" Then
              MsgBox "No file was selected.", vbOK, "No Selection"
              Exit Sub
        End If

        ' Replace tablename with the real name of the table into which
        ' the data are to be imported
        strTable = "tablename"

        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
              strTable, strPathFile, blnHasFieldNames

        ' Uncomment out the next code step if you want to delete the
        ' EXCEL file after it's been imported
        ' Kill strPathFile

End Sub

这篇关于如何将Excel电子表格传输到Access数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！