VBA Mapping Sharepoint Drive to File Explorer To Loop Through PDFS-CodePudding

I have a script within an excel that needs to loop through whichever folder its in on the Sharepoint, and attach it to an email.

As I understand I need a directory for this and DIR wont work on Sharepoint, so I am mapping the folder the file is in as the Z network drive to the computer and then looping through from there.

However, if the Z drive already exists because I ran this before, I need to remove it which sometimes throws an error of "This network connection has file open or Requests pending" and I cannot remove it, and have to run the script again and it usually works

Here is my code:

'Create Directory to find PDF
On Error GoTo mapped:
networkPath.mapnetworkdrive "Z:", ThisWorkbook.path
mapped:
Application.Wait Now   #12:00:10 AM#
networkPath.RemoveNetworkDrive "Z:"
networkPath.mapnetworkdrive "Z:", ThisWorkbook.path
file_name = Dir("Z:\*.PDF")

'Find the PDFs
Do While Len(file_name) > 0
    If Right(file_name, 3) = "pdf" Then
        pdf_name = file_name
        GoTo foundpdf
    Else
    End If
Loop

Is there a better way to loop through all PDFs on the sharepoint then mapping the Z drive over and over again or is there a better way to overcome the Z drive already existing?

CodePudding user response：

Dir will lock the files until Windows release it, so don't use it when you need to modify the network. instead, you can do this

Dim fso As Object
Dim file As Object
Set fso = CreateObject("scripting.filesystemobject")
For Each file In fso.getfolder("Z:\").files
    If Split(file, ".")(UBound(Split(file, "."))) = "xlsx" Then
        pdf_name = file_name
        GoTo foundpdf
    End If
Next file