Import information using last modified file

[Kycanns]

So I want to import data from one spreadsheet into another. simple right?

No,
The excel file source I want to pull has a variable file name therefore I want to pull from the most recently saved file in that specific folder. The file I am importing into is in another folder altogether (if that information is needed)

What would the code look like if I wanted to create a button to execute this action?

 

[JoeMo]

So I want to import data from one spreadsheet into another. simple right?

No,
The excel file source I want to pull has a variable file name therefore I want to pull from the most recently saved file in that specific folder. The file I am importing into is in another folder altogether (if that information is needed)

What would the code look like if I wanted to create a button to execute this action?

Untested - you need to insert the path to your folder where noted (red font).
This will open the last modified Excel file in your folder. You can append code to export data from this file to your other file.

Sub Kycanns()
Dim fso As Object, fPath As String, Fld As Object, Last As Date, myFile As String
fPath = "Put your folder path between these quote marks"   '<-- NOTE
Set fso = CreateObject("Scripting.Filesystemobject")
Set Fld = fso.getfolder(fPath).Files
For Each f In Fld
    If f.datelastmodified > Last Then
        If f.Name Like "*.xls*" Then
            Last = f.datelastmodified
            myFile = f.Name
        End If
    End If
Next f
Workbooks.Open Filename:=fPath & Application.PathSeparator & myFile
End Sub

 

[Kycanns]

thanks! so close. this opens up an entirely new workbook. How would you change this so that the information is being imported into a new worksheet in the workbook that the code is running in?

 

[Kycanns]

I should also add the file that I want to import is a shared file

 

[JoeMo]

thanks! so close. this opens up an entirely new workbook. How would you change this so that the information is being imported into a new worksheet in the workbook that the code is running in?

Can't see how that can happen - what is the value of myFile when a new workbook is opened?

 

[Kycanns]

Got it figured out. I had to un-share the file and it worked! thank you very much!!!

 

[JoeMo]

Got it figured out. I had to un-share the file and it worked! thank you very much!!!

You are welcome - thanks for the reply.