Puts format of a dbf files in a vector

[Guest]

Hi,
I have a macro which import a dbf file in Excel. I would like to be able to
convert each row that have a format D 8 0 as a date format in Excel. It
doesn't seems possible to get the D 8 0 information when the dbf file is open
in Excel. So, I'm wondering if it's possible to open MS Access and then open
the file and get all date columns informations. After that, I could close the
file and open it in Excel.

Can someone confirm me that a dbf file open in Excel loose all the columns
properties?

 

[John Nurick]

Hi Alex,

It's not so much that a dbf file open in Excel loses its column
properties as that Excel has no notion of data types or column
properties, only of cell formats.

But you don't need to use Access. Instead, use ADO, something like this
air code:

Dim dBaseFolder As String
Dim oConn As New ADODB.Connection
Dim RS As New ADODB.Recordset

dBaseFolder = "C:\Temp" 'location of dbf file
dBaseFile = "Orders.dbf" 'name of file

'Connection string and syntax for RS.Open based on
'http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForDBASE
oConn.Open "Driver={Microsoft dBASE Driver (*.dbf)};" & _
"DriverID=277;" & _
"Dbq=" & dBaseFolder

'Open recordset with no records
RS.Open "SELECT * FROM " & dBaseFile & " WHERE FALSE;", _
oConn, , , adCmdText

'Get the field properties
With RS.Fields("TheDate")
...
End With

RS.Close
oConn.Close

End Sub

 

[Guest]

Thank you John !

It works good.

 

[Cindy Winegarden]

Hi Alex,

Just to add, not all DBF files are compatible with Jet. FoxPro 2.6 files
with Memo fields don't come across correctly, and Visual FoxPro files aren't
readable at all. You can use the FoxPro and Visual FoxPro ODBC drivers,
downloadable from msdn.microsoft.com/vfoxpro/downloads/updates/odbc for any
FoxPro files created in VFP6 or earlier. For all FoxPro files including VFP7
and later use the FoxPro and Visual FoxPro OLE DB data provider,
downloadable from msdn.microsoft.com/vfoxpro/downloads/updates .

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
(e-mail address removed) www.cindywinegarden.com