Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save file to same directory folder
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save file to same directory folder
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" 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save file to same directory folder
Thanks Jim. Worked first time. A logical and easy solution!
Rob "Jim Cone" wrote in message ... 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
in which folder can you save a .CSV file? | Excel Discussion (Misc queries) | |||
file save error: the file or directory cannot be created | Excel Discussion (Misc queries) | |||
Automatically save a file in another directory | Excel Discussion (Misc queries) | |||
Need code to save file to new folder, erase from old folder | Excel Discussion (Misc queries) | |||
How do I save a read only file or folder? | Excel Discussion (Misc queries) |