Writing Excel Cell Values to DBF Fields

Question

I want to use Excel as a front end for a simple application. Data values will be written to cells in Excel after which calculations will be performed by Excel. I then want to write the results of these calculations to specific fields in a DBF (Dbase format) record.

Please can anyone tell me what the easiest way is to do this?

Grainger

Answer 1

Hi,

Thanks for posting in the MSDN Forum.

Based on your issue, I would suggest you save the Excel file contains data as a DBF file. Please refer to this method

ActiveWorkbook.SaveAs Filename:= _
"C:\test.dbf", FileFormat:=xlDBF4

Hope it helps.

Best Regards,

---

Leo_Gao [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Hi Leo. Thanks for the reply. The Foxpro database belongs to an application which enables users to analyse the data stored in many different ways so I need to keep the DBF file structure as it is. I want to be able to write data values, calculated in Excel, to specific fields in one of the DBF files.

Hope this makes it clearer. Any more guidance would be great.

Thanks, Grainger

---

R G Nelson

Answer 3

Hi R G Nelson,

This is a sample that exports data from the active excel worksheet to a table in a database file via ADO by using VBA. Hope it can help you.

Sub ADOFromExcelToDBF()
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=C:\FolderName\DataBaseName.dbf;"
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable  
    ' all records in a table
    r = 3 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0 
    ' repeat until first empty cell in column A
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("FieldName1") = Range("A" & r).Value
            .Fields("FieldName2") = Range("B" & r).Value
            .Fields("FieldNameN") = Range("C" & r).Value
            ' add more fields if necessary...
            .Update ' stores the new record
        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

Have a good day.

Regards,

---

Leo_Gao [MSFT]
MSDN Community Support | Feedback to us

Answer 4

Hi Leo,

          Many thanks for this code it looks like it will do the job. One question - the comment on line 3 says  "Connect to Access Database" is this a typo and should it say "Connect to Foxpro/Dbase database"?

Sorry to keep getting back to you but I'm not an expert in the use of ADO / VBA.

Regards, Grainger

---

R G Nelson

Answer 5

Hi R G Nelson,

Yes, ADO can be used in Access, SQL Server, Foxpro, Dbase, Oracle database etc. I' m sorry that the comment is my mistake.

Have a good day.

Regards,

---

Leo_Gao [MSFT]
MSDN Community Support | Feedback to us

Answer 6

Hi Leo,

           Many thanks for all your help.

Grainger

---

R G Nelson

Answer 7

There are MANY samples here:

http://www.erlandsendata.no/english/index.php?t=envbadac

---

Ryan Shuell