Import the .CSV file by todays date

[Dwight Cook]

I need the following program to import the .CSV file by todays date. I have looked everywhere but I cannot find what I need.
The name of the CSV file changes every day, so I need to be able to find it by the Date it was modified.
The Lines I need the dates for are lines 15 and 41.
Thanks
Dwight cook

1. Dim NextTime      As Date

2. Function LastModTime(FileSpec As String) As Date
3.   'Returns the date-time the file specified by FileSpec (path string) was last modified
4.    Dim fs, f, s
5.    Set fs = CreateObject("Scripting.FileSystemObject")
6.    Set f = fs.GetFile(FileSpec)
7.    LastModTime = f.Datelastmodified
8.End Function

9. Sub Check4Changes()
10.   'Checks the file FilePath for changes every 120 seconds
11.   'If file has changed, pops up a message box. Stores the
12.   'last modified time in cell M1 of Sheet1
13.   Dim LastMod       As Date
14.   ChDir "Q:\Manufacturing\Equipment\DispatchLogs\logs\7-DES"
15.   Const FilePath    As String = "Q:\Manufacturing\Equipment\DispatchLogs\logs\7-DES\"I need the Date here".CSV"
16.   On Error GoTo ReSchedule
17.   LastMod = LastModTime(FilePath)
18.   With Worksheets("Sheet1").Range("D1")
19.      If IsEmpty(.Value) Then
20.         .Value = LastMod
21.         GoTo ReSchedule
22.      ElseIf .Value < LastMod Then
23.         .Value = LastMod
24.         'MsgBox FilePath & " updated.", vbInformation, "Check4Changes"
25.         Call QuickKill
26.      End If
27.   End With
28. ReSchedule:
29.   'Reschedule this same routine to run in Two minutes.
30.   NextTime = Now + 2 / 1440
31.   Application.StatusBar = "Next check at " & NextTime
32.   Application.OnTime NextTime, "Check4Changes"
33.End Sub

34. Sub CancelChecking()
35.   Application.OnTime NextTime, "Check4Changes", Schedule:=False
36.   Application.StatusBar = False
37.End Sub
38.Sub Import_CSV()
'
39.' Import_CSV Macro
'

'
40.    With ActiveSheet.QueryTables.Add(Connection:= _
41.        "TEXT;Q:\Manufacturing\Equipment\DispatchLogs\logs\7-DES\"I need the date Here.CSV"", _
42.        Destination:=Range("$A$1"))
43.        .Name = "14031406"
44.        .FieldNames = True
45.        .RowNumbers = False
46.        .FillAdjacentFormulas = False
47.        .PreserveFormatting = True
48.        .RefreshOnFileOpen = False
49.        .RefreshStyle = xlInsertDeleteCells
50.        .SavePassword = False
51.        .SaveData = False
52.        .AdjustColumnWidth = True
53.        .RefreshPeriod = 0
54.        .TextFilePromptOnRefresh = False
55.        .TextFilePlatform = 437
56.        .TextFileStartRow = 1
57.        .TextFileParseType = xlDelimited
58.        .TextFileTextQualifier = xlTextQualifierDoubleQuote
59.        .TextFileConsecutiveDelimiter = False
60.        .TextFileTabDelimiter = False
61.        .TextFileSemicolonDelimiter = False
62.        .TextFileCommaDelimiter = True
63.        .TextFileSpaceDelimiter = False
64.        .TextFileColumnDataTypes = Array(3, 2, 1)
65.        .TextFileTrailingMinusNumbers = True
66.        .Refresh BackgroundQuery:=False
67.End With
68.Call Check4Changes
69.End Sub

[Richard Buttrey]

Hi and welcome to the forum. Unfortunately your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[Dwight Cook]

I apologize, I am new to the form and this will not happen again.
Thank you for pointing this out to me.
Dwight Cook

[Richard Buttrey]

Hi,

Does this Function help. I use it in one system to return the name of the latest time stamped file in a folder.

Function NewestFile(Directory, FileSpec)
' Returns the name of the most recent file in a Directory that matches the FileSpec (e.g., "*.xls").
' Returns an empty string if the directory does not exist or contains no matching files
    Dim FileName As String
    Dim MostRecentFile As String
    Dim MostRecentDate As Date
    If Right(Directory, 1) <> "\" Then Directory = Directory & "\"
    FileName = Dir(Directory & FileSpec, 0)
    If FileName <> "" Then
        MostRecentFile = FileName
        MostRecentDate = FileDateTime(Directory & FileName)
        Do While FileName <> ""
            If FileDateTime(Directory & FileName) > MostRecentDate Then
                MostRecentFile = FileName
                MostRecentDate = FileDateTime(Directory & FileName)
            End If
            FileName = Dir
        Loop
    End If
    NewestFile = MostRecentFile
End Function

[Dwight Cook]

Thank you for your quick response,
How would I add this to my program, what I have works if I edit the name.
I am not exactly sure where I need to add it, I do not have a lot of experience with VBA.
Thanks
Dwight