How to update existing tables of data from exported excel files.

[Mrdude1020]

Ive tried making command buttons for each of the three tables that when you click on it, it automatically updates the tables, but it doesnt seem to be updating them. Is this the right code for that to happen?

Private Sub Command0_Click()

Dim strFile As String

DoCmd.SetWarnings False

' Set file directory for files to be imported
strPath = "C:\Shares\Public\Staff Public Files\Brandon Penland\US Food Product Prices Newest\"
' Tell it to import all Excel files from the file directory
strFile = Dir(strPath & "*.xls*")

' Start loop


Do While strFile <> ""
' Import file
DoCmd.TransferSpreadsheet transfertype:=acImport, tablename:="US Food Product Prices Newest", FileName:=strPath & strFile, HasFieldNames:=True, Range:="default"
' Loop to next file in directory
strFile = Dir
Loop

MsgBox "All data has been imported.", vbOKOnly
End Sub

[ranman256]

No, its a bit more than that....

Public Sub ImportAllFilesInDir(ByVal pvDir)
Dim vFil, vTargT
Dim i As Integer
dim sSql As String
Dim db 'As Database
Dim fso
Dim oFolder, oFile
On Error GoTo errImp
If Right(pvDir, 1) <> "\" Then pvDir = pvDir & "\"
sTbl = "xlFile"
Set db = CurrentDb
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(pvDir)
For Each oFile In oFolder.Files
    vFil = pvDir & oFile.Name
    If InStr(sfile, ".xls") > 0 Then      'ONLY DO EXCEL FILES  
       DoCmd.TransferSpreadsheet transfertype:=acImport, tablename:="US Food Product Prices Newest", FileName:=vFil , HasFieldNames:=True, Range:="default"
    endif   
Next
Set db = Nothing
Set fso = Nothing
Set oFile = Nothing
Set oFolder = Nothing
DoCmd.SetWarnings True
Exit Sub
errImp:
MsgBox Err.Description, vbCritical, "ImportAllFilesInDir():" & Err
End Sub