VBA ADODB import dBASE file more then 8 characters long

[mattey28]

Hi I'm trying to import dBase file to Excel. My code looks like this:

Sub MFACTImport(TargetRange As Range)

DBFullName = "C:\temp\MISS_TRXCUR.DBF"
DBDirectory = "C:\temp"
TableName = "MISS_TRXCUR"

Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
    Set TargetRange = TargetRange.Cells(1, 1)
    
    Set cn = New ADODB.Connection
    cn.Open "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;ReadOnly=True;Dbq=" & DBDirectory & ";"
    Set rs = New ADODB.Recordset
    With rs

        .Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable
        
        For intColIndex = 0 To rs.Fields.Count - 1 ' NAZWY PÓL
            TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
        Next
        TargetRange.Offset(1, 0).CopyFromRecordset rs ' REKORDY

    End With
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

The problem is that this macro imports file with name MISS_TRX.DBF not MISS_TRXCUR.DBF. Is there a way to process file with name longer then 8.3 characters ?
Thanks for any help

 

[Andrew Poulsom]

For file names longr than 8 characters you need to use the DOS name, eg MISS_T~1.DBF. Here's a function you can use to get it:

http://www.freevbcode.com/ShowCode.Asp?ID=506

 

[mattey28]

Thanks to code you pointed I have DOS version of directory and file name but is the table name also coded in 8 characters ?

I change my code and I get different errors, but is ussualy stops at cn.Open line.
Should I use full path with file name (DOS encoded) in Dbq= ?

 

[Andrew Poulsom]

I think it would be something like:

rs.Open "select * from MISS_T~1.DBF", cn, , , adCmdText

 

[mattey28]

Thanks for help Andrew!

I've altered my code to

Set cn = New ADODB.Connection
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=" & DBDirectory & _
  ";Extended Properties=DBASE IV;"
  Set rs = New ADODB.Recordset
    With rs
      .Open "SELECT * FROM [MISS_T~3.dbf]", cn, , , adCmdText

and it works!!