Solved

Import data from a excel file to Access table

Posted on 2004-08-23
1
218 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

821 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