Creating folders in sharepoint site with VBA

Question

hey all,

I'm trying to write a VBA script that will connect to SharePoint site : https://name.sharepoint.com/sites/name/folder/folder2, will check whether folder1 and folder2 already exist, and will create as them as necessary.

So far the only thing that work for me is mapping the site as a drive , but since this script is intended to be used by a broad audience I prefer to find a safer alternative, does anyone have any idea in mind ?

Thx in advance

Answer 1

Dear MB_D, 

Good day! Thank you for posting to Microsoft Community. We are happy to help you

Based on your description, we understand that you want to write scripts to create specific folders in SharePoint Automation.

In my experience, I don't think VBA is the right tool for your needs, I think your needs may be possible through PowerAutomate or PnP Powershell. Since our forums focus on SharePoint functionality and are very limited in the creation of automated scripts, we encourage you to post in the following forums. I am sure that our experts from that team can address your query effectively and accurately.

Ref: PnP PowerShell Overview | Microsoft Learn & Forums - Power Platform Community (microsoft.com)

Thank you for your cooperation and understanding. Please do not hesitate to post your queries in Microsoft Community and we will always do our best to assist you!

Sincerely,

Tin | Microsoft Community Moderator

Answer 2

I am dealing with the same issue. My Payroll Dept has a template Macro Sheet from a vendor that exports the data keyed in the spreadsheet into individual formatted text files. Part of their routine is to create sub-folders relative to the location of the workbook and store the files. I have been beating my head against the Search Engine for a day now and it is clear that this capability has been engineered OUT of the SharePoint service. Just to be clear, these are both Microsoft products and they are not interoperable!!

By removing these capabilities, you are moving us backwards. So you can create AI, but you cannot allow VBA to create folders in SharePoint!? REALLY!?

The response provided is a very polite pile of BS.

Given the lack of integration between Power Automate and VBA within Excel, this is not a reasonable solution. It is not a solution at all.

Can someone PLEASE fix this issue in the products?

Thank you

Brian

Answer 3

Dear Brian Hildebrandt , 

Good day! I understand your mood very well.

It should be noted that Visual Basic for Applications (VBA) for Office is mainly for Office desktop applications, while SharePoint builds a network environment, due to the difference between the local environment and the cloud environment, we cannot use VBA to create scripts for SharePoint.

If you want to implement automation in SharePoint to create subfolders, you can use PowerAutomate to implement your needs, and when you have VBA programming experience, you can implement more requirements through PowerAutomate.

Ref: Forums - Power Platform Community (microsoft.com)

Thanks for your cooperation.

Sincerely,

Tin | Microsoft Community Moderator

Answer 4

Thanks for the reply! I have been doing more research on this as it is a serious issue for my business user. I can create files in the VBA macro but only in the same SharePoint folder. I would be happy to explore using PowerAutomate for this. How do I use PowerAutomate within the VBA Macro? Is there a way to trigger a PowerAutomate flow using VBA? Can I pass parameters (folder path) into the flow? What about the REST API? Can I use the REST API within VBA? I expect that the vendor providing the spreadsheet will work with me but I have to come up with a solution. We would use the SYNC feature; however due to the number of files stored in the document library, it blows up OneDrive and generally is not functional or not reliable.

Any assistance you can provide would be appreciated. I am beginning to feel that moving to SharePoint was a mistake?

Thanks

Brian

Answer 5

I'm having the same issue and I'm not happy with their answer here. It limits the already limited Sharepoint (400 char) to 255 char. Can't create or move to the dir. So they want us to use 2 programs to do what 1 program should be able to do. Here's the theory of how to get this to work:

Create, use and save files to Sharepoint directly by using ENVRION("HOMEPATH") & "" & "[CompanySharepoint]" & " - " & [LibraryName] & "" & [TmpFileDir]...then dump your files here. I would like to use some sort of move command here, but I don't know if that will work. At this point you could use Power Automate to trigger on created file and move it to a specific directory or maybe use some logic to get it to a dynamic directory.

Makes this much more convoluted and stupid.

Private Sub Command3_Click()

Dim path As String

Dim CompanySharepoint As String

Dim LibraryName As String

Dim TmpFileDir As String

CompanySharepoint = "SharepointDir"

LibraryName = "Library"

TmpFileDir = "Temp"

path = Environ("HOMEPATH") & "" & CompanySharepoint & " - " & LibraryName & "" & TmpFileDir & ""

MsgBox path

End Sub