Macro help, need to copy cells from unopened file

blthlt

Board Regular
Joined
Jan 22, 2013
Messages
58
Please help, I know this is easy I'm just missing something. I want to take the last created excel file in a directory and copy from A2:L300 and paste that in the xls that will house this vba code in the respective cells A2:L300. This is what I have so far. The files can be in the same folder if needed.

Code:
Sub Workbook_open()
    Dim FileSys As Object
    Dim objFile As Object
    Dim myFolder
    Dim strFilename As String
    Dim dteFile As Date
    Dim FileName As String
    Dim path As String
    Dim currentWb As Workbook
    Dim openWb As Workbook
    Dim openWs As Worksheet
    
    'set path for files - change for your folder
'Set const on next line to your folder path
    Const myDir As String = "C:\Users\BTATE\Downloads"
    
    'set up filesys objects
    Set FileSys = CreateObject("Scripting.FileSystemObject")
    Set myFolder = FileSys.GetFolder(myDir)
        
    'loop through each file and get date last modified. If largest date then store Filename
    dteFile = DateSerial(1900, 1, 1)
    For Each objFile In myFolder.Files
        'Debug.Print objFile.Name
            If InStr(1, objFile.Name, ".xls") > 0 Then
            If objFile.DateLastModified > dteFile Then
                dteFile = objFile.DateLastModified
                strFilename = objFile.Name
            End If
        End If
    Next objFile
   ' Workbooks.Open myDir & Application.PathSeparator & strFilename
    FileName = myDir & Application.PathSeparator & strFilename
    'MsgBox (FileName)
    Set FileSys = Nothing
    Set myFolder = Nothing
End Sub
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Watch MrExcel Video

Forum statistics

Threads
1,130,448
Messages
5,642,218
Members
417,262
Latest member
andrewd1

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