Save as in specific path given in cell

[Dani_LobP]

Hello,

I'm having error when trying to save a file.

I have path to a shared drive location in C7
Then in cell C27 i have a date to be added to the file name
And in cell C28 name of file to be added also as file name..

Worksheets("Control Panel").Activate
Path = Range("C7")
FileDate = Format(Sheets("Control Panel").Range("C27"), "mmmmyyyy")
FileScenario = Range("C28")
Sheets("Control Panel").Visible = False
Activeworkbook.SaveAs FileName:=Path & "Draft_" & FileDate & "_" & FileScenario & ".xlsx"
Sheets("Control Panel").Visible = True

Keep getting error related to Save as object..
I have the impression that is because of the path being a shared drive and can't find it.
Is there any way to refresh such connection or something like that so it won't give an error or another way to make it work?

Weirdest thing is that sometimes works fine...

Thanks in advance!

 

[Joe4]

One big thing that stands out. Never used reserved words (like "Path") as the names of variables, functions, or procedures.
This can confusion on Excel's end (doesn't know if you are referring to the inherent "Path" property or the variable "Path"), which can lead to unexpected results and errors. I would change it to something like "MyPath".

Also, does the value in cell C7 in your path end with a slash ("")?
If not, you are probably not saving the file where you think.

For example, if you had "C:\Temp" in C7, the file would be saved to:
C:\TempFileName...
instead of
C:\Temp\FileName...
(the file would be saved one directory up, and your last directory would actually become part of your filename instead)

 

[Dani_LobP]

Thanks for the reply! tried changing the Path naming but still not working.

Path in cell C7 is V:\example\example2\example3\Draft\

Tried this also by placing the macro in the folder i want it to copy the files, that way i don't have to tell him the path...

'DraftPath = Range("C7")
DraftPath = ThisWorkbook.Path & "\"
ThisWorkbook.SaveAs FileName:=DraftPath & "DRAFT_" & FileDate & "_" & FileScenario & ".xlsx"

But still gives error.. i don't know if path being a shared drive location has something to do. And why sometimes works while i go up and down macro trying to test if works..

 

[Fluff]

What is the error number & message that you get?

 

[Joe4]

I recommend building the whole file path/name, and temporarily returning it to a message box to confirm that you creating a valid file name in a valid path.
Also, maybe try "ActiveWorkbook" instead of "ThisWorkbook".

DraftPath = ThisWorkbook.Path & "\"
PathAndFileName = DraftPath & "DRAFT_" & FileDate & "_" & FileScenario & ".xlsx"
MsgBox PathAndFileName
ActiveWorkbook.SaveAs FileName:=PathAndFileName

Lastly, you may need to include the file format you want to save it in. That is the second argument of the FileName command.
See: https://docs.microsoft.com/en-us/office/vba/api/excel.xlfileformat

 

[Dani_LobP]

Run-time error '1004':
Method 'SaveAs' of object '_Workbook' failed


This one..

 

[Dani_LobP]

thanks Joe4.

For some reason added the fileformat to the line of code, and... seems to be working.
will keep testing it, but i am not sure why, but works

ActiveWorkbook.SaveAs FileName:=DraftPath & "_DRAFT_" & FileDate & "_" & FileScenario, FileFormat:=51

 

[Joe4]

For some reason added the fileformat to the line of code, and... seems to be working.

That probably means that the file that you running this from is probably in a different format (i.e. a Macro-Enabled Excel format).
You can only leave that argument off if saving in the exact same format as the file is currently in (and if this is the file with your Macros, then it is probably a Macro-Enabled Excel format).

 

[Dani_LobP]

Indeed it is xlsm format. I actually thought if i tell to save with extension .xlsx it would save it with that format.. i guess i was wrong

Thanks again for the help!