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

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 expressed precisely :
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??

'ActiveWorkbook.FullName' returns the complete path & filename.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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.

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,PR ODUCT1,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

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!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc