We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

File Save As saving to last file location, not SharePoint doc library, custom Save As button, Enble Macros

Medium Priority
424 Views
Last Modified: 2012-05-11
1. I have a SharePoint 2007 doc library with a custom content type - an xlsm.

I have  this code that runs before Save:
 
Sub SaveByDate()
Dim MyYear As String
Dim MyMonth As String
Dim MyDay As String
Dim MyHour As String
Dim MyMin As String
Dim MySec As String
Dim MyFileName As String

        MyYear = Year(Date)
        MyMonth = Right("0" & Month(Date), 2)
        MyDay = Right("0" & Day(Date), 2)
        MyHour = Right("0" & Hour(Time), 2)
        MyMin = Right("0" & Minute(Time), 2)
        MySec = Right("0" & Second(Time), 2)
        MyFileName = "PO_" & MyYear & MyMonth & MyDay & "-" & MyHour & MyMin & MySec
        ActiveWorkbook.SaveAs Filename:=MyFileName
End Sub

Open in new window


How do I insert the path of the SharePoint Library? This code saves to the last know save as file location.

2. Also, I want a custom Save and Close button that will save the spreadsheet as a regular xlsx and then close excel. Either a button on the sparesheet or the Ribbon (only for this workbook)

3. I want to enable macros for this workbook when it is opened. Can I do it with a prompt? right now I have a highlighted cell that says ' hey enable macros!!' . Not so fabulous solution. :)))
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Re 3, you can't pop up a prompt without code, and you can't run code unless macros are enabled, so no, I'm afraid not.
I know nothing about Sharepoint so can't comment about that.

Author

Commented:
Do you know how I can make this a Trusted Location?

Author

Commented:
how about a custom button for saving? Save as a regular excel sheet...
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
FWIW, a quick search indicates you simply add the server URL when saving:
ActiveWorkbook.SaveAs Filename:="http://server_url_here/" & MyFileName

Open in new window

Author

Commented:
like your file name shortened! thanks

I did not want to hard code the URL if I did not have to...

I will repost in sharepoint minus excel and see what happens. thanks

Author

Commented:
partially helped. did not answer sharepoint issues
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Bit of a punt, but can you use:
ActiveWorkbook.SaveAs Filename:=activeworkbook.path & "\" & MyFileName

Open in new window

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.