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

I have VB that customizes the file name in Excel when saving.

However when I add this code, it wants to save the file to my local machine = I lose the sharepoint path/url to the doc library.

How can I populate this dynamically? (I don't want to hard code the path)
 
Sub SaveByDate()
        Dim MyFileName As String
        MyFileName = "PO_" & Format(Now(), "yyyymmdd-hhnnss")
        ActiveWorkbook.SaveAs Filename:=MyFileName, FileFormat:=52
End Sub

Open in new window

sullisnyc44Asked:
Who is Participating?
 
aikimarkCommented:
This seems to work.  There is a difference in the file name pattern, so you will need two more "#" characters in the date part of the name since you are using a four digit year.
Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  If ActiveWorkbook.Name Like "PO_######-######.xlsm" Then
    Exit Sub
  End If
  Application.EnableEvents = False
  Cancel = True
  ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & "PO_" & Format(Now(), "yymmdd-hhnnss"), FileFormat:=52
  Application.EnableEvents = True
End Sub

Open in new window

0
 
aikimarkCommented:
You need to include the path
ActiveWorkbook.Path

Open in new window

0
 
sullisnyc44Author Commented:
that only returns the filename.

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

0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
aikimarkCommented:
What about

ActiveWorkbook.FullName

Open 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.
0
 
sullisnyc44Author Commented:
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?
0
 
aikimarkCommented:
Are you inspecting the Path and FulNme properties before you run your SaveByDate() routine?
0
 
sullisnyc44Author Commented:

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

Open in new window

0
 
sullisnyc44Author Commented:
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.
0
 
aikimarkCommented:
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.
0
 
sullisnyc44Author Commented:
path pulls up the local computer not the sharepoint document library directory.

Do you have SharePoint?
0
 
sullisnyc44Author Commented:
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?
0
 
sullisnyc44Author Commented:
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
0
 
aikimarkCommented:
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.
0
 
sullisnyc44Author Commented:
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...
0
 
sullisnyc44Author Commented:
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

Open in new window

0
 
aikimarkCommented:
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.
0
 
sullisnyc44Author Commented:
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!
0
 
sullisnyc44Author Commented:
Also I'd always like to save is as an xlsm
0
 
aikimarkCommented:
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

Open in new window

0
 
sullisnyc44Author Commented:
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.
0
 
sullisnyc44Author Commented:
but will that save my edits?
0
 
aikimarkCommented:
it should.  As with all expert suggestions, it is up to you to verify their correctness.  In other words...try it.
0
 
sullisnyc44Author Commented:
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

Open in new window

0
 
aikimarkCommented:
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.
0
 
sullisnyc44Author Commented:
filename uniqueness without create a bazillian duplicates (minutes/seconds)
0
 
aikimarkCommented:
you think there will be more than one save/second?!?
0
 
sullisnyc44Author Commented:
no but if they save it....and not close it... and then save it again... that will cause a problem, no?
0
 
aikimarkCommented:
once it has been saved with a date/time stamp name, then the file always retains that name.  Isn't that what you wanted?
0
 
sullisnyc44Author Commented:
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.
0
 
aikimarkCommented:
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.
0
 
sullisnyc44Author Commented:
Ah yes. against the pattern. very good. I will repost my final code. thanks again.
0
 
aikimarkCommented:
remember that I used a slightly different pattern than you did (only a two digit year instead of your four digit year)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.