Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Sub ImportOldRates()
- 'Declare the variables
- Dim MyPath As String
- Dim MyFile As String
- Dim LatestFile As String
- Dim LatestDate As Date
- Dim LMD As Date
- 'Specify the path to the folder
- MyPath = "C:Folder1Folder2"
- 'Make sure that the path ends in a backslash
- If Right(MyPath, 1) <> "" Then MyPath = MyPath & ""
- 'Get the first Excel file from the folder
- MyFile = Dir(MyPath & "*.xls", vbNormal)
- 'If no files were found, exit the sub
- If Len(MyFile) = 0 Then
- MsgBox "No files were found...", vbExclamation
- Exit Sub
- End If
- 'Loop through each Excel file in the folder
- Do While Len(MyFile) > 0
- 'Assign the date/time of the current file to a variable
- LMD = FileDateTime(MyPath & MyFile)
- 'If the date/time of the current file is greater than the latest
- 'recorded date, assign its filename and date/time to variables
- If LMD > LatestDate Then
- LatestFile = MyFile
- LatestDate = LMD
- End If
- 'Get the next Excel file from the folder
- MyFile = Dir
- Loop
- 'Open the latest file
- Workbooks.Open MyPath & LatestFile
- Application.DisplayAlerts = False
- Application.EnableEvents = False
- Application.Run "ConnectChartEvents"
- Cells.Select
- Range("E2").Activate
- Selection.copy
- ActiveWindow.Close
- ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
- DisplayAsIcon:=False, NoHTMLFormatting:=True
- Application.CutCopyMode = False
- Selection.Columns.AutoFit
- Range("A1").Select
- Application.DisplayAlerts = True
- Application.EnableEvents = True
- End Sub
- Application.Wait (Now + TimeValue("0:00:01"))
- Range("E2").Activate
- Range("A1,Z400").Activate
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement