asked on

Use VBA from Access 2003 to create files/folders in SharePoiint 2007

We have a core application written in Access 2003/VBA using SQL 2005 as the backend.
As part of the app, we have facilities for uploading/editing/deleting files to MS file system.

We've been migrating all files to MOSS7 (SharePoint 2007 Portal), and now we're moving the files managed by the application.

We still need to manage them from the current Access application, so we need to modify our code that currently uses CreateObject("Scripting.FileSystemObject") functions and methods (e.g. CreateFolder, FileExists, BuildPath, CopyFile) to manage web hyperlinks pointing to the SharePoint server.

What is the best method to do this, and can anyone point me to VBA code that illustrates creating a folder, creating a file, opening a file, etc. in SharePoint from Access?

ASKER CERTIFIED SOLUTION

Ted Bouskill

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hometowncc

ASKER

The hyperlink does work with as follows:
...
Dim fs
Dim fsFolder
Set fs = CreateObject("Scripting.FileSystemObject")
...
strtoUrl = "http://www.test.com/test_folder/test.doc"
Application.FollowHyperlink strtoUrl, , True
...

but the other functions - which are critical - don't work:
...
if fs.folderexists(strtoFolder) then ' don't create folder... already exists
...
else 'folder does not exist - create it
set fsfolder = createfolder(strFolderToCreate)
endif
...
if fs.fileexists(strFile) then 'check if file should be overwritten or skipped
...
else
fs.copyfile(strFile, strDestinationFile)
...
endif

-------
The filesystemobject object does not seem to detect a file or folder based on a url pointing to sharepoint, however the Application.FollowHyperLink does.

Private Sub btnFetchFile_Click()
Dim fs
Dim doc As Object
Dim ToFolder As String
Dim toURL As String
Set fs = CreateObject("Scripting.FileSystemObject")
ToFolder = fs.buildpath(Me.txtServer, Me.txtfolder)  'this adds a '\'character if one is missing in the concantenation of the two strings
 
Me.txtURL = ToFolder & Me.txtfile
 
            If fs.folderexists(Nz(ToFolder, "")) Then
                MsgBox "Folder [" & ToFolder & "] EXISTS!"
            Else
                MsgBox "ERROR: can't fetch folder " & ToFolder
            
            End If
            toURL = Nz(ToFolder & Me.txtfile, "")
            If fs.FileExists(toURL) Then
                MsgBox "File [" & toURL & "] EXISTS!"
            Else
                MsgBox "ERROR: can't fetch File " & toURL
            
            End If
    On Error GoTo 0
    Application.FollowHyperlink toURL, , True
    On Error GoTo Err_Exit_Sub
Err_Exit_Sub:
 
Set fs = Nothing
 
End Sub
 
 
Private Sub Form_Load()
Me.txtServer = "http://test:41277/"
Me.txtfolder = "personal/basic_user/Personal%20Documents/"
Me.txtfile = "test.doc"
End Sub

Open in new window

Ted Bouskill

Ah, but that is my point, try to access the file like this: "\\www.test.com\test folder\test.doc"

Computer101

Forced accept.

Computer101
EE Admin