Pulling Downloaded File

dtb912

New Member
Joined
Jul 19, 2019
Messages
9
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.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Maybe you can try this, although it probably needs some adjustments to suit your situation.
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,528
Messages
5,636,852
Members
416,945
Latest member
Himu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top