Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro that identify the newest file in a folder and open it.
hi,
i have folder that contain one new file each day. It's a text format file. The naming format is as follow: The folder is located at C:\dividend. And if today's date is jan 1 2008 then the file is dividend.yester.Jan012008.txt. So the next day, the new file would be dividend.yester.Jan022008.txt. These file only created during weekday. I have to import the content of these text file into excel each day. Is it possible to create a macro in excel that would automatically locate the most recent file and import the content automatically?? Thanks. Don |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro that identify the newest file in a folder and open it.
Is it always the next day?
What about on Sunday and Monday? Either way, maybe you can modify this: Option Explicit Sub testme() Dim PrevDayTxtFileName As String Dim PrevDay As Date Dim CurDate As Date CurDate = Date 'change the date here for testing Select Case Weekday(CurDate, vbMonday) Case Is = 2, 3, 4, 5, 6 'Tues - Sat PrevDay = CurDate - 1 'Mon - Fri Case Is = 7 'Sun PrevDay = CurDate - 2 'Fri Case Is = 1 'Mon PrevDay = CurDate - 3 'Fri End Select PrevDayTxtFileName _ = "dividend.yester." & Format(PrevDay, "mmmddyyyy") & ".txt" MsgBox PrevDayTxtFileName End Sub Don Doan wrote: hi, i have folder that contain one new file each day. It's a text format file. The naming format is as follow: The folder is located at C:\dividend. And if today's date is jan 1 2008 then the file is dividend.yester.Jan012008.txt. So the next day, the new file would be dividend.yester.Jan022008.txt. These file only created during weekday. I have to import the content of these text file into excel each day. Is it possible to create a macro in excel that would automatically locate the most recent file and import the content automatically?? Thanks. Don -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro that identify the newest file in a folder and open it.
Hello, you can do this using VB script, you can ge tthe date and time stamp
of the file, and check when the file was created. Hope this is helpful Anand.V.V.N -- "Who will guard the guards?" "Don Doan" wrote: hi, i have folder that contain one new file each day. It's a text format file. The naming format is as follow: The folder is located at C:\dividend. And if today's date is jan 1 2008 then the file is dividend.yester.Jan012008.txt. So the next day, the new file would be dividend.yester.Jan022008.txt. These file only created during weekday. I have to import the content of these text file into excel each day. Is it possible to create a macro in excel that would automatically locate the most recent file and import the content automatically?? Thanks. Don |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro that identify the newest file in a folder and open it.
Try this code. I don't know how you want to read the text file so I did not
include code to do the importing, just the code to find the file with the the latest filename. I also noticed you had an extra 0 between the three character Month name and the date. Is this correct? Sub getlatest() Folder = "c:\dividend\" Prefix = "dividend.yester." First = True Do If First = True Then Filename = Dir(Folder & "*.txt") Else Filename = Dir() End If If Filename < "" Then If Filename = Left(Filename, Len(Prefix)) Then FileDateString = Mid(Filename, Len(Prefix) + 1) 'remove .txt FileDateString = Left(FileDateString, _ Len(FileDateString) - 4) 'file date has a three character month followed by an extra 0 FileDate = DateValue(Left(FileDateString, 3) & " " & _ Mid(FileDateString, 5, 2) & " " & _ Mid(FileDateString, 7, 4)) If First = True Then LatestFile = Filename LatestDate = FileDate Else If FileDate LatestDate Then LatestFile = Filename LatestDate = FileDate End If End If End If End If First = False Loop While Filename < "" ' open file named = Folder & LatestFile ' ' Enter code to import data file here ' End Sub "Don Doan" wrote: hi, i have folder that contain one new file each day. It's a text format file. The naming format is as follow: The folder is located at C:\dividend. And if today's date is jan 1 2008 then the file is dividend.yester.Jan012008.txt. So the next day, the new file would be dividend.yester.Jan022008.txt. These file only created during weekday. I have to import the content of these text file into excel each day. Is it possible to create a macro in excel that would automatically locate the most recent file and import the content automatically?? Thanks. Don |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro that identify the newest file in a folder and open it.
Hi Don,
One way you could do it is to look at the newest file created in the folder, this could involve creating a File System object and examining the folder. However, if you already know much of the filename, and it is only the date portion that is changing, then here's an easier way to do it: Dim strDay As String Dim strMon As String Dim strYear As String Dim dteDate As Date Dim strFilename As String dteDate = Date strDay = Format(dteDate, "dd") strMon = Format(dteDate, "mmm") strYear = Format(dteDate, "yyyy") strFilename = "dividend.yester." & strMon & strDay & strYear & ".txt" This will give you a filename representing today's date. Should you wish to look at the day before then change the dteDate line to: dteDate = DateAdd("d", -1, Date) etc I hope this helps, Sean. -- (please remember to click yes if replies you receive are helpful to you) "Don Doan" wrote: hi, i have folder that contain one new file each day. It's a text format file. The naming format is as follow: The folder is located at C:\dividend. And if today's date is jan 1 2008 then the file is dividend.yester.Jan012008.txt. So the next day, the new file would be dividend.yester.Jan022008.txt. These file only created during weekday. I have to import the content of these text file into excel each day. Is it possible to create a macro in excel that would automatically locate the most recent file and import the content automatically?? Thanks. Don |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro that identify the newest file in a folder and open it.
First, I misread the question. I thought that file would be based on the
previous workday's date. Dave Peterson wrote: Is it always the next day? What about on Sunday and Monday? Either way, maybe you can modify this: Option Explicit Sub testme() Dim PrevDayTxtFileName As String Dim PrevDay As Date Dim CurDate As Date CurDate = Date 'change the date here for testing Select Case Weekday(CurDate, vbMonday) Case Is = 2, 3, 4, 5, 6 'Tues - Sat PrevDay = CurDate - 1 'Mon - Fri Case Is = 7 'Sun PrevDay = CurDate - 2 'Fri Case Is = 1 'Mon PrevDay = CurDate - 3 'Fri End Select PrevDayTxtFileName _ = "dividend.yester." & Format(PrevDay, "mmmddyyyy") & ".txt" MsgBox PrevDayTxtFileName End Sub Don Doan wrote: hi, i have folder that contain one new file each day. It's a text format file. The naming format is as follow: The folder is located at C:\dividend. And if today's date is jan 1 2008 then the file is dividend.yester.Jan012008.txt. So the next day, the new file would be dividend.yester.Jan022008.txt. These file only created during weekday. I have to import the content of these text file into excel each day. Is it possible to create a macro in excel that would automatically locate the most recent file and import the content automatically?? Thanks. Don -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro that identify the newest file in a folder and open it.
One way that doesn't rely on the last updated date of the text file (this could
change if someone updates the file) is to just look. Option Explicit Sub testme() Dim TxtFileName As String Dim CurDate As Date Dim TestDate As Double Dim TestStr As String Dim FoundIt As Boolean Dim dCtr As Long CurDate = Date 'today TestDate = CurDate FoundIt = False dCtr = 0 Do TestStr = "" On Error Resume Next TxtFileName = "c:\my documents\excel\dividend.yester." _ & Format(TestDate, "mmmddyyyy") & ".txt" TestStr = Dir(TxtFileName) On Error GoTo 0 If TestStr = "" Then TestDate = TestDate - 1 dCtr = dCtr + 1 If dCtr 100 Then 'not found in 100 previous days, get out! Exit Do End If Else FoundIt = True Exit Do End If Loop If FoundIt = False Then MsgBox "A previous file wasn't found!" Else MsgBox "The filename to use is: " & TxtFileName End If End Sub Don Doan wrote: hi, i have folder that contain one new file each day. It's a text format file. The naming format is as follow: The folder is located at C:\dividend. And if today's date is jan 1 2008 then the file is dividend.yester.Jan012008.txt. So the next day, the new file would be dividend.yester.Jan022008.txt. These file only created during weekday. I have to import the content of these text file into excel each day. Is it possible to create a macro in excel that would automatically locate the most recent file and import the content automatically?? Thanks. Don -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to open most current file in folder | Excel Discussion (Misc queries) | |||
Macro to open folder and let you chose file, then continue import | Excel Programming | |||
Macro syntax to open file in current explorer folder | Excel Discussion (Misc queries) | |||
using macro to open every file in a folder | Excel Programming | |||
using macro to open every file in a folder | Excel Programming |