vba to check if folder exists in SharePoint and if not then create folder

[anne.gomes]

Hi

So I want my code to loop through and check if folder in sharepoint exists and if so then save PDF into it depending on the business unit. if there is no folder then create it.

Sub SaveTo_SharePoint
busUnitList = Array( "A", "B", "C", "D", "E", "F", "G" )
Dim strFile As String
strFile = "http://site/Reporting%20Documents/"
For Each busUnit In busUnitList
    If Dir(strFile, vbDirectory) = busUnit Then
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:="http://site/Reporting%20Documents/" & fileName, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Else
        MkDir (strFile & fileName)
    End If
Next busUnit
End Sub

I get an error saying 'bad file name'

Anyone know why?

Thanks

[Bob Phillips]

I would think it is because the variable filename is not given any value in the ExportAsFixedFormatType method.

Why do you set the variable strFile to the base path and then not use it later but repeat the base path name?

Shouldn't it be

MkDir strFile & busUnit

and then call ExportAsFixedFormatType after creating the directory?

[anne.gomes]

Hey xld,

Yes busUnit is what it should be, I just thought I could also use fileName since I have called it before in other loops.

What do you mean by 'call ExportAsFixedFormatType after creating the directory'?

Also I get an error on line

MkDir (strFile & busUnit)

saying path/file access error

Thank you!!!

[Bob Phillips]

Anne,

If filename is declared as a public variable, you could re-use it here, but if declared in another procedure it will not exist in this one. It will get created implicitly when referenced if you are not using Option Explicit (more on this later), but you should not rely on either in my view. If it has been setup earlier, it is far better to pass it as a parameter in the call to this procedure in my view, make it clear where the values are coming from. Deliberate obfuscation is bad enough, accidental or unaware obfuscation is a tragedy.

You should always use Option Explicit at the start of your modules. I know Bill Jelen does believe this, but as I have told him many times, he is wrong, it makes for far better code development, and forces better practices (declare all of your variables). You might have gotten an error on filename if you used this if it isn't a public variable (but still better to be more obvious on what filename is as I said above).

As for ... 'call ExportAsFixedFormatType after creating the directory', I mean that if the directory doesn't exist you want to create it, but you still want to save trhe pdf, so you need another call to that method after MkDir.

[anne.gomes]

Okay thanks xld, I will try and come up with a new solution

[anne.gomes]

Does anyone know how the MkDir is formatted?

MkDir "//site/Reporting%20Documents/" & fileName

Gives me an error

[snb]

I would use a space instead of %20

[anne.gomes]

Hey snb,

It still gives me an error: Path/File access error

Any ideas?

[Paul_Hossler]

I think the format depends on if the SharePoint or other server iss hosted on a Unix or Windows Servers, etc.

Try \ instead if / in your ...

MkDir "//site/Reporting%20Documents/" & filename

[anne.gomes]

Thank you Paul_Hossler!!! It did the trick!

Here is the code everyone

Sheets(graphTabList).Select
    
    If Dir("//site/Reporting%20Documents/& fileName", vbDirectory) = "" Then
    
    MkDir "//site\fbusharedservices\Reporting%20Documents\" & fileName
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    fileName:="http://site/Reporting%20Documents/" _
    & fileName & "\" & fileName, IgnorePrintAreas:=False, OpenAfterPublish:=False
    
    Else
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    fileName:="http://site/Reporting%20Documents/" _
    & fileName & "\" & fileName, IgnorePrintAreas:=False, OpenAfterPublish:=False
    End If

[snb]

I'd prefer:

If Dir("//site/Reporting Documents/& fileName", 16) = "" Then   MkDir "//site\Reporting Documents"
  sheets(graphTabList).ExportAsFixedFormat 0,"http://site/Reporting Documents/"  & fileName