We have users that are saving filenames with a date at the end of the filename, for example c:\file0207.txt. The 02 is the year and the 07 is the month. Is there a way to have Access 2000 in a macro or module allow me to import the file for the current date? For example, if the month is July when I run a macro, Access can determine the current month and pick up the 0207 file and not the 0206 file. I am trying to automate a process to try and prevent the user from having to do any input or file selecting when running reports. We have some reports that are being done by other staff and the possibility for errors must be reduced. Any immediate help is greatly appreciated!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Using a date variable in a filename 1
- Thread starter bigdars
- Start date
-
1
- #2
bigdars,
This might get you on the right track. I don't think that VBA has a date feature that ONLY shows month and date in a #### format. But I do know that the 'Date$' function will show the date in this format
07-16-2002. If you can get them to save the filenames as 'File##-##-####.txt' you could EASILY ACCOMPLISH THIS THROUGH CODE!! Horay!
Here's the file that you tell the code to look for
("file" & Date$ & ".txt"
. Check it out. If you want to test it just throw this into a module and run it.
Sub Test1()
MsgBox ("file" & Date$ & ".txt"
End Sub
Today the computer would search for 'file07-16-2002.txt' (assuming that the date is set correctly on your computer). Give it a shot! Maybe someone knows how to tell the date statement/function to format a certain way so you can keep saving them as File####.txt format. But this will work until then. Goodluck!
-Josh
------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------
This might get you on the right track. I don't think that VBA has a date feature that ONLY shows month and date in a #### format. But I do know that the 'Date$' function will show the date in this format
07-16-2002. If you can get them to save the filenames as 'File##-##-####.txt' you could EASILY ACCOMPLISH THIS THROUGH CODE!! Horay!
Here's the file that you tell the code to look for
("file" & Date$ & ".txt"
. Check it out. If you want to test it just throw this into a module and run it. Sub Test1()
MsgBox ("file" & Date$ & ".txt"
End Sub
Today the computer would search for 'file07-16-2002.txt' (assuming that the date is set correctly on your computer). Give it a shot! Maybe someone knows how to tell the date statement/function to format a certain way so you can keep saving them as File####.txt format. But this will work until then. Goodluck!
-Josh
------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------
- Thread starter
- #3
- Thread starter
- #4
It still does not recognize the file name. I have tried several variations and no success. The code works fine for a messagebox, but it isn't working in a macro or a module when I program it in. It gives me the error that it can't locate the file. Any additional comments or suggestions?
You need to set the path as well. So if you're coding it correctly it should be something like this (granted tihs doesn't have your flie finding code).
Sub testing()
Dim Filename As String
Filename = "C:\File" & Date$ & ".txt"
'MsgBox ("C:\File" & Date$ & ".txt"
MsgBox Flenme
End Sub
Now 'FileName' is set to the txt file name... Have it search for 'FileName' instead of "C:\..." & ... you know.
-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------
Sub testing()
Dim Filename As String
Filename = "C:\File" & Date$ & ".txt"
'MsgBox ("C:\File" & Date$ & ".txt"
MsgBox Flenme
End Sub
Now 'FileName' is set to the txt file name... Have it search for 'FileName' instead of "C:\..." & ... you know.
-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------
- Thread starter
- #6
bigdars,
No problem, I'm glad that I could help.
-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------
No problem, I'm glad that I could help.
-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------
- Status
Similar threads
- Question
- Locked
- Question
- Locked
- Question
