PDA

View Full Version : Rename before moving a file



daniels012
05-05-2011, 08:02 AM
I have a file called "Proposal for XL" and I hit a button that moves a file to a certain directory. Works great. I want to be able to rename it right before moving it. Is this possible? If so, the name of the file I would like it to be is in the worksheet in cell "U17"

Thank You,
Michael


Here is the vba I have for the part that moves the file:

MyVal2 = Range("S10").Value
strPath4 = "C:\Documents and Settings\Owner\My Documents\Dropbox\ZEstim\"

ActiveWorkbook.Sheets("FRONT").Select
Estfile = Range("U10").Value
sfol = strPath ' dropbox directory

dfol = strPath4 & MyVal2 & "\" ' change to match the destination folder path for Estimating sheet
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(sfol & Estfile) Then
MsgBox sfol & Estfile & " does not exist!", vbExclamation, "Source File Missing"
ElseIf Not fso.FileExists(dfol & Estfile) Then
fso.MoveFile (sfol & Estfile), dfol
Else
MsgBox dfol & Estfile & " already exists!", vbExclamation, "Destination File Exists"
End If

Bob Phillips
05-05-2011, 08:19 AM
Untested



MyVal2 = Range("S10").Value
strPath4 = "C:\Documents and Settings\Owner\My Documents\Dropbox\ZEstim\"

ActiveWorkbook.Sheets("FRONT").Select
estfile = Range("U10").Value
sfol = strPath

dfol = strPath4 & MyVal2 & "\" ' change to match the destination folder path for Estimating sheet
If Dir(sfol & estfile, vbNormal) = "" Then
MsgBox sfol & estfile & " does not exist!", vbExclamation, "Source File Missing"
ElseIf Dir(dfol & estfile, vbNormal) <> "" Then
Name sfol & estfile As dfol & estfile
Else
MsgBox dfol & estfile & " already exists!", vbExclamation, "Destination File Exists"
End If

daniels012
05-05-2011, 08:32 AM
Before I try this I noticed you changed some of the stuff I had like the fso and added dir , will that matter, because the other worked fine?

Also, it will find the existing file name as "U10"
Estfile = Range("U10").Value
But I want it to be renamed what is in "U17". Should I add something like
EstfileNew = Range("U17").Value

Thank you very much for the help so far,
Michael

Bob Phillips
05-05-2011, 11:38 AM
Yes and yes.