Why cant I get the path to the sharepoint library????????

[nancy drew]

I have a custom excel content type and am attempting to create a unique file name

Sub SaveByDate()
Dim MyFileName As String
MyFileName = "PO_" & Format(Now(), "yyyymmdd-hhnnss")
ActiveWorkbook.SaveAs Filename:=MyFileName
End Sub

HOWEVER when I change the name it wants to save to MY DESKTOP and not the DOCUMENT LIBRARY - how can I grab the path? (DO I REALLY HAVE TO HARD CODE THIS???)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
SaveByDate
End If
End Sub

 

[john]

I have a custom excel content type and am attempting to create a unique file name

Sub SaveByDate()
Dim MyFileName As String
MyFileName = "PO_" & Format(Now(), "yyyymmdd-hhnnss")
ActiveWorkbook.SaveAs Filename:=MyFileName
End Sub

HOWEVER when I change the name it wants to save to MY DESKTOP and not the DOCUMENT LIBRARY - how can I grab the path? (DO I REALLY HAVE TO HARD CODE THIS???)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
SaveByDate
End If
End Sub

I use

fname = ActiveWorkbook.Name 'e.g. book1.xls
pname = ActiveWorkbook.FullName 'e.g. c:\files\book1.xls
fpath = Left(pname, Len(pname) - Len(fname))

'in this case, c:\files\

HTH
john

 

[Stefanie]

but my question is about pulling the URL dynamically, without
hardcoding, from the sharepoint document. how do I get that
information in an excel macro??

 

[Stefanie]

no it does not. It returns the path to my LOCAL MACHINE and not the
SHAREPOINT DOCUMENT LIBRARY.

Do you have Sharepoint? You can see what I mean if you do.

Create a doc library with Excel Content type. upload a template as a
content type and try to customize the file save as
functionality....let me know if you get the path of the document
library or not.

 

[Stefanie]

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

 

[GS]

Stefanie presented the following explanation :

no it does not. It returns the path to my LOCAL MACHINE and not the
SHAREPOINT DOCUMENT LIBRARY.

Do you have Sharepoint? You can see what I mean if you do.

Create a doc library with Excel Content type. upload a template as a
content type and try to customize the file save as
functionality....let me know if you get the path of the document
library or not.

I don't have Sharepoint. I also know nothing about it and so will do
some research.<g>

When opening files from my network it shows the UNC path & filename. I
thought this is what you were looking for. Sorry for the interuption!

 

[john]

Stefanie presented the following explanation :

I don't have Sharepoint. I also know nothing about it and so will do
some research.<g>

When opening files from my network it shows the UNC path & filename. I
thought this is what you were looking for. Sorry for the interuption!

Same here. Sorry for the bad info.