Daily file import timestamp shift

[tvman5683]

I have a daily file to import. If I hard code the entire file name my code works. My problem is getting around the daily name change of the file.
The owner has an automated process that creates the file so the name every day is slightly different due to the timestamp. Here's an example

Today's file is "mydailyfile_20140317_1035.xls"
Tomorrow will be "mydailyfile_20140318_1033.xls"

I can account for date using "sFileDate = Format(Now(), "yyyymmdd")"
The hour/minutes is never the same.
Is there a wildcard solution??

Thanks
JB

 

[AOB]

Hi JB,

You could use Dir?

Dim strPath As String
Dim strTodaysFile As String
 
strPath = "Insert your root folder here"
strTodaysFile = "mydailyfile_" & Format(Now(), "yyyymmdd") & "_*.xls"
 
If Dir(strPath & strTodaysFile) = "" Then
[INDENT]MsgBox "No File present for today"
[/INDENT]
 
 
Else
[INDENT]strTodaysFile = Dir(strPath & strTodaysFile)
[/INDENT]
 
 
End If

Haven't tested this but syntax should be roughly correct. Just make sure you have a "\" divider between the path and the filename (i.e. suffix your path) And remember that Dir will only return the filename (timestamp and all), not the full path.

 

[tvman5683]

Thanks,
It seems to work. Now I'll have to try and understand why so I'll know how to use it next time. Here's what it looks like.

Dim strPathFile As String, strFile As String, strPath As String, strTodaysFile As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True
'strPath = "S:\dailyETEreports\EndToEndTrackingSheet_"
'sFileDate = Format(Now() - 1, "yyyymmdd")
strPath = "S:\dailyETEreports\"
strTodaysFile = "EndToEndTrackingSheet_" & Format(Now() - 4, "yyyymmdd") & "_*.xls"
 
If Dir(strPath & strTodaysFile) = "" Then
MsgBox "No File present for today"
 
Else: strTodaysFile = Dir(strPath & strTodaysFile)
 
End If
 
strTable = "Order Tracking"
'strFile = sFileDate & "_*" & ".xls"
      strPathFile = strPath & strTodaysFile
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
            strTable, strPathFile, blnHasFieldNames

 

[gemma-the-husky]

check the help under format to see the full spec of a time field

basically

yy or yyyyy = year
mm = month number
mmm = month 3 letters
mmmm = month full
dd = day number
ddd = day 3 letters
dddd = day full
hh = hours
nn = minutes (we already used m as months)
ss = seconds

so you can format the date exactly as you want with this

 

[namliam]

I

do not know

why

do
we

type things in a certain

way....

I believe it is to keep things together and somewhat readable, readable code is maintainable code...
Dir simply fetches the full filename based on the wildcard name given....

Please do review: http://www.access-programmers.co.uk/forums/showthread.php?t=225837&highlight=name+convention, atleast for future projects but also this one if you you are not too deep into it yet.

 

[tvman5683]

Re: Daily file import timestamp shift solved

Thanks All,

AOB's advice has gotten me through this one. And i learned something new.

JB