• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

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

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. :)))
0
sullisnyc44
Asked:
sullisnyc44
  • 4
  • 4
1 Solution
 
Rory ArchibaldCommented:
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.
0
 
sullisnyc44Author Commented:
Do you know how I can make this a Trusted Location?
0
 
sullisnyc44Author Commented:
how about a custom button for saving? Save as a regular excel sheet...
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Rory ArchibaldCommented:
Make what a trusted location? Your Sharepoint server? (If so, see my earlier response re my Sharepoint knowledge!)

FYI, you can replace this:
        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

Open in new window

with this:
MyFileName = "PO_" & Format(Now(), "yyyymmdd-hhnnss")

Open in new window

0
 
Rory ArchibaldCommented:
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

0
 
sullisnyc44Author 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
0
 
sullisnyc44Author Commented:
partially helped. did not answer sharepoint issues
0
 
Rory ArchibaldCommented:
Bit of a punt, but can you use:
ActiveWorkbook.SaveAs Filename:=activeworkbook.path & "\" & MyFileName

Open in new window

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now