从Excel VBA导出Access Table到dBase文件

Question

我有一个Excel "应用程序"，用户添加/编辑/等。数据。准备好后，他们导出这些数据，最终交付的需要是一个dBase文件。由于Excel 2007不再具有另存为dBase功能，我创建了以下代码来导出我的数据到访问表。

在Excel中的VBA中是否有任何方法可以继续并将访问表转移到dBase文件中?还是需要从Access本身执行这一步?

我试图保持一切在Excel，使未来的修改尽可能容易。任何帮助都是感激的。如果可能的话，如果这个过程可以自动地与我的导出过程同步，我甚至可以从Access中执行。

Sub Export()
Dim dbConnection As ADODB.Connection
Dim dbFileName As String
Dim dbRecordset As ADODB.Recordset
Dim xRow As Long, xColumn As Long
Dim LastRow As Long
'Go to the worksheet containing the records you want to transfer.
Worksheets("FeedSamples").Activate
'Determine the last row of data based on column A.
LastRow = Cells(Rows.Count, 1).End(xlUp).row
'Create the connection to the database.
Set dbConnection = New ADODB.Connection
'Define the database file name
dbFileName = "\agfilespublicITSD_ApDevJames ScurlockPersonal Project NotesFeedSampleResults.accdb"
'Define the Provider and open the connection.
With dbConnection
    .Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFileName & _
    ";Persist Security Info=False;"
    .Open dbFileName
End With
'Create the recordset
Set dbRecordset = New ADODB.Recordset
dbRecordset.CursorLocation = adUseServer
dbRecordset.Open Source:="ImportedData", _
ActiveConnection:=dbConnection, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable
'Loop thru rows & columns to load records from Excel to Access.
'Assume row 1 is the header row, so start at row 2.
'ACCESS COLUMNS MUST BE NAMED EXACTLY THE SAME AS EXCEL COLUMNS
For xRow = 2 To LastRow
    dbRecordset.AddNew
    'Assume this is an 8-column (field) table starting with column A.
    For xColumn = 1 To 69
        dbRecordset(Cells(1, xColumn).value) = Cells(xRow, xColumn).value
    Next xColumn
    dbRecordset.Update
Next xRow
'Close the connections.
dbRecordset.Close
dbConnection.Close
'Release Object variable memory.
Set dbRecordset = Nothing
Set dbConnection = Nothing
'Optional:
'Clear the range of data (the records) you just transferred.
'Range("A2:H" & LastRow).ClearContents
MsgBox "Test"
Dim access As access.Application
Set access = "\agfilespublicITSD_ApDevJames ScurlockPersonal Project NotesFeedSampleResults.accdb"
access.DoCmd.OpenTable "ImportedData"
access.DoCmd.TransferDatabase acExport, "dBASE IV", "C:", acTable, "ImportedData", "TEST.DBF"
DoCmd.Close acTable, "ImportedData"
'CREATE dBASE FILE!
End Sub

Answer 1

您可以通过ADO: ADO连接到目标数据库IV文件。. NET OleDB和非常旧的dBASE IV文件

一旦连接到Dbase IV，就可以使用与从excel加载数据以访问相同的代码来将数据加载到Dbase IV中。