Publishing Excel Named Ranges to SharePoint Programmatically

Question

I have an excel file with named ranges saved in a document library in SharePoint. I created some excel web access web parts in order to display the excel files I have. My problem is I can't seem to find a way to publish my excel files so that only the named ranges will show up.

I know this can be done manually by setting the browser view options when saving it to SharePoint but I need to do it via code because I need to run it on multiple SharePoint sites.

I was checking Visio services and saw that it had ServerPublishOptions I was wondering if Excel service have something similar that I can use. I was also looking at PublishObjects of excel interop but I'm not sure if it will address my issue.

Bruno Leite Bruno Leite · Accepted Answer · 2013-05-10T12:39:18

This code select a range and insert into a sharepoint List using ADO, it is a easy way for me.

Public Const strSharePointInfo = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=http://sharepoint.server.com/Path/;LIST={12312456-124A-78BC-B8E7-1E526B74A015};"

Sub InsertRecordSetOnSharePoint(Rg as Range,ShtName as String)
'Bruno Leite
'http://officevb.com

Dim cn As ADODB.Connection 'Conexao para a Lista do SharePoint
Dim i As Integer,SQL as string


'sql to insert
SQL = "INSERT INTO [LISTNAME] (SELECT * FROM [Excel 12.0;DATABASE=" & ShtName & "].["& rg.name &"$])"

'open connection
cn.Open strSharePointInfo

'run SQL
cn.Execute SQL

Set cn = Nothing

Debug.Print "Insert OK"

End Sub

Publishing Excel Named Ranges to SharePoint Programmatically

1 Answers