Excel dbf & Arc View

W

Walid Khalid

This example explain how to open csv file and save it to dbf format in VB using
Microsoft Excel object Library (Select in reference) without losing any data.
I am using it to update ESRI shape file database (dbf).
Header is limited to 10 character so you can trim headers to 10

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

';; ! ************************************************** *************************
';; ! csv to dbf using Excel
';; ! ************************************************** *************************
';; ! Function : Create csv file from dbf file
';; ! Updated : October 01, 2004
';; ! (C) 1999-2004, Geographic Information System, Rodney District Council
';; ! Contact :Walid Ismail Al Tiay
';; !
';; !
';; ! **************************************************

' add below to your code

NewFileName = "C:\NewfileName.dbf"
CSVFileName = "C:\CSVfileName.csv"

Workbooks.OpenText FileName:=NewFileNameDB, _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _
TrailingMinusNumbers:=True

' getting the width of each coloums

Cells.Select
Selection.Columns.AutoFit
' getting the width of each coloums & extend
For i = 1 To Columns.CurrentRegion.Columns.Count
Cells(1, i).Select
wid = Selection.ColumnWidth
Selection.ColumnWidth = wid + 15
Next

' rename Header to default
For i = 1 To Columns.CurrentRegion.Columns.Count
Cells(1, i).Select
Txt = Selection.Text
'Remove All Spaces
Txt=Replace(Txt," ", "")
Txt=Trim(mid(Txt,1,10))
Next

'Save as

ActiveWorkbook.SaveAs FileName:= _
NewFileName, _
FileFormat:=xlDBF4, CreateBackup:=False

' close file without save
ActiveWorkbook.Close SaveChanges:=False

++++++++++++++++++++++++++++++++++++++++++++++++++

Walid Khalid Ismail
New Zealan
 
Top