UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Filter Folder For Time, Access 2016    
 
   
whitechair
post Jun 18 2019, 02:08 PM
Post#1



Posts: 491
Joined: 26-June 08



I would like to have a button that automatically selects the most recent excel file in the downloads folder and Imports it. I have the following VBA that works for filtering the file type, and impoting the slected file, but I would like to combine the two into one step. In order to do this I need Access to identify the proper file without user input.

Here is the code to bring up the dialog box:
CODE
Private Sub cmdBrowse_Click()
    Dim diag As Office.FileDialog
    Dim item As Variant

    Set diag = Application.FileDialog(msoFileDialogFilePicker)
    diag.AllowMultiSelect = False
    diag.Title = "Please select the most recent Excel document."
    diag.Filters.Clear
    diag.Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx"
    
    If diag.Show Then
        For Each item In diag.SelectedItems
          Me.txtFileName = item
        Next
    End If
End Sub


Here is the code to import the selected file:
CODE
Private Sub cmdImportSpreadsheet_Click()
    Dim FSO As FileSystemObject
    
    If Nz(Me.txtFileName, "") = "" Then
        MsgBox "Please select a file!"
        Exit Sub
    End If
    
    'If FSO.FileExists(Me.txtFileName) Then '''''this is not working but I triple checked it according to the video I watched on YouTube "How To Import An Excel Spreadsheet With VBA In Access 2013"
        ExcelImport.ImportExcelFile Me.txtFileName, "Temp"
    'Else
        'MsgBox "File not found."
    'End If
    Me!txtFileName = ""
End Sub


--------------------
Jeff Moseler
Access 2007
Go to the top of the page
 
June7
post Jun 18 2019, 03:27 PM
Post#2



Posts: 653
Joined: 25-January 16



Consider:
CODE
Public Sub LastExcel()
Dim fso As Object
Dim SourceFolder As Object
Dim FileItem As Object
Dim dteDate As Date, strFile As String
Set fso = CreateObject("Scripting.FileSystemObject")
Set SourceFolder = fso.GetFolder("C:\Users\June\")

For Each FileItem In SourceFolder.Files
    If dteDate < FileItem.DateCreated And FileItem.Type Like "*Excel*" Then
        dteDate = FileItem.DateCreated
        strFile = FileItem.Path
    End If
Next
Debug.Print strFile
End Sub


This post has been edited by June7: Jun 18 2019, 03:30 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
gemmathehusky
post Jun 18 2019, 03:54 PM
Post#3


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


just an observation.

there is a vba command to get the last modified date of a diskfile
filedatetime()

eg - the active database
CODE
MsgBox FileDateTime(CurrentProject.path & "\" & CurrentProject.Name)

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
June7
post Jun 18 2019, 04:31 PM
Post#4



Posts: 653
Joined: 25-January 16



Nice. Here is alternative. Difference between the two procedures is first reads Created date and this one is supposed to be last modified but can't get to agree with DateLastModifed parameter when used in first procedure. Also tried DateLastAccessed in first. Both procedures assume each file has different date.
CODE
Public Sub LastExcel()
    Dim strPath As String, strFile As String, strFileNext As String, dteDate As Date
    strPath = "C:\Users\June\"
    strFileNext = Dir(strPath & "*.xls*")
    dteDate = 0
    Do While strFileNext <> ""
        If dteDate < FileDateTime(strPath & strFileNext) Then
            dteDate = FileDateTime(strPath & strFileNext)
            strFile = strPath & strFileNext
        End If
        strFileNext = Dir
    Loop
Debug.Print strFile
End Sub

This post has been edited by June7: Jun 18 2019, 05:16 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
June7
post Jun 18 2019, 06:22 PM
Post#5



Posts: 653
Joined: 25-January 16



Correction, assumes each file has different date/time. As it is unlikely files will have same exact same date/time, should not be an issue.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
WildBird
post Jun 18 2019, 10:22 PM
Post#6


UtterAccess VIP
Posts: 3,578
Joined: 19-August 03
From: Auckland, Little Australia


I have code that you pass a parameter of a search string, extension, and folder and it will return the latest file name.

I use it for reports, I usually have multiple folders with reports that I datestamp into the name e.g. 20190619151645AnnualLeaveReport.xlsm
Could be hundreds of them.

I have a Excel file that uses an .ini file that has parameters in it, and it will check the folder for the files, and download the latest file that matches the criteria.

It means I can have multiple types of reports and instead of having users search for and open a network version, therefore locking it for everyone else, everyone gets their own copy. Users just have a shortcut to each file for each file type.

This could be adapted to search for Excel files in a download folder and link etc.

Question - is it only on one users machine? Or is it easy enough to get the path to the Downloads folder? (eg C:\Users\"UserName"\Downloads\)

Where are you linking to? Is the database local or network based?


--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
whitechair
post Jun 19 2019, 12:56 PM
Post#7



Posts: 491
Joined: 26-June 08



These are all great, I'm going to take a look and let you know which one works! Thanks again everyone!

--------------------
Jeff Moseler
Access 2007
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th July 2019 - 05:25 PM