5

I have an Excel "Application" where users add/edit/etc. data. When ready, they export this data, the final deliverable needing to be a dBase file. Since Excel 2007 no longer has the Save As dBase feature, I've created the following code to export my data to an Access Table.

Is there any way in my VBA in Excel to go ahead and transfer the Access Table then into a dBase file? Or do I need to do that step from Access itself?

I'm trying to keep everything in Excel to make future modification as easy as possible. Any help is appreciated. If possible, would even be fine with doing from Access if the process can be automated in-sync with my 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
Erik A
  • 31,639
  • 12
  • 42
  • 67
Analytic Lunatic
  • 3,853
  • 22
  • 78
  • 120
  • Can you keep an older version of Excel installed on the machine and use its older SaveAs DBase functionality? – Toby Allen Jun 16 '13 at 10:23
  • Thanks for the reply Toby. That would be possible, but in any form of Government work you have policies that you have to upgrade software/hardware every so often... :) In any case, the whole dBase idea got scrapped in favor of going form Excel to a fixed-width column .txt file. Got both working for future reference though! – Analytic Lunatic Jun 17 '13 at 14:37
  • I love that the government policies make you update your copy of Office but not your Database!! Maybe you should move to a modern database (mariadb,mysql, mssql, sqlite, even MS ACCESS) rather than Excel which is not a database. Just a thought. – Toby Allen Jun 17 '13 at 16:02
  • 1
    Oh, depending on the application, we have AS400, SQL Server 2008, MySQL, Access, etc. The Excel part int his instance is meant to act like a data collection screen and then output a .txt file of that data for upload into AS400 mainframe. – Analytic Lunatic Jun 17 '13 at 19:59

1 Answers1

0

You could connect to the target Dbase IV file through ADO: ADO.NET OleDB and very old dBASE IV file

Once you have a connection to Dbase IV, you can use the same code that you use to load data from excel to access to load the data into Dbase IV instead.

Community
  • 1
  • 1
Tarik
  • 10,810
  • 2
  • 26
  • 40