4

How can I respond to SharePoint surveys through VBA? I am trying to track a workbook's usage through a macro but I can't seem to figure it out or even the proper search terms to to search.

Any pointers towards the right direction would be helpful.

Edit: could I use powershell to create a list on SharePoint through VBA?

3
  • @PimpJuiceIT Yes, I am trying to track my workbook's usage. I could maybe use a backup SP DB but I have no idea where that is or if I have access. I don't need other people to see the usage (I'm just trying to get the values of a few cells on a worksheet in any format), so I guess there won't be any need for collaboration, but, the workbook will be hosted on SP.
    – wizlog
    Jan 24, 2018 at 14:30
  • I meant the backend DBs but I'm not familiar enough with the structure to know where to look but it was just an idea. The workbook may just be part of the content DB but i'm not sure but if there was a DB SQL table that kept track of any correlated stats, etc. then perhaps a simple SQL query would give you the needed detail. Jan 24, 2018 at 15:45
  • My comment to @TechLoard further clarifies what I'm trying to do.
    – wizlog
    Jan 29, 2018 at 14:38

1 Answer 1

0

Edit: could I use powershell to create a list on SharePoint through VBA?

Not specifically using Powershell but this answer attempts to answer the question :
How to create a list on SharePoint through VBA.

(1) Not sure from question as to what exactly your trying to accomplish, but this seems to be the rather most relevant snippet of code to take stuff from Excel and then write it out to a sharepoint list :

Excerpts from the link follow :
Original Question (Only the relevant portions pasted here):

I have an automation report that users use to generate a customer report and I want to see who actually is using it and what parameters they are using. I have thought that if I create a hidden list on a sharepoint site, and at the end of the Excel macro I can take the parameters from the main excel page and write it out to a sharepoint list.

This is the answer that may work (Posted to that question above):

Sub UpdateSpecificCells()

'If nobody has the file checked out
If Workbooks.CanCheckOut("http://excel-pc:43231/Shared Documents/ExcelList.xlsb") = True Then
Application.DisplayAlerts = False

'Open the file on the SharePoint server
Workbooks.Open Filename:="http://excel-pc:43231/Shared Documents/ExcelList.xlsb", UpdateLinks:=xlUpdateLinksNever


ActiveSheet.Cells(2, 7).Value = 100
ActiveSheet.Cells(3, 7).Value = 200
ActiveSheet.Cells(4, 7).Value = 300


'Close the workbook
Workbooks("ExcelList.xlsb").Save
Workbooks("ExcelList.xlsb").Close

End If
End Sub

Another Snippet as Answer to the same question :

This updates the sharepoint list via Listobject's publish method

Public Sub PublishList()
' Get the collection of lists for the active sheet
Dim L As ListObjects
Set L = ActiveSheet.ListObjects
' Add a new list
Dim NewList As ListObject
Set NewList = L.Add(xlSrcRange, Range("A1:G8"), , True)
NewList.Name = "PartsList"
' Publish it to a SharePoint site
NewList.Publish Array("http://sharepointportal.xxx.com/personal/xxx/_layouts/viewlsts.aspx?BaseType=0", _
"NewLists "), True
End Sub

Link to the original Content here.

Two other links that may be helpful follow :
(2) How to add items to a Sharepoint list using Excel VBA?

Pasting a relevant portion from the Accepted Answer there:

All data manipulations can be done through SharePoint Lists Web service named lists.asmx. You have to call into that web sevice. For instance UpdateListItems method should do what MS Access does now.

One option you can use to access the lists web sevice, is "Microsoft SOAP Toolkit" which has to be installed as office component (never done that, but here is an article describing it: http://oreilly.com/pub/h/1306

Another option is to use MSXML library (which is always available) and sending SOAP requests as simple HTTP requests. Then parsing the results as xml. There is an article showing examples how to do it: http://blogs.msdn.com/b/jpsanders/archive/2007/06/14/how-to-send-soap-call-using-msxml-replace-stk.aspx

(3) VBA code to insert records into a Sharepoint database list

Not pasting any content from this link since not sure from your question whether this would be particularly relevant to your situation or not. Please cleck on the hyperlink to check...

Two other references that I would like you to kindly go through :

(4) Update the data in an existing SharePoint list using VBA
Well, thats not the exact title of that question there but this seems to be the actual question asked there. This could be helpful to you.

(5) Update Sharepoint list with new values from an Excel sheet (using VBA)
Please have a look at the answers to this question also ...

I guess that only if you are able to give a detailed clarification as to what exactly you are intending to accomplish, only then can we help you any further ...

1
  • Techlord, thank you for the answer. I am trying to track workbook usage (I clarified a little bit in the comment to my question). In next part of 1, multiple people will be using (copies) my workbook at the same time—no single list can reliably be checked-out-able. In response to (2), I get a "cannot connect to the server at this time. Your Table cannot be published" error on the NewList.Publish line. Do I need to import lists.asmx as a reference? it isn't available for me. Note: I copied the web address from the list information settings page. Further note: I am connected to SP.
    – wizlog
    Jan 24, 2018 at 14:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .