Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Excel 2007 save as dbf4 (dbfIV)

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    423
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel 2007 save as dbf4 (dbfIV)

    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!

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel 2007 save as dbf4 (dbfIV)

    You could try the Add-In:

    http://thexlwiz.blogspot.com/

    Also, Access 2007 still supports save as DBF.
    Microsoft MVP - Excel

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    423
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2007 save as dbf4 (dbfIV)

    Quote Originally Posted by Andrew Poulsom View Post
    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

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel 2007 save as dbf4 (dbfIV)

    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.
    Microsoft MVP - Excel

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    423
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2007 save as dbf4 (dbfIV)

    Quote Originally Posted by Andrew Poulsom View Post
    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!!

  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,694
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Excel 2007 save as dbf4 (dbfIV)

    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.
    If posting code please use code tags.

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    423
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2007 save as dbf4 (dbfIV)

    Quote Originally Posted by Norie View Post
    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....

  8. #8
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel 2007 save as dbf4 (dbfIV)

    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/20...2007-with.html
    Microsoft MVP - Excel

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Posts
    423
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2007 save as dbf4 (dbfIV)

    Quote Originally Posted by Andrew Poulsom View Post
    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/20...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

  10. #10
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,694
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Excel 2007 save as dbf4 (dbfIV)

    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.
    If posting code please use code tags.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •