VBA to save file as cell name in XLSM, in current folder location

[Papinu]

Hello, I'm hoping somebody can help with VBA for me to save a file using cell name in current file format which is XLSM.

Many thanks

 

[dUBBINS]

dim wbname as String
wbname=range("a1").value
ActiveWorkbook.SaveAs Filename:="C:\" & wbNAME & ".xlsm"

Change A1 to whatever Cell you are wanting to reference.
Add your current path to where it says "C:\___" inside of quotations.

 

[Papinu]

Thank you so much @dUBBINS. Is there a way I can have it save by default into the path from where the file was opened without having to specify a location?
i.e. the save dialog box would open in the folder where the file was opened.

 

[Papinu]

Also, when I use the macro twice in a row, it says file exists and do I want to replace (yes, this is correct), so a YES works fine and it replaces the file. If I say no, a dialog box comes up and asks to debug or end. Clicking end works fine, but can I have it so no dialog box comes up when I click no?

 

[dUBBINS]

Papinu...I will figure these two things out for you; no problem.

 

[dUBBINS]

Thank you so much @dUBBINS. Is there a way I can have it save by default into the path from where the file was opened without having to specify a location?
i.e. the save dialog box would open in the folder where the file was opened.

Okay, it looks like this should work.

At the top of your code:

Dim getP as string

Somewhere at top, but only after workbook is open:

getP = Application.ActiveWorkbook.Path

Then change the line I gave you before to:

ActiveWorkbook.SaveAs Filename:= getP & wbNAME & ".xlsm"

Untested, and I have never tried this, but I tested parts of it, and I think it should work. Let me know. And now I will figure out your other question.

 

[dUBBINS]

Also, when I use the macro twice in a row, it says file exists and do I want to replace (yes, this is correct), so a YES works fine and it replaces the file. If I say no, a dialog box comes up and asks to debug or end. Clicking end works fine, but can I have it so no dialog box comes up when I click no?

I have to run, but off the top of my head, try to put this before the Save As line of code:

On Error Resume Next

I will check back to this thread tomorrow in the a.m. AK Time (-9)

 

[Papinu]

Thanks dUBBINS.

Using your advice, I've ended up with this code :-

Sub save_progress()
Dim getP As String
Dim wbname As String
getP = Application.ActiveWorkbook.Path
wbname = Range("a1").Value
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=getP & wbname & ".xlsm"
End Sub


When I run it, it saves to a folder that is not the current path and creates filename --> "folder name & field.xlsm"
No save dialog box appears.

Thanks for your ongoing help Will wait for your response.

 

[dUBBINS]

Sorry, my computer was down all day yesterday. That is very weird. Okay, let me build this on my end so I can test it as we go. Might be a few hours before I can get back to you.

 

[dUBBINS]

Thanks dUBBINS.

Using your advice, I've ended up with this code :-

Sub save_progress()
Dim getP As String
Dim wbname As String
getP = Application.ActiveWorkbook.Path
wbname = Range("a1").Value
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=getP & wbname & ".xlsm"
End Sub


When I run it, it saves to a folder that is not the current path and creates filename --> "folder name & field.xlsm"
No save dialog box appears.

Thanks for your ongoing help Will wait for your response.

This seems to do the trick:

 Sub save_progress()
 
 Dim wbname As String
 Dim pathONLY, filePATH, fileONLY As String
 filePATH = ThisWorkbook.FullName
 fileONLY = ThisWorkbook.Name
 pathONLY = Left(filePATH, Len(filePATH) - Len(fileONLY))
 wbname = Range("a1").Value
 On Error Resume Next
 ActiveWorkbook.SaveAs Filename:=pathONLY & wbname & ".xlsm"
 
 End Sub