VBA code to save xls file to

[wootenka]

I am new to VBA and have created the following code to create a file name. How do I add code to save to the following SP library?

Code to save:
Option Explicit
Sub SvMe()
'Saves filename as value of A1 plus the current date

Dim newFile As String, fName As String
' Don't use "/" in date, invalid syntax
fName = Range("g18").Value
'Change the date format to whatever you'd like, but make sure it's in quotes
newFile = fName & " " & Format$(Date, "mm-dd-yyyy")

End Sub

Desired Location:

http://yard/operations/Shared Documents/Sales Estimates/Fracturing

Please Help!!
<!-- / message -->

 

[VoG]

Try

Sub SvMe()
'Saves filename as value of A1 plus the current date

Dim newFile As String, fName As String
' Don't use "/" in date, invalid syntax
fName = Range("g18").Value
'Change the date format to whatever you'd like, but make sure it's in quotes
newFile = fName & " " & Format$(Date, "mm-dd-yyyy")
ActiveWorkbook.saveas Filename:="http://yard/operations/Shared Documents/Sales Estimates/Fracturing\" & newFile
End Sub

 

[wootenka]

That worked. IF you don't mind, I have two other questions.

1. How would I add to save it as an XLS format
2. How would I insert this to have it run after the following (Which I have a button for):

Private Sub CommandButton1_Click()
End Sub
Private Sub Update_Click()
For Each Prop In ThisWorkbook.ContentTypeProperties
If Prop.Name = "Quote Amount" Then
Prop.Value = Cells(6, 7).Value
End If

If Prop.Name = "Customer" Then
Prop.Value = Cells(3, 3).Value
End If
If Prop.Name = "Gross Profit" Then
Prop.Value = Cells(13, 7).Value
End If
If Prop.Name = "Well" Then
Prop.Value = Cells(8, 3).Value
End If
Next Prop
End Sub

 

[VoG]

1) try

Sub SvMe()
'Saves filename as value of A1 plus the current date

Dim newFile As String, fName As String
' Don't use "/" in date, invalid syntax
fName = Range("g18").Value
'Change the date format to whatever you'd like, but make sure it's in quotes
newFile = fName & " " & Format$(Date, "mm-dd-yyyy")
ActiveWorkbook.SaveAs Filename:="http://yard/operations/Shared Documents/Sales Estimates/Fracturing\" & newFile & ".xls", FileFormat:=56
End Sub

2) Just use

Call SvMe

 

[wootenka]

Thank you a ton! We are working. I now have to figure out why my Sharepoint document properties are not getting pushed through to the server. The code is updating the property on the file itself, just not in the library.

 

[VoG]

Please start a new thread for that. I know nothing about Sharepoint.

 

[wootenka]

Will do. Now that this is working, In this same work book I am trying to add a copy of two worksheets, a sales estimate and the coresponding cost analysis. The type of work we do requires us to bid the enitre job in different stages. So we want one workbook for the job that will include the revenue and cost analysis for each stage and then be summarized on a dashboard type section of the workbook.


I figured out how to automate the adding of the two worksheets and renaming them, but we have a summary page that totals all of the revenues, costs and gross profit. My problem is getting this calculation to include the new estimates.

Any thoughts?