代码之家 › 专栏 › 技术社区 › Rhyfelwr

将列表从SharePoint作为区域(或具有固定表名)导入到Excel

sharepoint vba excel

Rhyfelwr · 技术社区 · 5 年前

下面的代码从SharePoint中提取一个列表并将其导入到Excel。我的问题是,它将它作为一个表导入,而不是作为一个范围导入,并且我不能自动将它转换为范围,因为每次运行脚本(Table1、Table2,…)时,表名都不同。等等。

我的问题是:我可以直接从SharePoint将列表导入为Excel区域,这样就不必转换它了吗?如果不是,我可以这样做吗?每次运行导入过程时,导入表的名称都是fix?

以下是我的代码:

   Sub SharePoint_Import()
        Dim objMyList As ListObject
        Dim objWksheet As Worksheet
        Dim strSPServer As String
        Const SERVER As String = "xxxx.xxxxx.xxx.net/xxxx/xxxx" 'SP server
        Const LISTNAME As String = "{1234567-1234-1234-1234-1234567891}" 'SP List ID
        Const VIEWNAME As String = "" 

            Set RData = Sheets("rawdata") 'reset import sheet
            RData.UsedRange.ClearContents

        strSPServer = "https://" & SERVER & "/_vti_bin" '<- _vti_bin is necessary
        Set objWksheet = RData

          Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, Array(strSPServer, LISTNAME, VIEWNAME), False, , Range("A1"))
          Set objMyList = Nothing
          Set objWksheet = Nothing

    End Sub

2 回复 | 直到 5 年前

Siddharth Rout 5 年前

这样做

〔1〕变化 RData.UsedRange.ClearContents 到 RData.Cells.Clear . 这将彻底清除工作表。删除格式、文本、表格等。

[2]导入表后,添加此代码。因为只有一个表,所以可以使用表号来处理它。

Dim tbl As ListObject
Set tbl = RData.ListObjects(1)

Dim tblRng As Range
Set tblRng = tbl.Range

'~~> Convert table to range
tbl.Unlist

'~~> Comment this in case you do not
'~~> want to remove the formatting
With tblRng
    .Interior.ColorIndex = xlColorIndexNone
    .Font.ColorIndex = xlColorIndexAutomatic
    .Borders.LineStyle = xlLineStyleNone
End With

FunThomas 5 年前

可以使用ADODB查询SharePoint列表。检查以下代码(未测试,因为我没有可用的SharePoint,但在过去使用过类似的代码)

Const SERVER As String = "xxxx.xxxxx.xxx.net/xxxx/xxxx" 'SP server
Const LISTNAME As String = "{1234567-1234-1234-1234-1234567891}" 'SP List ID

Dim connStr As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset

connStr = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=1;RetrieveIds=Yes;" _
        & "DATABASE=" & SERVER _
        & ";LIST=" & LISTNAME & ";"
conn.Open connStr
Set rs = conn.Execute("select * from list")

Dim rData As Worksheet
Set rData = thisworknbook.Sheets("rawdata") 'reset import sheet
rData.UsedRange.ClearContents
rData.Range("A1").CopyFromRecordset rs
conn.Close

推荐文章