VBA Save File To New Folder

[Gian624]

Hi Everyone,

I'm working on a project where I need to open a spreadsheet with a macro, run a macro in the spreadsheet and save the spreadsheet to a new folder with a slightly modified name. My string of code in VBA reads:

ActiveWorkbook.SaveAs Filename:=Location & Left(myFile, Len(myFile) - 15) & newDate & ".xlsm"

Location is defined as the folder path I need to save to which is fine
Left... just takes the last 15 characters off the file name and changes it to the newDate

When I run the macro, everything works find but it saves the spreadsheet in the current folder and adds the path where I want to save it to the file name at the front. I need to modify the code so it saves it to the Location and saves the file name as the Left...

Thanks for any help,
Matt

 

[RCBricker]

Hi Everyone,

I'm working on a project where I need to open a spreadsheet with a macro, run a macro in the spreadsheet and save the spreadsheet to a new folder with a slightly modified name. My string of code in VBA reads:

ActiveWorkbook.SaveAs Filename:=Location & Left(myFile, Len(myFile) - 15) & newDate & ".xlsm"

Location is defined as the folder path I need to save to which is fine
Left... just takes the last 15 characters off the file name and changes it to the newDate

When I run the macro, everything works find but it saves the spreadsheet in the current folder and adds the path where I want to save it to the file name at the front. I need to modify the code so it saves it to the Location and saves the file name as the Left...

Thanks for any help,
Matt

you need to create the new folder.

MKdir location
ActiveWorkbook.SaveAs Filename:=Location & Left(myFile, Len(myFile) - 15) & newDate & ".xlsm"

that should make the folder you have defined in your location variable and then save the file there.

thanks,

 

[Gian624]

Hi RCBricker,

I had previously created the new folder I wanted everything to copy to. I used cell B10 as the reference cell for "Location" where I pasted the folder extension to. I tried adding Mkdir like you showed but that did not do anything. What was that supposed to do?

Thanks,
Matt

 

[RCBricker]

nothing if you have not defined a variable named location.

So you have the full path of the where you want the file to go in cell B10?

ActiveWorkbook.SaveAs Filename:=range("B10").value & Left(myFile, Len(myFile) - 15) & newDate & ".xlsm"

if you have a path but it does not end in character "\" in cell b10 then use this

ActiveWorkbook.SaveAs Filename:=range("B10").value & "\" & Left(myFile, Len(myFile) - 15) & newDate & ".xlsm"

if cell B10 is a named range for the sheet and named "Location" but the cell does not end in "\" then try this

ActiveWorkbook.SaveAs Filename:=location & "\" & Left(myFile, Len(myFile) - 15) & newDate & ".xlsm"

Just a note going forward. Please remember that none of us can see your spreadsheet. you stated location was defined, but no specific which led me to believe that it was a variable. use code tags and pasting your existing code in its entirety is a great help also.

thanks,

 

[Gian624]

Hi RCBricker,

That was the exact problem I was having... stupid little "\" missing. It works like a charm now.

Thanks again for all your help,
Matt

 

[RCBricker]

no problem

glad I could help

 

[druid9855]

I'm having a related issue with a Command button to save a file. The entire filename (including Path and Extension) is defined in a cell named FNAME. I get the error shown:

Private Sub CommandButton5_Click()
'
Dim FN As String
'
FN = Range("FNAME") [Error: Method 'Range' of object'_Worksheet' failed]
ActiveWorkbook.SaveAs FileName:=FN
End Sub

Can't seem to figure out what I'm doing wrong with the named range.