VBA - change directory folder with a cells value name

user_14416554 0
Viewed 192

I made a worksheet with some buttons to save, save as and print quickly, but when a I Save As is saving on the Google Drive folder because is where the current file is, but i want to save on desktop and I can't find a way to make this change directory without break the name of the file. I make the file auto rename to a value of two cells (B10 and B15) when Save As, but when i make the directory change with some ways I found on the internet, doesn't work. I cant make the name and the directory right without a error.

My current code:

Sub SaveAs()
On Error GoTo ErrorSaveAs

Dim ClientName

ClientName = Range("B10").Value & " - " & Range("B15").Value

ActiveWorkbook.SaveAs FileName:="ClientName", FileFormat:=xlOpenXMLWorkbookMacroEnabled

MsgBox "O arquivo foi salvo no seu Desktop.", vbVertical, "Sucesso! - Garage Audax" 'Success message after saving it
    
Exit Sub

ErrorSaveAs: MsgBox "Ocorreu um erro. Revise as informações e tente novamente.", vbVertical, "Ops! - Garage Audax" 'Error message in case of any error

End Sub
vba excel
2 Answers

I dont think your script was saving because you initially did not specify a file path in the .saveas method.

if you see on the script below. I added fpath variable (with wb.path), so the script stores the filepath of where the activeworkbook is currently saved. (if you did not save the excel file prior executing the script, the .Path property will return blank)

when you use .saveas method, now the fpath variable will tell the computer where to save the file.

if you want to change the file name, just change the "Client Name". The script below will save the file named as "Client Name" as it was originally written on your script.

Let me know if the script works, otherwise lets try to solve it!

Sub SaveAs()


Application.Calculation = xlCalculationAutomatic 
Application.ScreenUpdating = False 
Application.DisplayAlerts = False

Dim wb As Workbook
Dim fPath As String


Set wb = ThisWorkbook

fPath = wb.Path & "\"
 
On Error GoTo ErrorSaveAs

ActiveWorkbook.SaveAs Filename:=fPath & "ClientName", FileFormat:=xlOpenXMLWorkbookMacroEnabled

MsgBox "O arquivo foi salvo no seu Desktop.", vbVertical, "Sucesso! - Garage Audax" 'Success message after saving it
    
Exit Sub

ErrorSaveAs: MsgBox "Ocorreu um erro. Revise as informações e tente novamente.", vbVertical, "Ops! - Garage Audax" 'Error message in case of any error


Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub
user_143302990

You can get the current user path folder and add the desktop and join the desktop and file name.

Dim desktopFolder as string desktopFolder = vba.environ("USERPROFILE") & "\Desktop\myFile.xlsm"

user_130943540
Related