Need advice and code help with working with *.dbf files in Excel 97

[TBA]

Excel 97
Windows 2k Pro

If anyone uses ESRI mapping software, please feel free to jump in.

The software I use at work uses *.dbf files to hold attribute data for
spatial mapping features. When creating a new field in the table (the *.dbf
file) the user is prompted to enter a value type for that field, the choices
being String, Number or Boolean. The user is also prompted to enter a field
width, which basically defines the number of characters that field can hold.
If I open the dbf file in Excel (sometimes necessary for added
functionality) the column widths are automatically set to the field size as
set in the mapping software. The danger in this is that if I change a
column width in Excel and don't remember to change it back before saving, I
run the risk of truncating my data. I am trying to write a Macro or
procedure which will capture the field or column widths (store them as
variables?) when I click on a command button (on a form or a button on the
sheet itself) and another procedure which will restore the columns to their
original width when I click another button.

So far I have made a form which loads from a macro I have stored on a custom
toolbar button. Of course there's lots of other things I'd like to be able
to do or add to the form, but for right now I'm only interested in capturing
the column widths and restoring them.

The last thing is a big one. I'd like this to work for ANY size table,
since the *.dbf files I work with can range widely in number of columns and
rows. I realize that a *.dbf file will always open with cell A1 containing
the first field name of the table, and that Excel automatically names the
table range as "Database". I'm hoping this will simplify things.

Any hints or suggestions on form design or code are certainly welcome, as
well as any advice on general approach.

Thanks for reading!

-gk-

 

[BrianB]

You will need to store the column widths in a worksheet for recall.
AFAIK this will need to be in a separate workbook because a .DBF file
can only contain 1 worksheet. So something like this :-

'-----------------------------------
Dim ColumnWidthList As Worksheet
Dim DBF As Worksheet
'-----------------------------------
Sub STORE_COLUMN_COLUMNWIDTHS()
Set ColumnWidthList = ThisWorkbook.Worksheets("Sheet1")
Set DBF = Workbooks("MyDBF").Worksheets("MyDBF")
For C = 1 To 10
ColumnWidthList.Cells(1, C).Value = DBF.Columns(C).ColumnWidth
Next C
End Sub
'----------------------------------
Sub RESTORE_COLUMN_COLUMNWIDTHS()
Set ColumnWidthList = ThisWorkbook.Worksheets("Sheet1")
Set DBF = Workbooks("MyDBF").Worksheets("MyDBF")
For C = 1 To 10
DBF.Columns(C).ColumnWidth = ColumnWidthList.Cells(1, C).Value
Next C
End Sub
'------------------------------------

Regards
BrianB
==========================