vba - 按列位置将 Excel 电子表格导入 MS Access

问题描述

大家好，我遇到的问题是我正在导入的文件没有列名，并且由于其他原因我无法轻松添加它们。但是，我确实知道我想要哪些列。所以我制作了一个带有列位置的表格，这样我的 vba 可以循环并逐行添加相应的字段。但我似乎得到的是变量没有被读取，它认为 FieldID 是一个列名。那么我如何让 vba 了解我希望它从中获取值的列？

Dim db As DAO.Database
Set db = CurrentDb
Dim rsImport As DAO.Recordset
Dim rsFileColumns As DAO.Recordset
Dim CSVCn As Object
Dim rsCSV As Object
Dim strSQL, strName, strName1 As String
Dim rsImporttbl As DAO.Recordset

Set rsImport = db.OpenRecordset("Select * FROM ImportFiles ORDER BY FileID ASC")
If Not (rsImport.EOF And rsImport.BOF) Then 'This Table how the directory and order in which we want to import the tables.
    rsImport.MoveFirst                      'This helps if there any directory changes or if we need to switch the sequence of import
    Do Until rsImport.EOF = True
        Directory = rsImport!Directory
        FileID = rsImport!FileID
        FileName = rsImport!FileName
        FileType = rsImport!FileType
        DbName = rsImport!DatabaseName

        Set rsFileColumns = db.OpenRecordset("Select FieldName, FieldID From ImportField WHERE FileID =" & FileID & ";") 'This Tells the program which columns we want to import
            If Not (rsFileColumns.EOF And rsFileColumns.BOF) Then 'Also if needed we do have the column numbers stored
                rsFileColumns.MoveFirst
                Do Until rsFileColumns.EOF = True
                    FieldID = rsFileColumns!FieldID
                        Set CSVCn = CreateObject("ADODB.Connection")
                        Set rsCSV = CreateObject("ADODB.Recordset")

                        strSQL1 = "Provider=" & _
                                  CurrentProject.Connection.Provider & _
                                  ";Data Source=" & Directory & _
                                  ";Extended Properties='text;HDR=YES;FMT=Delimited'"
                        CSVCn.Open strSQL1 'Tells it where to look for the CSV to import

                        strSQL = "SELECT * FROM " & FileName & FileType 'Tells what the file is called to import
                        rsCSV.Open strSQL, CSVCn, adOpenForwardOnly, adLockReadOnly, adCmdText

                        Set rsImporttbl = db.OpenRecordset(DbName, dbOpenTable, dbAppendOnly + dbFailOnError) 'This is the table its appending the data to.

                        Do Until rsCSV.EOF
                            Field1 = rsCSV!FieldID 'Using the column number stored find the value
                            rsImporttbl.AddNew
                                                'please insert it into the same field number into the databases empty verison
                            rsImporttbl.Update
                            rsCSV.MoveNext
                        Loop
                        rsImporttbl.Close
                        rsCSV.Close
                rsFileColumns.MoveNext
                Loop
            Else
                MsgBox "There are no Records in ImportFields for this table: " & FileName
            End If
            rsFileColumns.Close
            Set rsFileColumns = Nothing

    rsImport.MoveNext
    Loop
Else
    MsgBox "There are no Records in ImportFiles"
End If
rsImport.Close
Set rsImport = Nothing
db.Close

标签： vbacsvms-access

vba - 按列位置将 Excel 电子表格导入 MS Access

问题描述

解决方案

推荐阅读