Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-04-26
32
Medium Priority
?
470 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:sullisnyc44
  • 18
  • 14
32 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 35476436
You need to include the path
ActiveWorkbook.Path

Open in new window

0
 

Author Comment

by:sullisnyc44
ID: 35477330
that only returns the filename.

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

0
 
LVL 46

Expert Comment

by:aikimark
ID: 35477589
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:sullisnyc44
ID: 35477624
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
 
LVL 46

Expert Comment

by:aikimark
ID: 35477707
Are you inspecting the Path and FulNme properties before you run your SaveByDate() routine?
0
 

Author Comment

by:sullisnyc44
ID: 35478039

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
 

Author Comment

by:sullisnyc44
ID: 35478049
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
 
LVL 46

Expert Comment

by:aikimark
ID: 35478952
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
 

Author Comment

by:sullisnyc44
ID: 35479025
path pulls up the local computer not the sharepoint document library directory.

Do you have SharePoint?
0
 

Author Comment

by:sullisnyc44
ID: 35479088
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
 

Author Comment

by:sullisnyc44
ID: 35479176
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
 
LVL 46

Expert Comment

by:aikimark
ID: 35479282
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
 

Author Comment

by:sullisnyc44
ID: 35483624
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
 

Author Comment

by:sullisnyc44
ID: 35483878
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
 
LVL 46

Expert Comment

by:aikimark
ID: 35484100
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
 

Author Comment

by:sullisnyc44
ID: 35484765
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
 

Author Comment

by:sullisnyc44
ID: 35484817
Also I'd always like to save is as an xlsm
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35485037
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
 

Author Comment

by:sullisnyc44
ID: 35485453
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
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 35485491
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
 

Author Comment

by:sullisnyc44
ID: 35486590
but will that save my edits?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35487584
it should.  As with all expert suggestions, it is up to you to verify their correctness.  In other words...try it.
0
 

Author Comment

by:sullisnyc44
ID: 35494092
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
 
LVL 46

Expert Comment

by:aikimark
ID: 35494196
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
 

Author Comment

by:sullisnyc44
ID: 35506914
filename uniqueness without create a bazillian duplicates (minutes/seconds)
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35506966
you think there will be more than one save/second?!?
0
 

Author Comment

by:sullisnyc44
ID: 35513311
no but if they save it....and not close it... and then save it again... that will cause a problem, no?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35513374
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
 

Author Comment

by:sullisnyc44
ID: 35513542
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
 
LVL 46

Expert Comment

by:aikimark
ID: 35513897
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
 

Author Comment

by:sullisnyc44
ID: 35690965
Ah yes. against the pattern. very good. I will repost my final code. thanks again.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35691028
remember that I used a slightly different pattern than you did (only a two digit year instead of your four digit year)
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A while back, I ran into a situation where I was trying to use the calculated columns feature in SharePoint 2013 to do some simple math using values in two lists. Between certain data types not being accessible, and also with trying to make a one to…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question