Nilsjohanekman
New Member
- Joined
- Nov 19, 2016
- Messages
- 8
Hey community,
I'm trying to create a Macro that searches a specific folder for the most recent file, opens it, and pulls out all of the data which needs to be pasted on a specific worksheet of a different excel file.
Through some research I've been able to compile the following code (I have very limited knowledge) but I'm receiving an error message. (Error Message: Run-time error '1004': 'C:\Users\nilsjohanekman\Desktop\PO Macro\PO Business Intelligence Report\11/19/2016 5:26"06PM.xlsx' could not be found. Check the spelling of the file name, and verify that the file location is correct.) I believe the code I created looks for the most recently updated and then looks for a file name exactly as the folder and the time/date identified as most recent. There is no file in the folder that will be named as above (they are actually all named something similar to 'PO_report_490_20161120001201-1145.csv').
My code:
Sub SearchFolderforNewBIReport()
Dim fso As Object
Dim folder As Object
Dim wb As Workbook
Dim objFile As File
Dim dteFile As Date
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
'set up fso objects
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder("C:\Users\nilsjohanekman\Desktop\PO Macro\PO Business Intelligence Report")
'loop through each file and get date last modified. If largest date then...
dteFile = DateSerial(1900, 1, 1)
For Each objFile In folder.Files
If objFile.DateLastModified > dteFile Then
Set wb = Workbooks.Open(folder.Path & "" & objFile.DateLastModified).Copy
Workbooks("POManagement.xlsm").Sheets(2).Range ("A1")
wb.Close SaveChanges:=True
End If
Next
Set fso = Nothing
Set folder = Nothing
End Sub
Any advice?
Regards,
Nilsjohanekman
I'm trying to create a Macro that searches a specific folder for the most recent file, opens it, and pulls out all of the data which needs to be pasted on a specific worksheet of a different excel file.
Through some research I've been able to compile the following code (I have very limited knowledge) but I'm receiving an error message. (Error Message: Run-time error '1004': 'C:\Users\nilsjohanekman\Desktop\PO Macro\PO Business Intelligence Report\11/19/2016 5:26"06PM.xlsx' could not be found. Check the spelling of the file name, and verify that the file location is correct.) I believe the code I created looks for the most recently updated and then looks for a file name exactly as the folder and the time/date identified as most recent. There is no file in the folder that will be named as above (they are actually all named something similar to 'PO_report_490_20161120001201-1145.csv').
My code:
Sub SearchFolderforNewBIReport()
Dim fso As Object
Dim folder As Object
Dim wb As Workbook
Dim objFile As File
Dim dteFile As Date
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
'set up fso objects
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder("C:\Users\nilsjohanekman\Desktop\PO Macro\PO Business Intelligence Report")
'loop through each file and get date last modified. If largest date then...
dteFile = DateSerial(1900, 1, 1)
For Each objFile In folder.Files
If objFile.DateLastModified > dteFile Then
Set wb = Workbooks.Open(folder.Path & "" & objFile.DateLastModified).Copy
Workbooks("POManagement.xlsm").Sheets(2).Range ("A1")
wb.Close SaveChanges:=True
End If
Next
Set fso = Nothing
Set folder = Nothing
End Sub
Any advice?
Regards,
Nilsjohanekman