Hi, I'm a little unsure of which direction to go with this. I'm not sure it's possible, I've looked all over but can't find a clear replacement for the code below to work with a SharePoint doc library. (I've been using it for years just fine with standard network folders)
What I'm trying to do is copy the filepath and filename of a file we hold in a (mounted) sharepoint site, and add the link to a knowledge-base table, so that users of the access database can click a link on a particular piece of equipment and open files related to that piece of equipment - manual, h&s, setup etc. It's done so that on a form they can click a button called 'GetFilePath', then another called 'PasteFilePath', so they can enter their own documents into the knowledgebase tables.
I've mounted the library to windows explorer so it has a drive letter: T:, path is \\teams.tees.ac.uk@ssl\sites\6\1, from where folder structure starts - so it's emulating network folders, but obv. it's not that simple. If I run the code (below) I just get error 'Runtime error 52: Bad file name or number'. Code which works just fine on windows explorer network files is:
Dim f As Object
Dim strFile As String
Dim strFolder As String
Dim varItem As Variant
Set f = Application.FileDialog(3)
f.AllowMultiSelect = False
If f.Show Then
For Each varItem In f.SelectedItems
strFile = Dir(varItem)
strFolder = Left(varItem, Len(varItem) - Len(strFile))
MsgBox "Folder: " & strFolder & vbCrLf & _
"File: " & strFile
Me.txtLink = strFolder & "\" & strFile
Next
End If
Set f = Nothing
(txtLink being the destination field which gets written to the equipment KB files table)
I could always get them to right click the address in the SharePoint web view and paste it into the field, but sometimes it opens the client app rather than the web app if it's office, and also it's not as slick as guiding them through a couple of buttons.
What I'm trying to do is copy the filepath and filename of a file we hold in a (mounted) sharepoint site, and add the link to a knowledge-base table, so that users of the access database can click a link on a particular piece of equipment and open files related to that piece of equipment - manual, h&s, setup etc. It's done so that on a form they can click a button called 'GetFilePath', then another called 'PasteFilePath', so they can enter their own documents into the knowledgebase tables.
I've mounted the library to windows explorer so it has a drive letter: T:, path is \\teams.tees.ac.uk@ssl\sites\6\1, from where folder structure starts - so it's emulating network folders, but obv. it's not that simple. If I run the code (below) I just get error 'Runtime error 52: Bad file name or number'. Code which works just fine on windows explorer network files is:
Dim f As Object
Dim strFile As String
Dim strFolder As String
Dim varItem As Variant
Set f = Application.FileDialog(3)
f.AllowMultiSelect = False
If f.Show Then
For Each varItem In f.SelectedItems
strFile = Dir(varItem)
strFolder = Left(varItem, Len(varItem) - Len(strFile))
MsgBox "Folder: " & strFolder & vbCrLf & _
"File: " & strFile
Me.txtLink = strFolder & "\" & strFile
Next
End If
Set f = Nothing
(txtLink being the destination field which gets written to the equipment KB files table)
I could always get them to right click the address in the SharePoint web view and paste it into the field, but sometimes it opens the client app rather than the web app if it's office, and also it's not as slick as guiding them through a couple of buttons.