Copy file path into Access from mounted SPoint drive (1 Viewer)

nortonm

Registered User.
Local time
Today, 13:18
Joined
Feb 11, 2016
Messages
49
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:18
Joined
Oct 29, 2018
Messages
21,357
Hi. Are you saying txtLink will have the T: drive in the file path?
 

nortonm

Registered User.
Local time
Today, 13:18
Joined
Feb 11, 2016
Messages
49
Thanks DBGuy...well, for the normal (nonSP) network share the txtLink holds the full share path of the file, so in my environment that's an S:, but i've got the Sharepoint root folder (\\teams.tees.ac.uk@ssl\sites\6\1) mapped to a T: drive. So for example on SPoint UNC it would be \\teams.tees.ac.uk@ssl\sites\6\1\Clinical skills (CS)\Clinical Skills pre 2020 for revision\Fluid Balance.docx, but the drive path would read T:\Clinical skills (CS)\Clinical Skills pre 2020 for revision\Fluid Balance.docx, so it would open if using the Application.FollowHyperlink command.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:18
Joined
Oct 29, 2018
Messages
21,357
Thanks DBGuy...well, for the normal (nonSP) network share the txtLink holds the full share path of the file, so in my environment that's an S:, but i've got the Sharepoint root folder (\\teams.tees.ac.uk@ssl\sites\6\1) mapped to a T: drive. So for example on SPoint UNC it would be \\teams.tees.ac.uk@ssl\sites\6\1\Clinical skills (CS)\Clinical Skills pre 2020 for revision\Fluid Balance.docx, but the drive path would read T:\Clinical skills (CS)\Clinical Skills pre 2020 for revision\Fluid Balance.docx, so it would open if using the Application.FollowHyperlink command.
Hi. Thanks for trying to clarify, but I'm still confused. Does the file path field (txtLink) have the mapped drive path to SP or the UNC path? If UNC, have you tried using the mapped path?
 

nortonm

Registered User.
Local time
Today, 13:18
Joined
Feb 11, 2016
Messages
49
Thanks - righto - just checked again, and it doesn't actually go as far as populating the txtLink textbox, as it's at that point the error comes up. Just went into debug to check, and the part of the script it failed in is highlighted - it's the strFile = Dir(varitem) line - and when I hover over varitem it reads varitem = "https://teams.tees.ac.uk/sites/6/1/Shared Documents/Clinical skills (CS)...that's all I can see when I hover. So that's the path it retrieves. So it looks like I missed off the 'Shared Documents' part of the path in my previous post.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:18
Joined
Oct 29, 2018
Messages
21,357
Thanks - righto - just checked again, and it doesn't actually go as far as populating the txtLink textbox, as it's at that point the error comes up. Just went into debug to check, and the part of the script it failed in is highlighted - it's the strFile = Dir(varitem) line - and when I hover over varitem it reads varitem = "https://teams.tees.ac.uk/sites/6/1/Shared Documents/Clinical skills (CS)...that's all I can see when I hover. So that's the path it retrieves. So it looks like I missed off the 'Shared Documents' part of the path in my previous post.
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

nortonm

Registered User.
Local time
Today, 13:18
Joined
Feb 11, 2016
Messages
49
Sorry - I didn't actually sort it out, I was just feeding back where the debug occurred - it's something about that https address that triggers the error 'Runtime error 52: Bad file name or number'. It doesn't seem to want to copy that address into the txtLink field on the form...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:18
Joined
Oct 29, 2018
Messages
21,357
Sorry - I didn't actually sort it out, I was just feeding back where the debug occurred - it's something about that https address that triggers the error 'Runtime error 52: Bad file name or number'. It doesn't seem to want to copy that address into the txtLink field on the form...
Oh, sorry to hear that. Can you actually go to the file if you use the Immediate Window? For example:
Code:
Application.FollowHyperlink "https://teams.tees.ac.uk/...
Might be a good idea to check it manually, then if it works, try manually entering the address into the table. If it still works, then you can step through the code to see how you can make sure the correct path address is entered into the table.
 

nortonm

Registered User.
Local time
Today, 13:18
Joined
Feb 11, 2016
Messages
49
Thanks, sorry, i've had to put it aside for the evening, i'll continue in the morning - many thanks.
 

nortonm

Registered User.
Local time
Today, 13:18
Joined
Feb 11, 2016
Messages
49
Apologies, I couldn't get into this site for ages, it's been down. Anyway, thanks for encouraging me to take a closer look, there was a folder in the SP path not parsing properly. OK now.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:18
Joined
Oct 29, 2018
Messages
21,357
Apologies, I couldn't get into this site for ages, it's been down. Anyway, thanks for encouraging me to take a closer look, there was a folder in the SP path not parsing properly. OK now.
Glad to hear you got it sorted out. Good luck!
 

Users who are viewing this thread

Top Bottom