Help renaming one excel file in an external directory. 4

[pdldavis]

Hi, I have an excel file deposited in a directory each week whose name is a date - 100203.xls, for example. I manually rename this file to C:\NewFile\Updated.xls

I was wondering if I could automate this with something like:

Dim Myfile
Myfile = ("c:\Update\'" & "*" & "' " &.xls&quot
Name Myfile as ("c:\NewFile\Updated.xls&quot

-which doesn't quite work.

Any suggestions would be appreciated.

Thanks, Dan

 

[VBslammer]

Use:

 MyFile = Dir("C:\Update\*.xls")

Then your variable will contain the first xls file in that directory. If there are mulitple files you can loop through them by calling DIR again.

Let me know if you want an example.


VBSlammer

Unemployed in Houston, Texas

 

[pdldavis]

vbslammer,

Thanks for the help. The Name command:

Name Myfile as ("c:\NewFile\Updated.xls&quot

must be the wrong command to use for this. When I mouseover Name, it shows the name of the form.

I tried docmd.rename, but it appears to apply to Access objects only or I've got the syntax wrong
What is the correct way to rename the file?

DoCmd.Rename ("C:\Newfile\Updated.xls&quot, ?, myfile

Thanks, Dan

 

[pdldavis]

I finally got something to work:

Dim myfile
Dim fso
myfile = Dir("C:\Newfile\*.xls&quot
Set fso = CreateObject("Scripting.FileSystemObject&quot
fso.MoveFile "C:\NewFile\" & myfile, "C:\NewFile\zxc.xls"

Thanks for the help

Dan

 

[VBslammer]

That's probably the best way to do it, since Name has many limitations, as noted in the documentation:

Name Statement
      

Renames a disk file, directory, or folder.

Syntax

Name oldpathname As newpathname

The Name statement syntax has these parts:

Part Description 
oldpathname Required. String expression that specifies the existing file name and location — may include directory or folder, and drive.  
newpathname Required. String expression that specifies the new file name and location — may include directory or folder, and drive. The file name specified by newpathname can't already exist. 



Remarks

The Name statement renames a file and moves it to a different directory or folder, if necessary. Name can move a file across drives, but it can only rename an existing directory or folder when both newpathname and oldpathname are located on the same drive. Name cannot create a new file, directory, or folder.

Using Name on an open file produces an error. You must close an open file before renaming it. Name arguments cannot include multiple-character (*) and single-character (?) wildcards.

VBSlammer

Unemployed in Houston, Texas

 

[pdldavis]

Thanks for the info on the name statement. I'll save that.
I appreciate the help.

Dan

 

[kb178]

This thread has been a big help to me - but now I'm feeling greedy...
When using the OutputTo function in VB in Access I'm allowed to export something as:
"C:\whatever " & Me.Date & ".xls"
(Me.Date is specified on the form I'm running the code from). I tried doing the same thing with the FileSystemObject, but it says "Path not found."
Is there a way to add the date to the name of the file you are copying to?
Thanks,
Kristen

 

[pdldavis]

Hi, Here's an output statement to snapshot that has the date & time. Perhaps you can modify this.

DoCmd.OutputTo acOutputReport, "rptGeneralWklistCustom", acFormatSNP, "S:\startup\snapshots\GeneralWorklist" & " - " & Format(Date, "mmmm dd&quot & "-" & Format(Time, "mmss&quot & ".snp"

 

[kb178]

Awesome! Thanks pdldavis! I guess it likes when you just use date, instead of calling something on the form!

 

[pdldavis]

I use the heck out of that particular statement. People love snapshots where I work. Glad it helped.