Save As Button

[Apeman]

I am trying to write some VBA so create a save as button that saves the document into the correct location and creates the correct file name... I am a bit of a VBA noob, I can do basics but this has me stumped! Please see the code below that I have tried to butcher from various different sources. Apologies in advance for the crap code, I know this will be easy for some of you guys to fix:

---

Private Sub CommandButton2_Click()


Dim fsName As String
Dim fName As Variant
Dim path As String


fsName = Range("A2").Value
fName = ("ABC123" & fsName)
path = ("bwg-apps01\cash4w\cabinet\Sales Enquiry\000" & fsName & "\001 Cost Sheet\")


ActiveWorkbook.SaveAs Filename:=(path & fName), FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False


 
End Sub

 

[Trevor G]

Does A2 also include the file extension .xlsm? as you maybe missing the extension. Or try adding this

ActiveWorkbook.SaveAs Filename:=(path & fName) & ".xlsm"​

Not tested.

 

[Apeman]

Does A2 also include the file extension .xlsm? as you maybe missing the extension. Or try adding this



Not tested.

Still not working... Cell A2 just contains a 5-digit number (job ref. number).

The error message is:

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

When I Debug it heiglights the following line:

ActiveWorkbook.SaveAs Filename:=(path & fName & ".xlsm"), FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

 

[Logit]

[COLOR=#333333]path = ("bwg-apps01\cash4w\cabinet\Sales Enquiry\000" & fsName & "\001 Cost Sheet\")[/COLOR]

You haven't specified a Drive ....

 

[Apeman]

[COLOR=#333333]path = ("bwg-apps01\cash4w\cabinet\Sales Enquiry\000" & fsName & "\001 Cost Sheet\")[/COLOR]

You haven't specified a Drive ....

Well Spotted... It is a UNC path and I missed the '\\' from the beging. However, it still doesn't work, I still get the same message, my code now looks like this:

Private Sub CommandButton2_Click()




Dim fsName As String
Dim fName As Variant
Dim path As String




fsName = Range("A2").Value
fName = ("CostSheet 000" & fsName)
path = ("\\bwg-apps01\cash4w\cabinet\Sales Enquiry\000" & fsName & "\001 Cost Sheet\")




ActiveWorkbook.SaveAs Filename:=(path & fName & ".xlsm"), FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False




 
End Sub

I also tried using the local drive letter as below and still no joy:

Private Sub CommandButton2_Click()




Dim fsName As String
Dim fName As Variant
Dim path As String




fsName = Range("A2").Value
fName = ("CostSheet 000" & fsName)
path = ("G:\cabinet\Sales Enquiry\000" & fsName & "\001 Cost Sheet\")




ActiveWorkbook.SaveAs Filename:=(path & fName & ".xlsm"), FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False




 
End Sub

 

[Trevor G]

I have created the following code and it works:

Sub Macro1()
fsName = ActiveSheet.Name
sName = Sheets("CostSheet 000").Range("A2")
path = "C:\Users\xxxx\Documents"
ActiveWorkbook.SaveAs Filename:=path & fsName & sName & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub​

​

 

[Apeman]

I have created the following code and it works:


​

Thanks Trevor G. I'll give it a try later.