File Management using VBA

  • Thread starter Thread starter C Brandt
  • Start date Start date
C

C Brandt

Hi Guys:

I need to change the name of a downloaded file
Example: An outside system creates a file "account.txt", on my system, every
morning and cannot tolerate having an existing file with that name. I would
like to change the name to "acct062507.txt" for the data gathered on the
25th of June.
This probably needs to be a standalone task, scheduled to run every morning
prior to the download (move yesterdays file to make room for the new
download).
Can someone point me to online help that gives examples of this type of
work?

Very much appreciated,
Craig
 
Sub rename()
s = " rename c:\account.txt acct" & Format(Now(), "mmddyy") & ".txt"
x = Shell("cmd.exe /c" & s, 1)
End Sub

this is just the same as in a .bat file
 
I believe you could also use the Name statement

Dim OldName As String, NewName As String
OldName = "c:\account.txt"
NewName = "c:\acct" & Format(Now(), "mmddyy") & ".txt"
Name OldName As NewName ' Rename file.

or

OldName = "c:\account.txt"
NewName = "C:\YOURDIR\"acct" & Format(Now(), "mmddyy") & ".txt"
Name OldName As NewName ' Move and rename file.

although if you want to use yesterday's date in the filename, you may want
to use Now()-1. or if you want to use the previous workday (see XL help for
workday function), perhaps use
"acct" & Format(evaluate("workday(now(),-1)"), "mmddyy") & ".txt"
but holidays could potentially cause problems.
 
JMB:

Thanks for your response. It was right on target.

It is amazing at how simple the solution is once you know how. Knowing the
answer, I went to do more research on "NAME" and it was no help. I never
would have figured it out without your help.

Do you know of any online help source that takes about how to handle file
management, like what to do when you have file name conflicts, etc? (Clear
examples always help.)

Thanks again,

Craig
 
I've never come across help sources/tutorials on file management, but I've
never looked very hard either.

To test to see if a file name already exists, you could use the Dir function

If Dir("C:\myfile.xls") = "" Then
'File does not exist
Else
'File already exists
End If

but you have to decide what to do if the file already exists (overwrite,
cancel, try to programmatically develop another file name, use an inputbox to
get a file name from the user, etc).
 
Back
Top