Need to convert hundreds of .dbf to .csv

[djl0525]

I have hundreds of .dbf files that I am manually converting to .csv using Excel. Unfortunately, my .dbf collection is growing faster than I can convert them. Is there a way to convert all the .dbf files in the current folder to .csv? All the .dbf files are in the same folder. The path to them would be the same as the macro file.

Any assistance will be greatly appreciated.

-- DJ

 

[Ragnar1211]

Can you do a record macro and convert one of the files and post the resulting vba code here please.

I will automate the rest for you.

 

[djl0525]

Here is the recorded macro...
Please replace F:\user\Files\ with code for current directory as the files will not always be in F:\user\Files folder.

Sub dbf_to_csv()
'
' dbf_to_csv Macro
' Macro recorded 3/9/2009 by DJ
'
'
Workbooks.Open Filename:= _
"F:\user\Files\sul83ann_null_36km.dbf"
ActiveWorkbook.SaveAs Filename:= _
"F:\user\Files\sul83ann_null_36km.csv", _
FileFormat:=xlCSV, CreateBackup:=False

End Sub

Thanks! --DJ

 

[Ragnar1211]

Try This:

Sub FindFiles()
Dim strDocPath As String
Dim strCurrentFile As String
Dim Fname As String
Application.ScreenUpdating = False

strDocPath = "F:\user\Files\"
'strCurrentFile = Dir(strDocPath & "*.*")
strCurrentFile = Dir(strDocPath & "*.dbf")

Do While strCurrentFile <> ""

    Workbooks.Open Filename:=strDocPath & strCurrentFile
    Fname = Left$(strCurrentFile, Len(strCurrentFile) - 4) & ".csv"
    ActiveWorkbook.SaveAs Filename:=strDocPath & Fname, FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close (False)
strCurrentFile = Dir
Loop
Application.ScreenUpdating = True
End Sub

 

[djl0525]

Thank you Ragnar1211 for your help. I would have responded sooner, but I've been under the weather for weeks. I just ran the code today. It worked really well! Mission accomplished!

I had to hard code the path to the files in the code. Can you make it look in the current directory - whatever folder the Excel macro is in? So that when it's run from different folders, the code does not have to be modified.

Can you make it count the files and tell me which file it's working on? Like, "Converting 23 out of 592." Or "Converting 592 files. 17% done."

Can you display a message box when it's done to say, "All done"?

Thanks again for your help!
--DJ
http://www.mrexcel.com/forum/member.php?u=98572

 

[djl0525]

Anybody know how to change the hard coded file path to the active directory of the current file? See the strDocPath line in the code.

Sub ConvertDBF_to_CSV()
Dim strDocPath As String
Dim strCurrentFile As String
Dim Fname As String

'* * * Edit the path to your .dbf files in the strDocPath line below!!! * * *

Application.ScreenUpdating = False

strDocPath = "F:\user\Files\"
'strCurrentFile = Dir(strDocPath & "*.*")
strCurrentFile = Dir(strDocPath & "*.dbf")

Do While strCurrentFile <> ""

Workbooks.Open Filename:=strDocPath & strCurrentFile
Fname = Left$(strCurrentFile, Len(strCurrentFile) - 4) & ".csv"
ActiveWorkbook.SaveAs Filename:=strDocPath & Fname, FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close (False)
strCurrentFile = Dir
Loop
Application.ScreenUpdating = True
End Sub

--DJ

 

[micro-blades]

Change this: strDocPath = "F:\user\Files\"

To this: strDocPath = ThisWorkbook.Path & "\"

See if that works.

Brian

 

[djl0525]

That worked perfectly!

Thanks!

 

[djl0525]

Do you know how I can include the path in a msgbox statement? I'm trying to say:

"The files in _____ have been converted."

I can create a message without the path, but I'd rather include it.

-- DJ

 

[micro-blades]

This'll do it:

MsgBox "The files in """ & ThisWorkbook.path & """ have been converted."

Brian