Publish an Excel workbook on Sharepoint

Question

I have an excel workbook and need to publish it to a SharePoint site using Excel VBA. Therefore, I created a method in the module using the code below:

Private sc_Lists As SoapClient30 Public c_WSDL_URL As String Private Const c_SERVICE As String = "Lists" Private Const c_PORT As String = "ListsSoap" Private Const c_SERVICE_NAMESPACE As String = "http://schemas.microsoft.com/sharepoint/soap/" Private Sub Class_Initialize() Dim str_WSML As String str_WSML = "" Set sc_Lists = New SoapClient30 c_WSDL_URL = glb_URL sc_Lists.MSSoapInit2 c_WSDL_URL, str_WSML, c_SERVICE, c_PORT, c_SERVICE_NAMESPACE sc_Lists.ConnectorProperty("WinHTTPAuthScheme") = &H1 sc_Lists.ConnectorProperty("UseSSL") = True sc_Lists.ConnectorProperty("AuthUser") = "username" sc_Lists.ConnectorProperty("AuthPassword") = "pass" End Sub 

The problem is when I open an excel document and try to publish it, then it asks for the credentials of my SharePoint site, even if I provided them in the code, as seen above. I need to not request credentials when publishing.

Answer 1

I had problems with auth when trying to click on SP using VBA; it is just fragile and thin. One solution that I used at my work was to map the SP site as a network drive instead and just save the file directly (as in this UC lesson) , thereby avoiding this problem.