Importing Foxbase files in bulk

[vinodi]

Hi,

I have posted this question in other forums, therefore I have not received any answer to this one yet.

I have several hundred Foxbase (.DBF files) that I am importing in MS-Access 2000 by using the File -> Get External Data -> Import option. The problem is the IMport dialog box allows me to import only one file at a time. Is there any macro or module that should be written to perform this task. I am basically a VB programmer and have no knowledge of Access Basic ( although I can understand the code, u know what I mean ). Therefore I would request anybody in this forum to give me an answer which will be highly appreciated.

Thanx in advance.B-)

 

[jfischer]

Here's a generic routine that you can modify to suit your needs.

Function ImportAllDBase(sDir As String) As Long
  Dim sFile   As String
  Dim sTable  As String
  Dim sFilter As String
  Dim sPath   As String
  Dim lCount  As Long
  
  On Error Resume Next
  lCount = 0
  If sDir & &quot;&quot; <> &quot;&quot; Then
    sPath = sDir
  Else
    sPath = CurDir()
  End If
  If Right(sPath, 1) = &quot;\&quot; Then
    sPath = Left(sPath, Len(sPath) - 1)
  End If
  sFilter = sPath & &quot;\*.dbf&quot;
  sFile = Dir(sFilter)
  Do While sFile <> &quot;&quot;
    'Strip extension from file name
    sTable = Left(sFile, Len(sFile) - 4)
    DoCmd.TransferDatabase acImport, &quot;dBaseIII&quot;, _
                           sPath, acTable, _
                           sFile, sTable
    If Err.Number = 0 Then
      lCount = lCount + 1
    End If
    Err.Clear
    sFile = Dir 'Next .dbf
  Loop
  ImportAllDBase = lCount
End Function