CODEWITHSUNDEEP
exceladodbdbfvba
Ziker
Ziker

Reputation: 141

DBF (dBase) file - open and convert in Excel VBA

I have problem as I'm trying to read a dbf file to my VBA macro, go through it, process the data and put in choosen worksheet. I am stuck, because I am getting 3001 error (arguments of wrong type, out of range or in conflict)

This is my code, what could be wrong about it?

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to import", _
FileFilter:="Excel Files *.dbf (*.dbf),")
''
If FileToOpen = False Then
MsgBox "Plik nie został wybrany.", vbExclamation, "Błąd!"
Exit Sub
Else
   Dim conn As Object
   Set conn = CreateObject("ADODB.Connection")
   Dim rst As Object
   Set rst = CreateObject("ADODB.Recordset")
   iPos = InStrRev(FileToOpen, "\", , vbTextCompare)
   Filepath = Left(FileToOpen, iPos)
   Filename = Right(FileToOpen, iPos - 1)
   MsgBox Filename & " " & Filepath
   conn.Open "DRIVER={Microsoft dBase Driver (*.dbf)};" & "DBQ=" & Filepath
   Debug.Print conn.ConnectionString
   rst.Open "Select * From " & Filename, conn, adOpenStatic, adLockReadOnly, adCmdText
Do Until rst.EOF
   Debug.Print rst.Fields(1).Value
   rst.MoveNext
Loop
    rst.Close
    Set rst = Nothing
    conn.Close
    Set conn = Nothing
End If

I also checked a file format and it's "File without DBT" whatever it means, still don't know the format of it :> Maybe I should change Driver ID?

Upvotes: 0

Views: 6698

Answers (1)

Archlight
Archlight

Reputation: 2079

I think your connection string is wrong. Try to use this connection string instead.

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Filepath & ";Extended Properties=dBASE IV"

And verify that youre filepath is a valid path to a folder and not to a specific file.

Upvotes: 1

Related Questions