Save file to same directory folder
in [General]
|
Prev: why do i get the pdf icon when copy and paste in excel
Next: How to not display clip board on the right side?
From: RobN on 2 Apr 2010 19:52 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
From: Jim Cone on 2 Apr 2010 22:47 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" <None(a)nowhere.com> wrote in message news:4E7B07A2-1C54-4555-B685-EE8E548E86D6(a)microsoft.com... 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
From: RobN on 4 Apr 2010 03:06
Thanks Jim. Worked first time. A logical and easy solution! Rob "Jim Cone" <james.coneXXX(a)comcast.netXXX> wrote in message news:O5Hk9ft0KHA.224(a)TK2MSFTNGP06.phx.gbl... > 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" <None(a)nowhere.com> > wrote in message > news:4E7B07A2-1C54-4555-B685-EE8E548E86D6(a)microsoft.com... > 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 > |