If you mean 250 WORKBOOKS in a folder then heres some code that scans all files in the folder and imports them to the table
Code:
sub LoadAllXLFiles()
ScanAllFilesInDir "C:\Users\iplayer\Documents\Odesk New1\41 Odeskrune Hansen mcro\Patient 47"
end sub
'----------------
Public Sub ScanAllFilesInDir(ByVal pvDir)
'----------------
Dim vFil, vTargT
Dim i As Integer
Dim fso
Dim oFolder, oFile
Dim vTbl
On Error GoTo errImp
If Right(pvDir, 1) <> "\" Then pvDir = pvDir & "\"
sTbl = "xlFile"
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(pvDir)
For Each oFile In oFolder.Files
vFil = pvDir & oFile.Name
If InStr(vFil, ".xls") > 0 Then 'ONLY DO XL FILES
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, vTbl, vFil ,True
End If
Next
Set fso = Nothing
Set oFile = Nothing
Set oFolder = Nothing
MsgBox "Done"
Exit Sub
errImp:
MsgBox Err.Description, vbCritical, "clsImport:ImportData()" & Err
Exit Sub
Resume
End Sub