Import data from a excel file to Access table

Hi All

I have a location in the system where the user stores an excel file with the data for the week. The data from this excel file has to be appended in to an access table.
The system has to pick the latest file from the folder (by picking the file with the latest date) and append the data in to the table.

How can this be done. Any code help will be highly appreciated.

Thanks
Ram

LVL 1
sramkrisAsked:
Who is Participating?
 
peterpuscasConnect With a Mentor Commented:
Hi sramkris,

you can use the function getLatestFile as it.
You have to change the folder (C:\TMP\TMPACCESS\), the table name (YourTable),
probably the spreadSheet type and the range from the spreadSheet


Private Sub Command0_Click()
  On Error Resume Next
  DoCmd.DeleteObject acTable, "YourTable"
  On Error GoTo 0
  Dim myFile As String
  myFile = getLatestFile("C:\TMP\TMPACCESS\")
  If Len(myFile) > 0 Then
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "YourTable", myFile, True, "A1:D3"
  End If
  '  Label1.Caption = myFile
End Sub

Function getLatestFile(myFolder As String) As String
  Dim fs, f
  Dim fileDate, lastDate As Date
  Dim crtFile, lastFile As String
 
  getLatestFile = ""
  Set xx = Application.FileSearch
  Application.FileSearch.LookIn = myFolder
  Application.FileSearch.FileName = "*.xls"
  xx = Application.FileSearch.Execute(msoSortByLastModified, msoSortOrderAscending, True)
 
  If Application.FileSearch.FoundFiles.Count > 0 Then
    '  getLatestFile = Application.FileSearch.FoundFiles(1)
     Set fs = CreateObject("Scripting.FileSystemObject")
     For i = 1 To Application.FileSearch.FoundFiles.Count
     crtFile = Application.FileSearch.FoundFiles(i)
     Set f = fs.GetFile(crtFile)
     fileDate = f.DateLastModified
     If i = 1 Then
       lastDate = fileDate
       lastFile = crtFile
     ElseIf lastDate < fileDate Then
       lastDate = fileDate
       lastFile = crtFile
     End If
     Next i
     getLatestFile = lastFile
  End If
End Function

Good Luck,
Peter
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.