Pulling Downloaded File

[dtb912]

I am looking to do a complicated task with a macro. For work, one of my responsibilities is updating data on a program that runs online. When I export the data into excel, it creates a new file with a new filename in the Downloads Folder. Is there a way to build a macro that:

1) Opens the latest downloaded file from the Downloads Folder (regardless of filename)
2) Saves it in a specific folder of choice with a dynamic filename

I can build additional macros from there, I just don't know the one above.

Thanks for the help.

 

[GWteB]

What you are asking for is basically determining the name of a file located in a specific folder, based on the most recent date & time stamp of all files in that folder and moving that file to another specific folder and finally renaming that file. If so then you might want to check this out. It's similar to your task although it needs to be adjusted properly and more details are needed.

 

[GWteB]

Maybe you can try this, although it probably needs some adjustments to suit your situation.

Option Explicit

Public Sub GetMostRecentlyFile()

    ' depends on Function DictSortByValue()

    Dim oDict       As Object
    Dim oFSO        As Object
    Dim oFile       As Object
    Dim sPath       As String
    Dim sFileName   As String
    Dim sFileDate   As Date

    sPath = "D:\User\Downloads"         ' <<<< change as required

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    If oFSO.FolderExists(sPath) Then
        Set oDict = CreateObject("Scripting.Dictionary")
        For Each oFile In oFSO.GetFolder(sPath).Files
            oDict.Add oFile.Name, oFile.DateCreated
        Next oFile
        Set oDict = DictSortByValue(oDict, xlDescending)
        
        sFileName = oDict.Keys()(0)             ' <<<< file name of most recent file
        sFileDate = oDict.Items()(0)            ' <<<< its date/time stamp of creation
        
        MsgBox "The most recently created file in" & vbCrLf & _
                sPath & vbCrLf & _
                "is: " & sFileName & vbCrLf & _
                "created on: " & sFileDate, vbInformation, "GetMostRecentlyFile"
        oDict.RemoveAll
        Set oDict = Nothing
    Else
        MsgBox "Folder " & sPath & " does not exist.", vbExclamation, "GetMostRecentlyFile"
    End If
SUB_QUIT:
    Set oFSO = Nothing
End Sub

Public Function DictSortByValue(argDict As Object, Optional argSortOrder As XlSortOrder = xlAscending) As Object

    ' == sort a dictionary by its values ==

    Dim arrList     As Object
    Dim dictTmp     As Object
    Dim coll        As Collection
    Dim vKey        As Variant
    Dim vValue      As Variant
    Dim vItem       As Variant

    Set arrList = CreateObject("System.Collections.ArrayList")
    Set dictTmp = CreateObject("Scripting.Dictionary")

    On Error GoTo SUB_ERROR
    ' Put all values from argDict (as a key) in an ArrayList and
    ' necessarily omit repetitions because a key can only occur once.
    ' Also put those values in dictTmp with their keys as a collection
    For Each vKey In argDict
        vValue = argDict(vKey)
        ' if the value doesn't exist in dictTmp then add
        If Not dictTmp.Exists(vValue) Then
            ' create a collection to store the keys
            ' needed for duplicate values
            Set coll = New Collection
            dictTmp.Add vValue, coll
            ' Add the value to the ArrayList
            arrList.Add vValue
        End If
        ' Add the current key to the collection
        dictTmp(vValue).Add vKey
    Next vKey
    ' sort in ascending order
    arrList.Sort
    If argSortOrder = xlDescending Then
        ' reverse needs a sorted list for a descending result
        arrList.Reverse
    End If
    ' initialize argDict for new input
    argDict.RemoveAll
    ' Read through the ArrayList and add the values
    ' and corresponding keys from dictTmp to argDict
    For Each vValue In arrList
        Set coll = dictTmp(vValue)
        For Each vItem In coll
            argDict.Add vItem, vValue
        Next vItem
    Next vValue
    Set arrList = Nothing
    ' Return the new dictionary
    Set DictSortByValue = argDict
    
SUB_DONE:
    Exit Function

SUB_ERROR:
    If Err.Number = 450 Then
        Err.Clear
        arrList.Clear
        Set arrList = Nothing
        Set dictTmp = Nothing
        Set coll = Nothing
        Err.Raise Number:=vbObjectError + 100, _
                  SOURCE:="Procedure: DictSortByValue", _
                  Description:="Cannot sort the dictionary if the value is an object"
    End If
End Function