Import information using last modified file

Kycanns

New Member
Joined
Feb 20, 2012
Messages
26
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?
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,211
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
Rich (BB code):
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
 
Last edited:

Kycanns

New Member
Joined
Feb 20, 2012
Messages
26
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?
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,211
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Feb 20, 2012
Messages
26
Got it figured out. I had to un-share the file and it worked! thank you very much!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,046
Messages
5,526,456
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top