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


N

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
 
Ad

Advertisements

J

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
 
S

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??
 
S

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.
 
S

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
 
G

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!
 
Ad

Advertisements

J

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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top