PDA

View Full Version : [SOLVED] vba to check if folder exists in SharePoint and if not then create folder



anne.gomes
06-09-2014, 03:38 PM
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
06-09-2014, 04:03 PM
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
06-09-2014, 04:27 PM
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
06-10-2014, 02:39 AM
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
06-10-2014, 07:52 PM
Okay thanks xld, I will try and come up with a new solution :)

anne.gomes
06-11-2014, 02:26 PM
Does anyone know how the MkDir is formatted?




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



Gives me an error

snb
06-11-2014, 03:13 PM
I would use a space instead of %20

anne.gomes
06-11-2014, 04:13 PM
Hey snb,

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

Any ideas?

Paul_Hossler
06-11-2014, 06:06 PM
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
06-11-2014, 06:51 PM
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
06-12-2014, 01:03 AM
I'd prefer:


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