Using excel to create a folder in a sharepoint library

[marcusblackcat]

Hi all - i am working in Excel 2010 and our comnpany are starting to use SharePoint.

Within a number of the excel files we use, there is a need to produce a pdf report of one or more of the worksheets.

This worked fine when saving using network folders (simple thisworkbook.path method)

However, when the file is moved into a sharepoint folder, the "thisworkbook.path" gives the "Bad file name or number" error

Running thisworkbook.path in the immediate window shows "Invalid use of property"

So, what I need to do is to make the excel spreadsheet check if a folder exists in the same folder as the worksheet. Using this in network drives I use the following:

I have the sharepoint library mapped in my network locations but this is not working

If Dir(ThisWorkbook.Path & "\PDF Reports", vbDirectory) = "" Then
MkDir Path:=ThisWorkbook.Path & "\PDF Reports"
End If

So it should look to see if there is a folder named PDF reports and, if not, it should make that directory. The debugger errors on the bold part. Ironically, if I comment out that bit of code and manually create the folder, it saves fine using the "thisworkbook.path" method so I'm at a loss!!

Anyone have any idea how I can do this when the file is moved to SharePoint?

I've spent the last hour on the site trying to find an answer but got sick of getting hopes up to be dashed when it wasn't what I wanted.

Thanks in advance