Solved

Import data from a excel file to Access table

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

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…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…

911 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

16 Experts available now in Live!

Get 1:1 Help Now