I am currenty trying to import a Sharepoint list in an Excel sheet via VBA and thanks to this: Import Sharepoint List into Excel Using VBA Only It is working pretty well, but if i leave the "VIEWNAME" field blank it only imports the list in the default view and if i put another name in the field it throws an error. Thanks in advance
Sub AddImportTable(KPIrow As Integer)
Dim objMyList As ListObject
Dim objWksheet As Worksheet
Dim strSPServer As String
Dim SERVER As String
beforeServerInput:
ServerInput.Show ' This is just a custom inputbox userform but works the same '
SERVER = ServerInput.TextBox1
If SERVER = "" Then
a = MsgBox("You have not entered a right statetment. Continue?", vbYesNo, "ERROR")
If a = vbNo Then
Exit Sub
ReturnValue = 1
Else
GoTo beforeServerInput
End If
End If
Dim LISTNAME As String
beforeListInput:
ListInput.Show
LISTNAME = ListInput.TextBox1
If LISTNAME = "" Then
a = MsgBox("You have not entered a right statetment. Continue?", vbCriticalYesNo, "ERROR")
If a = vbNo Then
Exit Sub
ReturnValue = 1
Else
GoTo beforeListInput
End If
End If
Const VIEWNAME As String = ""
VIEWNAME = InputBox("Please enter the name of the view you want to import. For example: 'All Items'. If you leave this blank standard view will be taken.", "View?")
' The SharePoint server URL pointing to'
' the SharePoint list to import into Excel.'
strSPServer = SERVER & "/_vti_bin"
' Add a new worksheet to the active workbook.'
Set objWksheet = Sheets.Add
On Error GoTo del
Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, _
Array(strSPServer, LISTNAME, VIEWNAME), True, , Range("A2"))
objWksheet.Visible = xlSheetHidden
ListInput.TextBox1 = ""
ServerInput.TextBox1 = ""
Sheets("Sources").Cells(KPIrow, 1) = KPIrow - 2
Sheets("Sources").Cells(KPIrow, 3) = SERVER
Sheets("Sources").Cells(KPIrow, 4) = LISTNAME
objWksheet.Name = Sheets("Sources").Cells(KPIrow, 2)
ReturnValue = 0
GoTo ende
del:
Application.DisplayAlerts = False
objWksheet.Delete
Application.DisplayAlerts = True
Set objMyList = Nothing
Set objWksheet = Nothing
Sheets("Sources").Cells(KPIrow, 2) = ""
ReturnValue = 1
Err = MsgBox("I am sorry, but an Error occured.", vbCritical, "Error")
ende:
On Error Resume Next
Set objMyList = Nothing
Set objWksheet = Nothing
answer = MsgBox("Do you want to save? It is heavily recommended, because otherwise you can't refresh everything.", vbYesNo, "Save")
Application.DisplayAlerts = False
If answer = vbYes Then
ActiveWorkbook.Save
End If
Application.DisplayAlerts = True
End Sub
PS: I am using Excel 2010 but im not sure which sharepoint version