PDA

View Full Version : Open file with datestamp in filename



Riaaz66
07-12-2010, 03:19 AM
Hi,

Every morning I have to get data from another worksheet which has every day another name due to the datestamp every day.
For example: today the file from which I need to get data is called "daily_p&l_20100712.xml" and tomorrow it will be "daily_p&l_20100713.xml"

Now how do I start my code in VBA to open this file and copy data from it to my worksheet?

Please provide me a start???

Kind regards,
Riaaz66

Dr.K
07-12-2010, 09:20 AM
You can always use DIR to check for a specific file, or iterate through all the variations in a directory that start with the same string.

Your case is a little simpler.

Dim strInputFile As String
Dim datInputFile As Date


datInputFile = "7/12/2010"

strInputFile = "PATH\daily_p&l_" & Format(datInputFile, "YYYYMMDD") & ".xml"

Workbooks.Open strInputFile

Riaaz66
07-12-2010, 11:53 AM
Hey Dr.K

Thank you very much for your reply. But I think this won't work. In your code I have to specify the date manually every day. I think I have to change my approach to solve this.

Because the .xml file I need on daily base is always the last generated one in the night or morning, I think it is better to open the last modified file which start with "daily-p&l", isn't it?

Do you or somebody know how to open the last modified file?

Regards,

Riaaz66

Dr.K
07-12-2010, 12:16 PM
That's because I meant for that to be a general example; you don't have to hard code the date.

HOWEVER... if you are going to always be using it same day, you can use the Date() function. This function returns the current date for you. Try going to the Immediate window, and typing "? Date" and hit enter. The code above uses Date().

If you want to check file properties, you'll need to use the FileSystem Object. you iterate through each file, store the last modified date in a variable, replacing it when you find a higher one. Then you can use that variable to build the name of the file to open.

EDIT: My example was ambiguous. I have gone back and fixed it. here is the same line using the Date() function.

strInputFile = "PATH\daily_p&l_" & Format(Date, "YYYYMMDD") & ".xml"