Description

This tool is a simple macro which can be added to Excel to save the active sheet as DBF file. The DBF file is in a FEWS-readable format. When following the instructions below, the macro can be started by pressing a button in the toolbar.

Improvements to the script are welcomed, please send them to Arnejan van Loenen. Use at own risc.

Installation

The following procedure works in Excel 2003, but should also work in newer verions.

  1. Download the script modWriteDBF.bas
  2. In Excel, open the Visual Basic Editor (Tools -> Macro -> Visual Basic Editor)
  3. In the right column, right click on "VBAproject (PERSONAL.XSL)" (Read this if there is no PERSONAL.XLS )
  4. Select "Import File", and select the downloaded script
  5. Select menu "Tools" -> "References"
  6. Make sure that the newest "Microsoft ActiveX Data Objects Library" is selected (probably 2.7 or 2.8)
  7. Go back to Excel, right-click on an empty space on the toolbar, select "customize"
  8. In the tab "Commands" select "Macro's" in the left display
  9. In the right display, select "Custom button". Drag this button to a location in the toolbar
  10. Right-click on the button, select "Assign macro"
  11. Select "PERSONAL.XLS!SaveAsDBF"
  12. It is also possible to change the button image and text here
  13. Close the customize screen

Function of the script

The script performs the following actions when the button is pressed:

  1. Ask whether the user wants to overwrite the current dbf (this will only happen if the active sheet IS a dbf)
  2. The script will loop through the columns. It will stop when there is no value on the first row of a column
  3. For each column the maximum number of characters in a cell is calculated, this determines the "width" of a column
  4. The script will loop through the rows: when a row is encountered in which the first cell is empty, the script assumes the end of the table is reached and will stop

Known issues

  • Sometimes the content of cells with many characters are not correctly written in the dbf. It is not known why this happens. Solution: try again (maybe multiple times).
  • Sometimes during the running of the script the Visual Basic Editor pops up and the script stops running. No problem, just press F5 and the code continues.
  • In case of very large tables, you can get the error "field will not fit in record". The total number of characters or bytes in the whole table seems to be the problem. It is not solved yet.
  • No labels

Disclaimer