Excel Document Library:When I customize the filename, I lose the path to the server

You need to include the path

ActiveWorkbook.Path

Select allOpen in new window

that only returns the filename.

how do I return the complete sharepoint path/url WITHOUT harcoding it?

What about

ActiveWorkbook.FullName

Select allOpen in new window

That should give both path and file name.  Since you're constructing your own new file name, you'll have to parse the path out of the Fullname property.

nope just the filename.

There has to be some other property... built in document properties or content type properties that I must be able to get to... something that will pull even information about the template used in the document library?

Do you know?

Are you inspecting the Path and FulNme properties before you run your SaveByDate() routine?

I moved my code up - it was creating dupes on it's own.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI Then
       Sheets("Purchase Order").Range("Description,VendorInfo,QUANTITY1,PRODUCT1,ITEM1,PRICE1,submitter,ShipVia,Terms,MACRO_ALERT").Interior.ColorIndex = xlColorIndexNone
       Sheets("Purchase Order").Range("Location").Interior.Color = RGB(184, 204, 228)
       Sheets("Purchase Order").Range("MACRO_ALERT").Value = ""
       
      Dim MyFileName As String
    Dim MyFilePath As String
 '     MyFilePath = "http://server/dept/purchasetracking/"
 MyFilePath = Application.Path
 
      MyFileName = "PO_" & Format(Now(), "yyyymmdd-hhnnss")
      MsgBox (MyFilePath & MyFileName)
 ActiveWorkbook.SaveAs Filename:=MyFilePath & MyFileName ', FileFormat:=52
    '    ActiveWorkbook.SaveAs Filename:=MyFileName
    End If
End Sub

Select allOpen in new window

PS the msbox that pops up displays my local drive.

if I leave everything alone and just do a normal save/save as - it points to the correct location - the SharePoint Document library.

I think the duplicates are the result of your code in the BeforeSave() event doing a save and not cancelling the impending save.  I'm not sure of the context of your problem to comment on your choice/use of the BeforeSave() event.

What is the status of this problem?  From your last comment, it seems like the .Path property comment was the correct answer.

path pulls up the local computer not the sharepoint document library directory.

Do you have SharePoint?

ok I've decided to hard code the path. (but it's making me bezerker that I can't pull this from somewhere)

I'm still getting duplicate documents though, where should this code go?

What's interesting is that this sort of works - but I'm creating the doc twice. When I get the Save As dialog - the document already exists:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI Then
       Sheets("Purchase Order").Range("Description,VendorInfo,QUANTITY1,PRODUCT1,ITEM1,PRICE1,submitter,ShipVia,Terms,MACRO_ALERT").Interior.ColorIndex = xlColorIndexNone
       Sheets("Purchase Order").Range("Location").Interior.Color = RGB(184, 204, 228)
       Sheets("Purchase Order").Range("MACRO_ALERT").Value = ""
      SaveByDate
    End If
End Sub

Sub SaveByDate()
 Dim MyFileName As String
       Dim MyFilePath As String
       MyFilePath = "http://server/dept/purchasetracking/"
       MyFileName = "PO_" & Format(Now(), "yyyymmdd-hhnnss")
       ActiveWorkbook.SaveAs Filename:=MyFilePath & MyFileName, FileFormat:=52
End Sub

Do you understand the event and the Cancel parameter?

No.  I do not have Sharepoint.

Your posted code (not the path literal string comment) was using Application.Path instead of Activeworkbook.Path.  I can understand where they would be different.

Oh thanks!! I didn't even catch that! I will see what happens if I change that. (I've edited and moved this code so many times...ugh)

So you are saying that I should enable/disable events before and after my code and set cancel to True?

I will test and repost code...

unfortunately this messagebox comes up blank. I will have to hard code.

MyFilePath = ActiveWorkbook.Path
       MsgBox (MyFilePath)

I did make these changes. Do you see anything else I should do?

 

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.EnableEvents = False
    'Disable events, we don't want BeforeSave to fire here
    If SaveAsUI = True Then
       Cancel = True
       Sheets("Purchase Order").Range("Description,VendorInfo,QUANTITY1,PRODUCT1,ITEM1,PRICE1,submitter,ShipVia,Terms,MACRO_ALERT").Interior.ColorIndex = xlColorIndexNone
       Sheets("Purchase Order").Range("Location").Interior.Color = RGB(184, 204, 228)
       Sheets("Purchase Order").Range("MACRO_ALERT").Value = ""
      SaveByDate
    End If
    Application.EnableEvents = False
End Sub

Sub SaveByDate()
 Dim MyFileName As String
       Dim MyFilePath As String
       MyFilePath = "http://server/dept/purchasetracking/"
     '  MyFilePath = ActiveWorkbook.Path
     '  MsgBox (MyFilePath)
       MyFileName = "PO_" & Format(Now(), "yyyymmdd-hhnnss")
       ActiveWorkbook.SaveAs Filename:=MyFilePath & MyFileName, FileFormat:=52
End Sub

Select allOpen in new window

The BeforeSave() event happens before a save.  It allows you to inspect and tweak properties and cells.  It also allows you to cancel the save operation by setting the Cancel parameter to True.

Since you are performing a save operation before the scheduled save operation and not cancelling the scheduled saved operation, that is likely the cause of your duplicate documents.

Rather than doing the save in the BeforeSave() event, you might just change the name of the workbook and let the save operation do the actual saving.

so remove that code and put my new file name where?

Also - what would you suggest for a unique file name system? Every time I hit save I realized that it saves a new doc with it's super unique name.

This is becoming more trouble than I anticipated - all I wanted was a unique file name when uploading to a document library!

Also I'd always like to save is as an xlsm

I ran the following code on my system (no Sharepoint destination, sorry).  It seems to work as expected.  Every time I click the Save toolbar icon, it creates this .XLSM workbook with a new timestamp name.

Note: I used the .Path property instead of a hard-coded literal.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Application.EnableEvents = False
  Cancel = True

  ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & "PO_" & Format(Now(), "yymmdd-hhnnss"), FileFormat:=52

  Application.EnableEvents = True
End Sub

Select allOpen in new window

even if i'm editing an exisiting doc and click save, this will run?

I only want to set the filename the first time it's saved.

but will that save my edits?

it should.  As with all expert suggestions, it is up to you to verify their correctness.  In other words...try it.

Thanks.

what if I wanted the name to be more like this:
MyFileName = "PO_" & Format(Now(), "yyyymmdd") & "-" & UserInitials (xlsm)

How would I change this line:
If ActiveWorkbook.Name Like "PO_########-??.xlsm" Then...

where UserInitials =

Public Function UserInitials() As String
    
    Dim vaNames As Variant
    Dim sInit As String
    Dim lMax As Long
    Dim i As Long
    
    vaNames = Split(UCase(Application.UserName), " ")
    
    lMax = Application.WorksheetFunction.Min(2, UBound(vaNames))
    
    For i = 0 To lMax
        sInit = sInit & Left$(vaNames(i), 1)
    Next i
    
    UserInitials = sInit
    
End Function

Select allOpen in new window

that should work.  However, I don't understand why you want to include the initials, since Sharepoint should retain the user information when the file is saved.

filename uniqueness without create a bazillian duplicates (minutes/seconds)

you think there will be more than one save/second?!?

no but if they save it....and not close it... and then save it again... that will cause a problem, no?

once it has been saved with a date/time stamp name, then the file always retains that name.  Isn't that what you wanted?

Yes. absolutely! You helped me with exactly what I needed.

But the logic works with Now() and comparing to that most current time and the file name...  which, of course is always changing. I removed the time for this reason. It's possible that a user will crete edit a spreadsheet, save it and then come back, edit it, save it again.

I'm just trying to reduce the possibility for duplicates.

thanks so much for all of your help.

I think you should use Now().

The If...End If state I posted (in the accepted comment) checks the filename against a date-and-time pattern.

Ah yes. against the pattern. very good. I will repost my final code. thanks again.

remember that I used a slightly different pattern than you did (only a two digit year instead of your four digit year)