Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!


Free Excel Forum

Sharepoint Export To Excel

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Does anyone know how to export several lists from sharepoint to excel? Currently I export one at a time via the "Export to spreadsheet" function.

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Output the Name of the Current Excel Workbook Including Extension - UDF
- Free Excel UDF to display the name of the current Excel workbook, including the workbook extension. This function has n
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth

Similar Topics

I am trying to export a chart in a worksheet to a Sharepoint location as a jpg. The code below works with the first export line to my desktop. But I get a runtime error 'method of export of object chart failed' if I try either of the other two that try to send it to sharepoint. I am using Sharepoint 3.0 and Excel 2007. The site is a set as trusted site and I have admin rights.

[code]Sub SaveChrtAsJpeg()
'ActiveChart.Export "C:\Documents and Settings\m204727\Desktop\Temp Files\Chart3.jpg", "jpg"
'ActiveChart.Export " & /charttest.jpg", "jpg"
ActiveChart.Export "", "jpg"
End Sub[code]

I have a hidden excel sheet that houses critical usage statistics that I would like to push to a sharepoint site. I simply want to relay that data to the sharepoint site...

Excerpt of something I read on line:

Exporting data to Excel is well-supported in SharePoint and makes graphing and printing convenient (via the Print with Excel and Chart with Excel options). But it's also possible (and may often be desirable) to export data to Excel just for the sake of manageability. The Excel Export function creates an Excel Web query linking to the original data. In this way, you can create spreadsheets that will accept data, and then push that data to SharePoint.

This can be done by generating an Excel spreadsheet, then linking the spreadsheet to SharePoint (by using Export and Link to Excel from a Datasheet Task Pane). Once this is done, data can be entered into the spreadsheet and pushed from the spreadsheet to Excel with the Synchronize List option.

Any ideas?

hello team-

the following VBA exports "myfile.xml" from within an excel file to "mydocuments" folder on my sharepoint server. It exports perfectly as long as there is no existing "myfile.xml" already in the destination folder. It produces an error where there is a previous incidence of "myfile.xml" already in the destination folder..

Can somebody offer me some VBA to overwrite the previous instance of "myfile.xml" with the current file without prompting the user?

I have read/write administrator rights on the sharepoint so it's not a rights access issue on sharepoint (I get the same error when trying to export to my local c:\ as well)


Code BlockSub ExportXML2Sharepoint()

ActiveWorkbook.XmlMaps("XMLMap").Export URL:= _
End Sub


Code BlockRun time error '-2147467259 (80004005)'

Method 'Export' of object 'XMLMap' failed

Thanks Conor


Is it possible to import\export data to and from Infopath.
i know i can export Infopath forms into Excel but can i export an Excel spreadsheet into Infopath. I presume if this were possible i would have to have similiar fields in infopath to that in excel for the data i want to export.

i will need to be able to do this via a macro

basicly i will be export data from an application into Excel then export to infopath to display on our intranet site via sharepoint 2007, but it all has to work in 2003 versions as this is what the users have.

Thank you

I have a macro that does an export of a chart to a Sharepoint image library. This is the code I am using to do the export:

    Dim oChart As Chart    
    ActiveSheet.ChartObjects("Chart 1").Select
    Set oChart = ActiveChart
    oChart.Export Filename:="\\SharePointAddress\NCC\publishingImages\filename.gif", FilterName:="GIF"

The code works fine on one computer but returns an error on another. The error I recieve is:
Run-time error '-2147286987 (80030035)':
Method 'Export' of object '_Chart' failed
Also it will not work on that same computer for a different user.
The code works anywhere if I change the address to an actual folder location like C:\Images.
I realise this might be more of a problem with sharepoint than excel but it doesn't hurt to try.

Not sure if this is even possible, but I have a co-worker who wants to import the data located in the calendar entries in Sharepoint. I know there is a button which will manually export them to excel. Is it possible to export them using vba?

I attempted linking the website to excel, which imported the file names but not their content.


I want to open a Sharepoint view/list directly in Excel. Sharepoint has the Export to Spreadsheet option under the Actions button.

I found this,, which tells how to build a Excel macro to do this. It worked - almost.

The Sharepoint view has a column of lookup fields with multiple values and they manually export just fine.

The Excel macro opens with all other columns but does not include the lookup fields with multiple values. It just skips right over that column of data.

I need the those multiple values.

Using Excel 2007 - I also saved the XLS macro as 2003.

Dear Experts,

I have an issue as given below.
I need to calculate the count of AA which is having import.

AA ? ?
BB ? ?

Here the problem is , i have calculated the counts using the Sumproduct,SUMIFS,function.but xcelsius is not supporting these functions. So the results needs to be calculated based on some other formula. can some one help me. thanks in advance.



Being very much VBA challenged () I am attempting to use the "OutputTo" command in a macro to export a report to a sharepoint site; I am specifying the specific filename. There are no issues with the export, but I'm wondering--is there a way that I can create a unique filename everytime I run the macro (i.e. to have a history of files on the sharepoint)? For example, apend a date and time stamp to the end of the filename?

Appreciate your feedback...

I am using an Access front-end to upload/insert files into an OLE field in the db table. My company is using SQL server as a back-end. Is there a relatively easy way to export a selected group of uploaded files from Access/SQL server & export them to an application like Sharepoint, another MS application, or to a file share location?

I have a SharePoint list that has attachments associated with the items in the list. I need to export the list and all the attachments so I can email it out to people and work on it offline. Does anyone know how this could be done? Can this be done, by exporting it to Access? If yes how?



I'm trying to export an Excel spreadsheet to an XML. The instructions I've
read tell me to go to the "developer" tab and then "export", however the
export function is grey and not available. Any suggestions

Hi All,

I have created an excel application which analyses some data held in SharePoint via a SharePoint list. It then creates an excel export and closes the initial workbook.

I have tried uploading this to SharePoint for use however this causes no end of errors which did not occur when i tested in excel. These errors are usually around objects not being recognised, i tried re-writing my code to dim excel as an application but this makes no difference.

Does Microsoft Office SharePoint Server 2007 allow workbooks with macros to be uploaded and run? Can anyone think of any other reason as to why this does not work?



Hi. I have a report that is in Excel that uses exports from various SharePoint lists. The problem is that two of them can't be refreshed once the document is saved and closed. The other 5 are fine and can be refreshed. Does anyone have any ideas as to why this is happening on the 2 and not on the other 5? I really need to get this report set up in a fashion that all anyone has to do to get the results is just open it and refresh the tables. I can't keep having them re-export the lists each and every time. Thanks to anyone who can help.

Hi, new member here!

I am having some trouble with this.. I usually import data into Access and format/output it from there, but the user wants to do everything in Excel.

On the attached spreadsheet, I have 2 tabs named "data" and "export"

The data tab contains data that I want to ultimately have formatted for export.

The export tab shows the format of the data.

Could someone help me with the code to be able to put behind a button so that the data will be exported as .csv


Hi All,

Here's the short question: the SharePointURL property of ListObject is ReadOnly. I wanna change it!

The long version: I have a SharePoint site where many months ago I did the "Export to Spreadsheet" command. I've since done lots of work on this file, including code that lets the user refresh the data:


So this is a one way pull-the-data-from-SharePoint process. Works fine, back slaps all around.

Now another division wants to use the same process, but wants to keep their own SharePoint list. Easy enough to give them a duplicate site. Now I want to give them a copy of my Excel file, except that it should pull data from the duplicate site.

I don't want to just "Export to Spreadsheet" again from the new site and copy my Excel changes to that workbook. Too many named ranges, dynamic ranges, etc for that to probably work easily.

So any ideas on how to do the equivalent of changing the SharePointURL property of ListObject to point to the new SharePoint?



i've build a database that exports to excel via the transferspreadsheet method.

it pull from the same qry...therefore exports to the same sheet in the excel workbook.

i am running into issues where is wants to export a second sheet and title it "Export1" rather than "Export".

is this because there is already a named range for "Export" i need it to import the qry results to the same sheet "Export" because there is a summary sheet in the workbook that has predefined references to the sheet "Export".


I'm interested in exporting an Outlook Distribution List to Excel. I would need all the properties included in the export. I haven't been able to get this with a "Save as" text and the "Import/Export" feature in Excel only seems to allow you to export all contacts, not a subset(distribution list) within it. Any ideas?


Hi guys

I want to write vba code when the user click on a button in the userform then it should export the todays data from sheet1 in the same folder with the name "log export 14-09-10" ..that means for tomorrow the excelsheet name will be "log export 15-09-10".

I hope anyone can help me out.


I am trying to export and Excel file as Tab delimited and I do not see all of the tabs in the export that I expected. The last 11 columns in the spreadsheet are blank, except for the header row. I was hoping the header row would indicate to the export how many "blanks" i have, and want to see in the export, but it doesn't. Is there a way that I can export to tab delimited in Excel, and have it give me tabs between the blank cells? I understand they are blank because they have no value and the export can't just give me tabs all the way through column IV, but the header row should be the indicator for how wide the spreadsheet is.

I have the following macro set up to export a tab to a new .csv file. What I would like to do is have the filename set up as the value from cell G2, rather than the static "Export" name.

Thanks for any help.

Sub Export()
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ChDir "C:\"
    ActiveWorkbook.SaveAs Filename:="C:\Export.csv", FileFormat:=xlCSV, _
End Sub

I have an access database that pulls in a sharepoint list (we'll call this the master list) that contains a list of hyperlinks to other sharepoint lists (child lists).

I am wondering if it is possible to run a macro to use the child list locations, listed in my master list, to create new tables containing the data in each child list. I would then need to export these child lists to a location, and then delete the tables.

I need to do this with a macro because the number and locations of the child lists are always changing, and the only place where those locations are known on a daily basis will be the master list.

Any ideas?


I have 2 dates in cells I1 & I2, being the start and end dates.

I am currently using the code below successfully :-


=SUMIFS('Bank Export'!$G:$G,'Bank Export'!$A:$A,"Wages",'Bank Export'!$B:$B,">=29/04/11",'Bank Export'!$B:$B,"<31/05/11")

What I would like is to use the 2 dates in I1 & I2, rather than typing them manually as above. But I can't figure out how to incorporate the cell references with the ">=" and "<".

Can anyone show me how to amend the formula?

Many thanks in advance.

I am using the following Macro to copy certain columns to a new Worksheet in a particular order which is working fine however I have two issues:

1 - When the new worksheet is created the column widths are not being exported over so I have go to the new worksheet an manually adjust the width again.

2 - I have a number of columns which will be move when additional columns are added i.e. from column AB to AC which means I have to change the code again.Is there a way to track these so I do not have to change the code again. In row 5 each of the columns has a unique heading.


Sub CreateExportSheet()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    If SheetExists("Export Sheet") = True Then
        MsgBox "The sheet Export Sheet already exist"
        Exit Sub
    End If
    Application.ScreenUpdating = False
    Set DestSh = Worksheets.Add
    DestSh.Name = "Export Sheet"
    Sheets("Sheet1").Columns("D:D").Copy Destination:=Sheets("Export Sheet").Columns("A:A")
    Sheets("Sheet1").Columns("Q:Q").Copy Destination:=Sheets("Export Sheet").Columns("B:B")
    Sheets("Sheet1").Columns("S:S").Copy Destination:=Sheets("Export Sheet").Columns("C:C")
    Sheets("Sheet1").Columns("U:U").Copy Destination:=Sheets("Export Sheet").Columns("D:D")
     Sheets("Sheet1").Columns("AB:AB").Copy Destination:=Sheets("Export Sheet").Columns("P:P")
    Sheets("Sheet1").Columns("AH:AH").Copy Destination:=Sheets("Export Sheet").Columns("E:E")
    Sheets("Sheet1").Columns("N:N").Copy Destination:=Sheets("Export Sheet").Columns("F:F")
    Sheets("Sheet1").Columns("AI:AI").Copy Destination:=Sheets("Export Sheet").Columns("G:G")
    Application.ScreenUpdating = True
End Sub
Function SheetExists(SName As String, _
                     Optional ByVal WB As Workbook) As Boolean
    On Error Resume Next
    If WB Is Nothing Then Set WB = ThisWorkbook
    SheetExists = CBool(Len(Sheets(SName).Name))
End Function

Can anyone help?

So I want to compare a few cells in different workbooks. I want to use the vlookup function, but it doesn't seem to be working.


Sub oheight()
Dim x as integer
x =2 

Application.Workbooks.Open ("Export.xls")
Range("F7") = VLookup(Range("B6"), Workbooks("Export.xls").Range("B2:B302"), 1)

Do While x < 25
  If Not Range("B" & x) = VLookup(Range("B6"),Workbooks("Export.xls").Range("B2:X302"), x) Then
   Range("B" & x).Interior.ColorIndex = 3
End If


End Sub

It is gives me an error saying the vlookup function isn't defined.

Also sometimes it can find export.xls to open, and other times it can't. Anyone have any ideas whats going on?