Hello,

I have the following snippets of code that creates a new directory
every year (code that has been in production since many years):


Public Const PublishDir = "http://teamsites.company.tld/sites/
department/project/


If Not DirectoryExists(PublishDir & sYear) Then MkDir (PublishDir &
sYear)


Function DirectoryExists(ByVal PathName As String) As Boolean
'Macro Purpose: Function returns TRUE if the specified
' folder exists, false if not.

'declarations
Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")

'determine if it exists
DirectoryExists = objFSO.FolderExists(PathName)

'close everything up
Set objFSO = Nothing
End Function


This works great on a filesystem (local and network). But I cannot get
it to work on Sharepoint. I understand that Sharepoint directories are
not real, only exist in the database, and are "created" on the fly.
However other file operations on Sharepoint have no problem at all in
Excel (or Word) VBA:


..SaveAs FileName:=PublishDir & sFilename


Any thoughts?
Anyone with hands-on experience with VBA+SharePoint? We're using
Office 2003 and MOSS 2007, should that matter.

--
Amedee

When creating a folder don't include the last backslash in the folder name.

from
Public Const PublishDir = "http://teamsites.company.tld/sites/
department/project/

to
Public Const PublishDir = "http://teamsites.company.tld/sites/
department/project


"Amedee Van Gasse" wrote:

Hello,

I have the following snippets of code that creates a new directory
every year (code that has been in production since many years):


Public Const PublishDir = "http://teamsites.company.tld/sites/
department/project/


If Not DirectoryExists(PublishDir & sYear) Then MkDir (PublishDir &
sYear)


Function DirectoryExists(ByVal PathName As String) As Boolean
'Macro Purpose: Function returns TRUE if the specified
' folder exists, false if not.

'declarations
Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")

'determine if it exists
DirectoryExists = objFSO.FolderExists(PathName)

'close everything up
Set objFSO = Nothing
End Function


This works great on a filesystem (local and network). But I cannot get
it to work on Sharepoint. I understand that Sharepoint directories are
not real, only exist in the database, and are "created" on the fly.
However other file operations on Sharepoint have no problem at all in
Excel (or Word) VBA:


..SaveAs FileName:=PublishDir & sFilename


Any thoughts?
Anyone with hands-on experience with VBA+SharePoint? We're using
Office 2003 and MOSS 2007, should that matter.

--
Amedee

Try this instead


Public Const PublishDir = "http://teamsites.company.tld/sites/
department/project/


If Not DirectoryExists(PublishDir & sYear) Then CreateDirectory
(PublishDir,sYear)


Function DirectoryExists(ByVal PathName As String) As Boolean
'Macro Purpose: Function returns TRUE if the specified
' folder exists, false if not.

'declarations
Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")

'determine if it exists
DirectoryExists = objFSO.FolderExists(PathName)

'close everything up
Set objFSO = Nothing
End Function

Sub CreateDirectory(ByVal Path As String, ByVal Folder As String) As Boolean
'Macro Purpose: Function returns TRUE if the specified
' folder exists, false if not.

'declarations
Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
if right(Folder,1) = "\" then
Folder = left(Folder,len(Folder)-1)
end if

objFSO.Add(Folder, Path)

'close everything up
Set objFSO = Nothing
End Function

"Amedee Van Gasse" wrote:

Hello,

I have the following snippets of code that creates a new directory
every year (code that has been in production since many years):


Public Const PublishDir = "http://teamsites.company.tld/sites/
department/project/


If Not DirectoryExists(PublishDir & sYear) Then MkDir (PublishDir &
sYear)


Function DirectoryExists(ByVal PathName As String) As Boolean
'Macro Purpose: Function returns TRUE if the specified
' folder exists, false if not.

'declarations
Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")

'determine if it exists
DirectoryExists = objFSO.FolderExists(PathName)

'close everything up
Set objFSO = Nothing
End Function


This works great on a filesystem (local and network). But I cannot get
it to work on Sharepoint. I understand that Sharepoint directories are
not real, only exist in the database, and are "created" on the fly.
However other file operations on Sharepoint have no problem at all in
Excel (or Word) VBA:


..SaveAs FileName:=PublishDir & sFilename


Any thoughts?
Anyone with hands-on experience with VBA+SharePoint? We're using
Office 2003 and MOSS 2007, should that matter.

--
Amedee

On Sep 23, 4:42*pm, Joel wrote:
When creating a folder don't include the last backslash in the folder name.

from
Public Const PublishDir = "http://teamsites.company.tld/sites/
department/project/

to
Public Const PublishDir = "http://teamsites.company.tld/sites/
department/project

That could be just an artifact of me "sanitizing" the code for
publication on the web.

On Sep 23, 4:54*pm, Joel wrote:

* * 'determine if it exists
* * DirectoryExists = objFSO.FolderExists(PathName)

The method FolderExists works for SharePoint folders.

* * if right(Folder,1) = "\" then
* * * *Folder = left(Folder,len(Folder)-1)
* * end if

That should be:
If Right(Folder, 1) = "\" Or Right(Folder, 1) = "/" Then
because a Sharepoint path is an URL with forward slashes.

* * objFSO.Add(Folder, Path)

The methods Add and GetFolder give an error 76 (path not found) when
they are used on SharePoint folders.

But then I had a brilliant idea. I can also access the Sharepoint
directory via UNC notation too: \\teamsites.company.tld\sites
\department\project
This works. Thanks for tuning in.