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?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,907
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,907
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!!!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,228
Messages
5,836,090
Members
430,404
Latest member
goncaloColt

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