latest date

[slimjen]

All, using Access 2010. I have some vba code to import from a folder a spreadsheet into my database using transferspreadsheet method. Right now the file I am importing is in a folder with no other files. But now; there can be multiple files of the same file with different dates for example:

 2014 07 08 testfile
2014 07 23 testfile
2014 08 20 testfile
2014 09 10 testfile

How do I format my code to look at the files in the folder and import the file with the latest date? Thanks

[ranman256]

Here's some code to import ALL excel files in a foldler and some to import ALL sheets in the workbook...

[code]
'import all files in folder
'------------------
Public Sub ImportAllXLFilesAndSheets(ByVal pvDir)
'------------------
Dim vFil, vTargT
Dim i As Integer
Dim sTbl As String, sSql As String
Dim tdf As TableDef
Dim colSheets As New Collection
Dim vSheet, vQry, oFolder, oFile
sTbl = "xlFile" 'linked xl file to import
On Error GoTo errImp
Set db = CurrentDb
Select Case True
Case pvDir = ""
MsgBox "No source folder given", vbCritical, "Error"

Case Else
DoCmd.Hourglass True
DoCmd.SetWarnings False
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(pvDir)
For Each oFile In oFolder.Files
If InStr(oFile, ".xls") > 0 Then 'only excel files
vFil = pvDir & oFile.Name

'get all the sheets in the workbook
Set colSheets = getAllSheetsInWb(vFil)
For Each vSheet In colSheets
DeleteTbl sTbl 'remove prev xl file

'ATTACH THE WORKBOOK via sheet
DoCmd.TransferSpreadsheet acLink, sTbl, vFil, True, vSheet

'append data
DoCmd.OpenQuery "qaImportXlFile"
Next 'sheet
End If
Next 'file
End Select
Set colSheets = Nothing
Set oFile = Nothing
Set oFolder = Nothing
DoCmd.Hourglass False
DoCmd.SetWarnings True
Exit Sub
errImp:
MsgBox Err.Description, vbCritical, "ImportAllXLFilesAndSheets()" & Err
Exit Sub
Resume
End Sub
'remove the table from db
'------------------
Private Function DeleteTbl(ByVal pvTbl)
'------------------
Dim db As Database
On Error Resume Next
Set db = CurrentDb
db.TableDefs.Delete pvTbl
db.TableDefs.Refresh
Set db = Nothing
End Function
'get all sheets in the workbook
'------------------
Private Function getAllSheetsInWb(ByVal pvFile) As Collection
'------------------
Dim xl As excel.Application
Dim sSht As String
Dim colSheets As New Collection
Set xl = CreateObject("excel.application")
With xl
.Visible = True
.Workbooks.Open pvFile
For Each sSht In .Sheets
colSheets.Add sSht, sSht
Next
.ActiveWorkbook.Close False
.Quit
End With
Set xl = Nothing
Set getAllSheetsInWb = colSheets
End Function
[/ode]

[slimjen]

I appreciate the response and code. But I need only the file with the latest date imported???

[ranman256]

oops, I thought is was all..
in the code that scans ALL files, check the date as you go, save the max date , then at the end, open THAT FILE.

usage: vDate = getFileProperty(vFile, 5) 'get the date property of the file
if vDate > vMax then
vMaxFile = vFile
endif

Public Function getFileProperty(ByVal pvFile, ByVal pvAttrib)
'PROPERTIES VALUES
'5 date stamp
' 9 Author
'10 Title Author
'11 Subject Title
Dim vDir, f
Dim vDat
getDirName pvFile, vDir, f
sFileName = f
sFolderPathspec = vDir
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(sFolderPathspec)
vDat = objFolder.GetDetailsOf(objFolder.Parsename(sFileNa me), pvAttrib)

getFileProperty = vDat
Set fs = Nothing
Set f = Nothing
End Function

[slimjen]

I'm so sorry Ranman; I can't decifer your code to incorporate it into my database. Can you walk me through where to put where? Thanks

[ssanfu]

Here is an example picking a folder then checking for the latest date in the file name.
The file name must begin with: yyyy mm dd where
yyyy is the 4 digit year
mm is the 2 digit month and
dd is the 2 digit day.

There must be a space between each of the date parts.
"2014 09 01 test.xlsx" <= good
"20140901 test.xlsx" <= bad
"2014 9 1 test.xlsx" <= bad
"2014 9 11 test.xlsx" <= bad

[slimjen]

Ok Steve. My brain is working a little better than last night. I'm using this code behind a cmd button:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
 "tbl_Empl", "C:\folder\Empl.xlsx", True

How do I modifiy this code to reference the sfile?? Do I replace the "C:\folder\Empl.xlsx" with form1? I just don't know the correct way. Thanks

[slimjen]

This is what I came up with start: I keep the form1 open then behind my import cmd button:

Dim strFile As String
strFile = Me.Form.form1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    "tbl_temp1", strFile, True

I get error: Application Defined or object defined error

[ssanfu]

Unless you changed my code, "strFile" should be "sFile". ("s" not "str")

I would have to know what your process (code) currently is to import a file..

Attached is another example (quit rolling your eyes! )

Put the Excel files in a folder (remember which folder) and put the dB in a trusted location.
Open the dB.
Follow the steps

Look at/trace the code.

[slimjen]

Ha Ha. Thanks

[slimjen]

ok. this works great. Is there anyway to get the folder hardcoded instead of asking the user to browse for the folder? I really appreciate all you help.

[slimjen]

Ahh. I got it. Again; thanks for all your help!!