Save file to same directory folder

[RobN]

Hi,

I need to rename a file and save that file to the same folder where the
original is saved (via VBA code).

I find that if I've also opened another file from another folder, this
particular file I wish to save with a new name is saved to the wrong folder.
I don't want to set a folder as it could change from computer to computer.
I just want to make sure that wherever the original file is stored, the code
saves to the newly named file to that same folder.

I notice that when I manually select SaveAS, it opens to the correct folder,
but running the code below, the folder it saves to is unfortunately the one
last opened or saved to.

Sub SaveRollover()
'This saves the rolled over file with a new file name.

Dim WB As Workbook
Dim newFileName As String
Dim rng As Range
Set rng = Sheets("Setup").Range("P2")

On Error GoTo Oops

newFileName = rng.Text
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
rng.Text _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=True
Etc....

Rob

 

[Jim Cone]

Provide the file path and you will know where it is...

Filename:=ThisWorkbook.Path & Application.PathSeparator & rng.Text
--
Jim Cone
Portland, Oregon USA
easy ways to sort... http://www.contextures.com/excel-sort-addin.html



"RobN" <[email protected]>
wrote in message
Hi,
I need to rename a file and save that file to the same folder where the
original is saved (via VBA code).

I find that if I've also opened another file from another folder, this
particular file I wish to save with a new name is saved to the wrong folder.
I don't want to set a folder as it could change from computer to computer.
I just want to make sure that wherever the original file is stored, the code
saves to the newly named file to that same folder.

I notice that when I manually select SaveAS, it opens to the correct folder,
but running the code below, the folder it saves to is unfortunately the one
last opened or saved to.

Sub SaveRollover()
'This saves the rolled over file with a new file name.
Dim WB As Workbook
Dim newFileName As String
Dim rng As Range
Set rng = Sheets("Setup").Range("P2")

On Error GoTo Oops
newFileName = rng.Text
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
rng.Text _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=True
Etc....
Rob

 

[RobN]

Thanks Jim. Worked first time. A logical and easy solution!

Rob