Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need advice and code help with working with *.dbf files in Excel 97
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- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need advice and code help with working with *.dbf files in Excel 97
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 ========================== "TBA" wrote in message ... 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- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel xls files open as Machine Code | New Users to Excel | |||
Need Advice on Working with Routing Slips | New Users to Excel | |||
VBA Excel code not working properly (HELP!) | Excel Discussion (Misc queries) | |||
working excel files keeping on desktop | Excel Worksheet Functions | |||
Little more advice on this code | Excel Discussion (Misc queries) |