import multiple files

[Eric W.]

I have a directory full of files that contain data which
will be imported into my access db. I need an example of
a looping statement to go through my directory, grab these
Excel files, and append them into my Access db. Any
help? Much appreciation!

Eric W.
Madison, WI

 

[Marshall Barton]

I have a directory full of files that contain data which
will be imported into my access db. I need an example of
a looping statement to go through my directory, grab these
Excel files, and append them into my Access db. Any
help?

Check Help on the Dir function. You would use it something
like this:

strFileName = Dir("somepath\*.XLS")
Do Until strFileName = ""
'import from file "somepath\" & strFileName
strFileName = Dir()
Loop

 

[George]

This is gross overkill for what you want, but look
carefully at the DIR() function.
That is what you want.
Good luck.
George


Option Compare Database
Option Explicit
Sub ScanDisk()
Dim strX As String
strX = ListFiles("D:\", True)
strX = ListFiles("C:\MyDocu~1\", True)
End Sub
Function ListFiles(parDir As String, parSubDir As Boolean)
Dim dbs As Database, rstFileList As Recordset
Dim strNextName As String, strAttr As String, intRecCount
As Integer, strExt As String
Dim Directory(200) As String
Dim intDirCt As Integer, intSize As Long, dttDateTime As
Date
Set dbs = CurrentDb()
Set rstFileList = dbs.OpenRecordset("tblFiles",
dbOpenDynaset)
intDirCt = 0
strNextName = Dir(parDir, vbDirectory)
Do While strNextName <> ""
strAttr = 0
intSize = 0
If strNextName <> "pagefile.sys" Then
strAttr = GetAttr(parDir & strNextName)
End If
' If InStrRev(strNextName, ".") = 0 Then
strExt = ""
' Else
' strExt = UCase(Mid(strNextName, 1 + InStrRev
(strNextName, "."), 3))
' End If
If strNextName <> ".." And strNextName <> "." Then
If strAttr = 16 Then
intDirCt = intDirCt + 1
Directory(intDirCt) = parDir & strNextName
& "\"
Else
intSize = FileLen(parDir & strNextName)
dttDateTime = FileDateTime(parDir &
strNextName)
End If
End If
If strExt <> "DLL" And strExt <> "EXE" Then
Debug.Print parDir, strNextName, strAttr, intSize,
dttDateTime
intRecCount = intRecCount + 1
With rstFileList
.AddNew
!FName = strNextName
!FPath = parDir
!FSize = intSize
!FDate = dttDateTime
!FAttribute = strAttr
.Update
End With
End If
strNextName = Dir()
Loop
If parSubDir Then
Dim intI As Integer
For intI = 1 To 50
If Directory(intI) <> "" Then
strNextName = ListFiles(Directory(intI), True)
End If
Next intI
End If
End Function