Solved

Import data from a excel file to Access table

Posted on 2004-08-23
1
215 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:sramkris
1 Comment
 
LVL 5

Accepted Solution

by:
peterpuscas earned 100 total points
ID: 11877434
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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now