Excel 2007 save as dbf4 (dbfIV)

bolo

Active Member
Joined
Mar 23, 2002
Messages
423
Right i have been forced to upgraded to Excel 2007 and i don't like it. It seems slower and the new menu bar isn't very helpful, especially with macros. But the thing that really gets me wound up is the fact that i now cannot save as a dfb file!!!! I do a fair bit of work in ArcGIS and so i have written macros to output dbf files for GIS work. But now i can't do it! anyone know of any simple code that would allow me to save as a dbf. and i know its quite late, but how can i complain to micro$oft? Its not as if the dbf save as function was causing anyone grief in the first place. This upgrade is seriously pi$$ing me off!

I tried searching but nothing came up, so apologies if it is a repeat thread.

Stupid Micro$oft... Rubbish!
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
You could try the Add-In:

http://thexlwiz.blogspot.com/

Also, Access 2007 still supports save as DBF.


I had a look at the addin but it seems to require payment to have the full version. Also i am looking for something to integrate into the code i have written so that i don't have to do anything else. My macros are written in excel 03 and i really can't be bothered to convert to access just for this purpose. Thanks
 
Upvote 0
Save as DBF has been deprecated in Excel 2007, so you will need to find an alternative. You don't need to convert to Access; you can automate it with VBA in Excel.
 
Upvote 0
Save as DBF has been deprecated in Excel 2007, so you will need to find an alternative. You don't need to convert to Access; you can automate it with VBA in Excel.

I appreciate that save as dbf has now been removed from Excel 07. Can't say i understand why it has been removed though!

Anyway with regards to your second point, can you elaborate? I have only really written VBA within excel, and i have rearely used it to control other applications. If you could provide a starting point that would be great!!
 
Upvote 0
Why do you need to use the dbf format?

As far as I can find, though I'll admit my research hasn't been comprehensive, ArcGIS can work with Excel.:)
 
Upvote 0
Why do you need to use the dbf format?

As far as I can find, though I'll admit my research hasn't been comprehensive, ArcGIS can work with Excel.:)

ARCGIS can work with EXCEL. Its just that a number of macros i use in ARCGIS use the dbf format. ARCGIS only recently added proper functionality with excel in the latest version. Also whilst ARCGIS can work with xls, the xls file type is not expressly linked with the shape files that arc uses and so using linking the data would require another step. It is just frustrating that they would break something that was working fine....
 
Upvote 0
You can use CreateObject to create an instance of the Access Application. The steps would be:

1. Save as CSV in Excel using the SaveAs method.
2. Import the CSV into Access using the TransferText method.
3. Export to DBF from Access using the TransferDatabase method.

You could also try using ADODB:

http://nerds-central.blogspot.com/2007/12/write-dbf-file-from-excel-2007-with.html

Hi andrew, Thanks for the suggestions. I am trying your first one. I can't get past step 3.

The macro works fine until it reaches the transferdatabase command, giving a runtime 3011 error. I can't work out what the problem is? I am running this code from Excel 07...
Thanks

Bolo

Code:
Sub saveDBF()

Application.DisplayAlerts = False
Dim curPath As String
Dim tempLen As Integer
Dim FileNN As String
' save current page as csv

curPath = ThisWorkbook.Path & "\"
If FileN = "" Then
    FileN = Application.GetSaveAsFilename(InitialFileName:=curPath, _
      FileFilter:="DBF 4 (dBASE IV) (*.csv),*.*", Title:="Save As DBF")
    If FileN = False Then Exit Sub
End If
If Len(Dir(FileN)) <> 0 Then Kill FileN
tempLen = Len(curPath)
FileNN = Right(FileN, Len(FileN) - tempLen)
ActiveWorkbook.SaveAs Filename:=FileN, FileFormat:=xlCSV
Windows(FileNN).Close
FileNN = Left(FileNN, Len(FileNN) - 4)
If Len(Dir(Left(FileN, Len(FileN) - 3) & "mdb")) <> 0 Then _
  Kill Left(FileN, Len(FileN) - 3) & "mdb"

'create Access object
Dim objAcc As Object

Set objAcc = CreateObject("Access.Application")
objAcc.Visible = True
objAcc.NewCurrentDatabase Left(FileN, Len(FileN) - 3) & "mdb"

objAcc.DoCmd.TransferText acImportDelim, , FileNN, FileN, True

objAcc.DoCmd.TransferDatabase acExport, "dBase IV", curPath, _
       acTable, FileNN, left(FileN,len(FileN)-3) & "dbf", False

' Close the database.
objAcc.CloseCurrentDatabase

' Quit Access.
objAcc.Quit

' Close the object variable + housekeeping.
Set objAcc = Nothing
Kill FileN
Kill Left(FileN, Len(FileN) - 3) & "mdb"

Application.DisplayAlerts = True

End Sub
 
Upvote 0
Are you 100% sure you need to save in dbf format?

As far as I can see from the code posted so far all you want is a CSV file, perhaps not with the .csv file extension mind you.:)
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top