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.
Installation
The following procedure works in Excel 2003, but should also work in newer verions.
- Download the script modWriteDBF.bas
- In Excel, open the Visual Basic Editor (Tools -> Macro -> Visual Basic Editor)
- In the right column, right click on "VBAproject (PERSONAL.XSL)" (Klik hier als er geen PERSONAL.XLS aanwezig is)
- Select "Import File", and select the downloaded script
- Go back to Excel, right-click on an empty space on the toolbar, select "customize"
- In the tab "Commands" select "Macro's" in the left display
- In the right display, select "Custom button". Drag this button to a location in the toolbar
- Right-click on the button, select "Assign macro"
- Select "PERSONAL.XLS!SaveAsDBF"
- It is also possible to change the button image and text here
- Close the customize screen
Function of the script
The script performs the following actions when the button is pressed:
- Ask whether the user wants to overwrite the current dbf (this will only happen if the active sheet IS a dbf)
- The script will loop through the columns. It will stop when there is no value on the first row of a column
- For each column the maximum number of characters in a cell is calculated, this determines the "width" of a column
- 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.