1

Good afternoon,

I have got a "program" in VBA that has to write in a SharePoint Online list.

Problem:

I used to insert data from an excel file (via VBA) to a SharePoint 2013 list by connecting directivity to the database underlying (via ADODB connection).

With SharePoint Online this is no more possible, indeed I had to come up with a different solution by using CSOM (C#) library.

After struggling for a week to perform a well-done insert that satisfy all my needs…now I am stuck.

Goal:

I need a way in which VBA code can cast my insert (C#) by using the parameters that I communicate via VBA => Full Process/idea explained

I found some references c# - How to call .NET methods from Excel VBA? but for me it is difficult to implement it to my code.

This is my current code:

using Microsoft.SharePoint.Client;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security;
using System.Text;
using System.Threading.Tasks;
namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
using (ClientContext clientContext = new ClientContext("Sharepoint list URL"))
{
string Uname = "Username";
string password = "Password";
SecureString Securepasseord = GetSecureString(password);
clientContext.Credentials = new SharePointOnlineCredentials(Uname, Securepasseord); clientContext.ExecuteQuery();

clientContext.ExecuteQuery();

List oList = clientContext.Web.Lists.GetByTitle("Target List Name");

//clientContext.LoadQuery();
clientContext.ExecuteQuery();

ListItemCreationInformation listCreationInformation = new ListItemCreationInformation();
ListItem oListItem = oList.AddItem(listCreationInformation);

oListItem["Client"] = "John";
oListItem["City"] = "New York";
oListItem["Company"] = "ZXY";



oListItem.Update();
clientContext.ExecuteQuery();
}
}
private static SecureString GetSecureString(String Password)
{
SecureString oSecurePassword = new SecureString(); foreach (Char c in Password.ToCharArray()) { oSecurePassword.AppendChar(c); }
return oSecurePassword;
}
}
}

I thought about creating a JSON object to link the two, but if it will manage more than a request at the same time it would be a mess. I would like to keep a direct connection between these systems.

Thank you in advance for your answers.

Regards, Daniele

Edit: for this project, I cannot use OneDrive/Excel Online solutions due to internal policies. I need to bring data to SharePoint in this way and then I can start working with Power Automate to manage all the notifications/approvals flows.

Stroz
  • 11
  • 4
  • Have you considered using Microsoft Power Automate? It provides a simple direct connection from Excel to SharePoint, no code required. no VBA, no C#. There is a button on the Share Point site, in the list toolbar to start working with it, called "Automate". – Nikolay Jan 27 '22 at 18:40
  • Thank you, Nikolay for your reply. Unfortunately, I am not allowed to use excel online/OneDrive (Client's internal policies), indeed I am using Power Automate for notifications and approvals management. The CSOM solution was the only way I found out. Sadly, I have inherited an excel based application...otherwise, I would have switched everything in power apps and correlated Microsoft solutions. – Stroz Jan 27 '22 at 22:50

1 Answers1

0

In the end, I solved the problem by using the Main(string [] args). I will pass data via the command line. Easy, direct, fast and reliable.

For example:

Client = args[0];

City   = args[1];

Company= args[2];

....................


oListItem["Client"] = Client;

oListItem["City"] = City;

oListItem["Company"] = Company;
Peter Csala
  • 17,736
  • 16
  • 35
  • 75
Stroz
  • 11
  • 4