How do I pull the URL/Path of the sharepoint document library I'm in from Excel VB?

Question

I would like to set the filename for an item I create in an Excel Document Library. Howeverm when I try to interfere with the standard save with my own filename, it wants to save to my LOCAL MACHINE. I would happily like to supply the PATH if that is necessary, but I really DONT WANT TO HARD CODE IT.

Are there any properties I can use to parse this info? Meta data? Template?

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

MikeD MikeD · Accepted Answer · 2011-05-02T20:19:52

I like to use function GetSetting() and statement SaveSetting to save such informations to the registry (last used path, etc.).

When the file is opened from a SHP folder, ActiveWorkbook.Path starts with "http://". In this case you could save this path from a Sub Workbook_Open() procedure into the registry to retain the SHP path information. This can be later on used to offer a good path/filename to the user.

Hope that helps .... Good Luck MikeD

How do I pull the URL/Path of the sharepoint document library I'm in from Excel VB?

1 Answers