I have an Excel application in which users add / edit / etc. data. When they are ready, they export this data, the final result should be a dBase file. Since Excel 2007 no longer has Save As dBase, I created the following code to export my data to an access table.
Is there any way in my VBA in Excel to go and pass the access table to a dBase file? Or do I need to take this step out of access itself?
I am trying to save everything in Excel in order to make modification in the future as simple as possible. Any help is appreciated. If possible, it would even be nice if Access could automate synchronization with the export process.
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
source share