1

SO I'm looking for a way to force the login screen to sharepoint WITHOUT downloading any list information, I have two methods that I use to pull information via vba to my excel workbook. currently I am using this first procedure when the workbook opens because it forces a login window to popup to login to sharepoint, but this method downloads information that I don't need. I'm looking for a way to force the login screen without downloading list info. If I go to sharepoint and login first it still does not work.

Dim mySh As Worksheet
Dim spSite As String

Set mySh = Sheet4

If mySh.Range("A1") > "" Then
mySh.Range("A1:C2").ClearContents
End If

Dim src(0 To 1) As Variant

spSite = "https://mysharepoint address" 
src(0) = spSite & "/_vti_bin"

src(1) = "{my list GUID}" 'GUID


mySh.ListObjects.Add xlSrcExternal, src, True, xlYes, mySh.Range("A1")

After I use the above method I can use the following method for everything else I do with my little program. But using this method will not force the login screen and going straight to SharePoint to login first does not work either.

 Dim cnt As ADODB.Connection
 Dim rst As ADODB.Recordset
 Dim mySQL As String

'set connection
 Set cnt = New ADODB.Connection
 Set rst = New ADODB.Recordset

 'sql select statement
 mySQL = "SELECT Something,Something1withsumfunction,Something2 From[Table] Where [Something4]<>'Somethingelse' Group By something1"

'open connection
With cnt
    .ConnectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=mysharepoint;LIST={GUID};"
    .Open
End With


'open table / recordset
rst.Open mySQL, cnt, adOpenForwardOnly, adLockReadOnly

'check if data exist for recordset
If Not (rst.BOF And rst.EOF) Then
    'copy data to cell a1 if there is data
    Sheet7.Range("A2").CopyFromRecordset rst
End If

'close recordset/connection and clean memory
If CBool(rst.State And adStateOpen) = True Then rst.Close
Set rst = Nothing
If CBool(cnt.State And adStateOpen) = True Then cnt.Close
Set cnt = Nothing

Any Ideas, Im not real sure what I'm looking at in the first procedure, I just know it works and does what I need kind of.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.