我有一个Excel“应用程序”,用户可以添加/编辑/等操作数据。准备好后,他们需要将数据导出为dBase文件。由于Excel 2007不再具有保存为dBase功能,因此我创建了以下代码将我的数据导出到Access表中。
在我的Excel VBA中是否有任何方法可以将Access表转移到dBase文件中?还是需要从Access本身执行该步骤?
我正在尝试保持所有内容都在Excel中,以便将来的修改尽可能容易。感谢任何帮助。如果可能,即使可以将过程与我的导出过程自动同步执行,也可以从Access中执行。
在我的Excel VBA中是否有任何方法可以将Access表转移到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 = "\\agfiles\public\ITSD_ApDev\James Scurlock\Personal Project Notes\FeedSampleResults.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 = "\\agfiles\public\ITSD_ApDev\James Scurlock\Personal Project Notes\FeedSampleResults.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