Save file to same directory folder


R

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
 
Ad

Advertisements

J

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top