Locate certain file in the download folder and then copy and paste content to another worksheet in another workbook

[Pookiemeister]

There are four of us that are tasked with code creating multiple spreadsheets that tracks a lot of data. This is one big project that we decided to split in half. Two of us will take one half. One spreadsheet we will be creating will be named: "NotTheRealName1" and the other will be named "NotTheRealName2". We've decided to use github for this project.

I found this code online and pasted in the workbook all these spreadsheets would be going into. In order for this code to work, I had to enable the "Microsoft Scripting Runtime" within references under "Tools". I did modify it a little to make it more dynamic. I'm not sure exactly what all this does but I am sure, I don't need a lot of it.

1. To start, I just need it to find the line to modify that will find these two files (one at a time).
2. Once it finds one of the files, it will open it, Copy the entire spreadsheet (ctrl+A) and then finds the spreadsheet within the workbook with the exact name and then it will paste the it starting in Range("A1").

Sub ImportMostRecentFile()

Dim fso As Scripting.FileSystemObject
Set fso = New Scripting.FileSystemObject

Dim MyFolder As Scripting.Folder
Set MyFolder = fso.GetFolder("C:\Users\" & Environ("username") & "\Downloads")

Dim Fle As Scripting.File
Dim NewestFile As Scripting.File

ThisWorkbook.FollowHyperlink Address:="https://github.com/login", NewWindow:=True

For Each Fle In MyFolder.Files

   If NewestFile Is Nothing Then Set NewestFile = Fle
      If NewestFile.DateCreated < Fle.DateCreated Then Set NewestFile = Fle

Next Fle


Workbooks.Open NewestFile
ActiveWorkbook.Worksheets("Sheet1").Copy after:=Workbooks("Import Latest File.xlsm").Worksheets("Sheet1")
ActiveSheet.Name = NewestFile.Name

Workbooks(NewestFile.Name).Close

End Sub

 

[Micron]

If other users could ever have different versions of Office I think you will have an issue with the Scripting reference. To validate where the library file ( .dll, .tlb, .olb, etc.) is located would not be worth the effort. Their library files will likely be in a different location. I'd suggest ditching the reference as you don't really need it. Plus, I have read up about how this library is being deprecated but can't recall the timeline or affects of that.

Based on your post description, I think all you need is the msoFileDialogFilePicker, which is built into VBA. That would allow a user to select a file, then you could copy from the range. If you want the user to also navigate to and pick the target file, then I'd open one dialog and return the path, then open another for the target file and return that path. Each dialog can have its own title to (perhaps) make the operation less confusing as to what's going on.

Less flexible but still useful for some file operations is the Dir() function, which also does not require that reference.