to continue the saga from my previous post (excel event binding), the custom application i wrote for my client is now running happily with excel 2003 after implementing those new event handlers to satisfy excel 2003.  so far so good.  but that is not the end of the story.

new computers at my client site come with office 2007.  (i don't know where you can buy office 2003 anymore.)  excel 2007 again has some new events that need to be handled:

workbookrowsetcompleted()
aftercalculate()

after adding these event handlers, excel 2007 ole automation has no problem. 

however, my client application manipulates a lot of dbf files.  (please don't ask me to upgrade the dbf to some sort of sql databases, as in my client's case it does not make sense -- trust me! 🙂

i found out some features of the app which automate excel to save in dbase iii format do not work anymore, and after some googling, i became aware that excel 2007 cannot save in dbf format any more.

haha, it comes to a dead end. 

on a side note, in the past, when i want to automate excel, i just start up excel and record a macro of the actions i like to do, and then view the auto-generated macro to retrofit the code into vfp.  sweat and easy.

i tried to do the same in excel 2007, say inserting a picture into a doc, and guess what, the recorded macro is empty.  reading the help file it says navigations in the ribbon bar will not be recorded.  (to tell you the truth, i am a bit shock when i read that :p)  unfortunately, i don't know how to insert a picture into a doc without using the ribbon bar. 

so i have to record a macro in excel 2002/2003 and then try the generated code with excel 2007 to see if it works or not.  is there an easier way?

and of course, the idea of using excel to repair a dbf i blogged about earlier on does not work any more...

why things become so difficult in excel 2007?  or is it just me? 🙂
btw, i am using vfp9 sp2.

40 Responses to Excel 2007 and DBF

  • Simon Arnold says:

    Don’t know if this will help, but this codes works with Excel 2007 and will insert a bitmap into a WorkSheet:

    LOCAL loExcel AS Excel.Application

    LOCAL loBook AS Excel.Workbook

    LOCAL loSheet AS Excel.Sheets

    #DEFINE autoIn2Pts 72

    loExcel = CREATEOBJECT(‘Excel.Application’)

    loBook = loExcel.Workbooks.Add()

    loSheet = loBook.ActiveSheet

    WITH loSheet

    lcLogo = _SAMPLES + "\TasTrade\Bitmaps\TTradeSm.bmp"

    .Shapes.AddPicture(lcLogo,.T.,.T.,0.25 * autoIn2Pts, 0.25 * autoIn2Pts, 1.00 * autoIn2Pts, 1.00 * autoIn2Pts)

    ENDWITH

    loExcel.Visible = .T.

  • About this line from David:

    "I became aware that Excel 2007 cannot save in DBF format any more."

    I’m not really sure Microsoft did drop any save format.

    Have a look at the FileFormat enumeration:http://msdn2.microsoft.com/en-us/library/bb241279.aspx

  • davidfung says:

    Hi Simon,

    Thanks for the sample code! 🙂

    David

  • davidfung says:

    Hi Christian,

    Sound to me the constants are still there, but Excel 2007 will not honor them as the designated save format!

    David

  • burnout says:

    Excel 2007 has been a deadend for me. nearly all of my formats are in dbf. and no I can not change to sql. the progs I use dont support it.

    so now or they create an addin that adds dbf save function or I will change to openoffice. years of small programs to manupulate dbf files in specific ways and all of my knowhow has gone in smoke with 2007. no thank you.

  • Daniel Slone says:

    Ouch!  I’m glad I saw this thread before getting Office 2007!  I convert a lot of spreadsheets ionto .dbf using Excel 2003 for use in ESRI GIS.  I’ll have to find another way before we are forced to upgrade.  Thanks.

  • chuck snyder says:

    yep, 2007 is becoming a real headache.  I tried the code above to insert a picture, but we are getting the pic from a url and its not reading it.

    Any way to do that??

  • vfox_latino says:

    I think Microsoft ignored the fact that in LatinAmerica there is a lot of people who still make or use VFP aplications with uses DBF format,  

    We still use EXCEL to export data to DBF Files for VFP apps.  

    I hope MS (or someone) make a DBF plugin for EXCEL 2007 to save as DBF files,

    otherwhise we have to use OpenOffice, b.t.w. that’s an excelent choice.

    TKS

  • santosh says:

    I am using Excel 2007. And it seems that you can still open a DBF (dBase) file in Excel but you can’t save it in DBF format. Is there any solution to this? I would like to make some changes in a DBF file using Excel and save it back in DBF format.

  • davidfung says:

    Hi Santosh,

    Yes, Excel 2007 can still open DBF, but as far as I know there is no way to save in DBF format.

    David

  • Haskett says:

    I take it there has been no plug-in created yet to save xls as dbf?

    I read somewhere you can custom install Excel and save the old versions, how does this work?  Do you open the older 2003 version when working with dbfs?

    What happens when you get data in the 2007 format?  Will I have to write code to save as a 2003 xls inorder to open it and manipulate it into a dbf?

  • davidfung says:

    Hi Haskett,

    No plug-in that I heard of, but I may be wrong.

    Yes, you can install Office 2007 and Office 2003 side by side, but in the long run, it may be better to find an alternate solution as you cannot buy Office 2003 any more…

    Hope this helps.

    David

  • Will says:

    Time for me to explore Open Office also. A big time DBF user as we all are in the direct mail industry. Shame on Microsoft! FoxPro is actually one of thier own products with no compatiblity built in. Unheard of.

  • Pan says:

    Well, u can see the enumeration is still there, but no matter how hard i try there’s still no DBF..pretty much sucks for microsoft… -_-

  • JC says:

    Same boat here – ESRI ArcGIS user.  I run stats in Excel that are much faster and easier (and sometimes impossible) to run from ArcGIS. I use and save in the .dbf directly from the shapefile, and the next time I open the shapefile in GIS, the stats are right there.

    Now, with a forced workplace-wide upgrade to Office 2007, I can’t save the changes in .dbf anymore, and saving it to another format and importing into GIS isn’t an option since it’ll kill the relationship between the original .dbf and the other required files for the shapefile.

    Thanks, Microsoft, for your "upgraded" software that reduces functionality for a good portion of users, simply because you think simple .dbf is archaic.

  • DBF_Fan says:

    This may help some folks – I just opened a .dbf from a GIS file, ran a couple fake calcs in it, saved as Office 2007 Excel format.

    Opened MS Access 2007, imported said Excel file, and exported to Dbase format (.dbf IV). I named this .dbf the same as the original .dbf – effectively overwriting it – but the shapefile opens with the newly created information intact.

    Just make sure you create the columns to be calculated in GIS to keep your references intact.  In the long run, though, I don’t know if anything would get lost in translation using this method.  

    Despite that the workaround seems to work, I managed to convinced my IT guy to install Excel 2003 side by side just so I can keep the .dbf functionality.

  • Goutam says:

    That’s good, I got the Alternative solution from Excell2007 to .dbf with via Access.

  • MOhit says:

    Why so much of pain just rename the file extension to DBF. By removing the blank sheet.

  • Foxdev says:

    MOhit,

    Could you explain further "rename the file extension by removing the blank sheet". What file extension and what blank sheet? I thought you were talking about the access file but the "blank sheet" confused me.

  • Foxdev says:

    MOhit,

    Could you explain further "rename the file extension by removing the blank sheet". What file extension and what blank sheet? I thought you were talking about the access file but the "blank sheet" confused me.

  • sar says:

    where URL can download convert file from Excel2007 to DBF file?

  • Regarding the Excel2007 to DBF issue. The link above and the text below is from ESRIs technical articles. it has a simple solution to the issue.  

    Export an Excel table to .dbf format in ArcGIS.

    In Excel 2007, save the table as, ‘Excel 97-2003 Workbook’ (.xls format).

    Navigate to the .xls table location through ArcCatalog, and expand the file to view the worksheets.

    Right-click on the worksheet representing the table. Select Export > To dBase (single).

  • Dawn says:

    I’m happy I read all of your comments… made me feel better because after trying so many times, I thought I was the problem. Excel 2007 made it harder, wasted my time and frustrated me no end. Thanks for your input guys! I’ll try Tomas’s suggestion.    

  • Bahaa Kaeen says:

    Hi

    i have tried 2 suggestion, opening the excel sheet in access 2007 actually worked, and i got all the informations true, but the suggestion from ESRI article didn’t work well, i’ve Successfully converted the file to dbf but i lost some informations.

    So, i will go with access, its very easy.  

  • keselek kudanil says:

    to editing dbf file using microsoft office 2007, use your microsoft access 2007. Open dbf file using access 2007 (dont use excel 2007), then you can make editing in it. after you finish, save that project with ctrl s. dont save this file with "save as". it works for me, i open dbf file for my arcview file that have more than 65.000 records (office 2003 cant open this file, because maximum records for office 2003 is 65.000)

  • Steven says:

    If you haven’t tried this it saved me.  I had to write a bit of code but now I can import any Excel 2007 file into a dbf.

    http://support.microsoft.com/default.aspx?scid=kb;en-us;949529

  • tbowl says:

    As a big ESRI user, this has made my life a lot more difficult.  Of course there are ways around this issue, but in practicality it really is a pain in the ass.  I have to go back and change many of my python scripts to input different file types.

    Anyone have any idea why microsoft would remove this functionality?  Maybe they just wanted to be dicks about it.

  • vanarae says:

    this is by far the best thread regarding this issue – i too need to regularly convert updated excel spreadsheets into DBF4 files to bring into ArcMap.  

    I had previously tried importing the excel spreadsheet into access – it worked, but didn’t bring in my header titles, making manipulation of the data very difficult in ArcMap.  Does anyone know how to make sure that the first row header titles in Excel get correctly converted to first row header titles in Access?

    Also, thanks for suggestion to save excel 07 file as excel ’97-’03 and then import this directly into ArcGIS. I was able to do the import directly from ArcMap by adding data (I didn’t go through ArcCatalog) however the join didn’t work (all the values from the excel file became ‘null’ when joined to the shapefile).  Does anyone know why this happens?

    I’m so close to making this work – any advice will be appreciated.

  • Scott says:

    You can always just get Open Office and use Calc – which is just like Excel 2003 – to do the .dbf stuff – and its free!

  • Opelumi says:

    Thks guyz for all ur inputs

  • majorde says:

    I put together an Excel 2007 add-in to add an item to the Office menu ‘Save As’ list for saving to DBF (dBASE IV). It is available at these two locations:

    http://www.excelforum.com/tips-and-tutorials/688650-excel-2007-add-in-to-save-dbf.html

    http://www.dbforums.com/microsoft-excel/1644233-excel-2007-add-save-dbf.html

    I hope this helps someone.

  • Alc18 says:

    majorde.. The add-in you provided works great!! Thanks for sharing!!

  • Nate Ocean says:

    I think Microsoft are a bunch of inconsiderate ****.  Not only was the deletion of DBF capabilities from Excel unnecessary (sheesh, the code is already there), but is shows how out of contact they are with their own customers.  At the least, they should have provided a plug-in if they felt including it in the base product was undesirable.

    One should also be wary of allowing Microsoft to automatically “update” your products.  A recent update of Windows Media Player has made about 1/3 of my songs (legally obtained) suddenly loose their licenses.  Oh, and once “upgraded” there is no turning back.

    Thanks to “majorde” for his VB program to giving “save” to DBF capability.  However, it seems to fail on a file with a large number of records (8,000).  For me, OpenOffice seems to fill the bill (it’s free).  If necessary I will go back and reload Excel 2003 version.  2003 is more robust anyway, although 2007 has more features and handles much larger arrays.  Unfortunately Excel 2007 uses the disastrous “Ribbon” command bar.

    I wish they’d stop ***** at Microsoft.

  • gygulyas says:

    XLSX2DBF is an Excel 2007 add-in that helps convert and/or save a native DBF file that was edited with Excel 2007.

    Two common scenarios are supported:

    A) Open a native DBF file in Excel 2007 – Make changes – Save as a native DBF file with the changes.

    B) Open/create an Excel file that looks like a database (headers/columns/rows) – Save as a native DBF file.

    http://thexlwiz.blogspot.com/

    Gyula

  • joao gusmao says:

    I face same thing like other, wile i prepare a data for mapping there is data request to save in DBF file but i realise that in office 2007 there is no longer DBF format file. So i am thingking are i am the only people who face this problem?

  • Gyula says:

    Hi there,

    just wanted to let you know that I released a new version of the add-in that enables saving a DBF file in Excel 2007.

    New features:

    1. Now you can add/insert new fields, create calculated fields in addition to adding new records or editing existing records in your native DBF file!

    2. If you start with an Excel file the software now have enhanced capabilities to determine the field types (better than Microsoft’s own in earlier Excel versions).

    3. The add-in checks DBase field naming conventions and also identifies duplicate fields. All problem field names are visually identified with a cell comment!

    4. If you start out with a brand new file and forget to save it, the add-in will ask before the conversion.

    5. Large files are supported. I edited files over 500,000 records with no problem.

    See the post at http://thexlwiz.blogspot.com/.

    Gyula

  • Gary Cave says:

    I know that it’s a pain to have to purchase new software, but JMP (by SAS) will allow to open .dbf files and save as .dbf files.

  • Well, things are more easy than you think

    1.- Don´t get confussed about Excel 2007, i was in the same problem minutes ago. Fortunately still Excel let you save in a lot of different formats (example: toimport.csv)

    2.- Save your file as a csv -comma delimited file-

    3.- change your program in dbase adding more less to read like… "apppend from toimport.csv file type delimited"

    4.-voila, your application is ready and you have a dbIV file.

    regards from Mexico

    Alejandro

    alxtapia@gmail.com

  • francisco avendano says:

    well guys i haven’t read all the comments but i will suggest either using 2003 office for opening .dbf files or to simply use openoffice and in there you will be able to launch .dbf files. I dont deal with these files extension all the time, but when i have to i prefer using openoffice, since i dont really know how to use access, and i guess it is easier to manipulate data on excel. however now i have an inquiry….

    on oppenoffice you only have the option to save under the .dbf format. is there a difference between the former selection we could choose from on excel 2003 such as dbf(I), dbd(II), dbf(III), dbf(IV). does openoffice opens all the four types of dbf versions? and when saving to .dbf which version do they actually use?

    thanks

  • Mujeebullah Afghanistan says:

    dear All GIS Users,

    if some one want to have its 2007 excel format into dbf they can easily import their xls table into the geodatabase and again from there they can easily export in dbf format,

    again if u want to bring changes open the dbf file into excel 2007 save bring changes save it in xls export it into the geodatabase and from there you can export ur table to dbf.

    any question (mujeeb_danishjo@yahoo.com)

Leave a Reply

Your email address will not be published. Required fields are marked *