CODEWITHSUNDEEP
vbaexcelsharepointsharepoint-2010
First
First

Reputation: 11

how to export an Excel graph to sharepoint

I wanted to know how to export a diagram in sharepoint with an Excel macro.

I've already code a macro to export my graph as a gif picture, here is my code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ExportGraph
End Sub


Sub ExportGraph()
    ' Déclaration des variables
    Dim Sheets As Variant
    Dim NomSheet As String
    Dim Graph As Variant
    Dim NomGraph As String
    Dim Fich As String
    Dim i As Byte

    ' Boucle parcourant les feuilles
    For Each Sheets In ActiveWorkbook.Sheets
        NomSheet = Sheets.Name
        ' On parcours les graphiques de la feuille
        For Each Graph In Sheets.ChartObjects
            i = i + 1

            ' Selection d'un graphique
            Sheets.ChartObjects(i).Activate

            ' Récupération du nom du graphique
            NomGraph = ActiveChart.ChartTitle.Characters.Text

            ' Chemin où l'on veut exporter les graphiques
            Fich = "d:\LocalData\p080931\Desktop\racc\"
            ' On lance la procédure d'export
            ActiveChart.Export Filename:=Fich & NomGraph & ".gif",
FilterName:="GIF"
        Next
    Next
End Sub

But I want to export it directly in my SharePoint document library.

Any idea to do this ? I didn't find a function which work with an URL.

Thanks a lot

Upvotes: 1

Views: 1932

Answers (2)

clarencebuttowski
clarencebuttowski

Reputation: 58

I realize this is late but hopefully it is useful to someone else. There is a Windows bug however where the code below will only work if you open up the SharePoint in a browser navigate to the SharePoint library and hit the 'Open with Explorer' button under the Library tab in the Connect & Export section. This only needs to be done one-time per machine.

You can change the file type using the examples I have included below. BMP seems to provide the highest quality image. 

Sub PublishSharePoint()

'Unprotect worksheet
Worksheets("My Worksheet").Unprotect

Call SaveImage("My Worksheet", "B8:N46", "Report Name")

'Protect worksheet
Worksheets("My Worksheet").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

This will call the routine below

Sub SaveImage(sSheet As String, sRange As String, sFile As String)


Worksheets(sSheet).Activate

Set Plage = ThisWorkbook.Worksheets(sSheet).Range(sRange)

Plage.CopyPicture

With ThisWorkbook.Worksheets(sSheet).ChartObjects.Add(Plage.Left, Plage.Top, Plage.Width, Plage.Height)
.Activate
.Chart.Paste
.Chart.ChartArea.Border.LineStyle = xlNone
'.Chart.Export "C:\...\" & nameFile & ".png", "PNG"
'.Chart.Export "C:\...\" & nameFile & ".jpg", "JPG"
.Chart.Export "\\url.sharepoint.com\sites\SiteAssets\SitePages\Page\" & sFile & ".bmp", "BMP"
End With

Worksheets(sSheet).ChartObjects(Worksheets(sSheet).ChartObjects.Count).Delete

Set Plage = Nothing

End Sub

If you want to map the site as a network drive this will also work:

Dim oNetwork As Object
Set oNetwork = CreateObject("WScript.Network")
oNetwork.MapNetworkDrive "Z:", "http://SharePoint.com/sites/MySite/SiteAssets/SitePages/Report", False

Then remove drive

oNetwork.RemoveNetworkDrive "Z:"

Upvotes: 1

user5818624
user5818624

Reputation:

It is possible that you could map a network drive to your SharePoint site/subsite and then have your code export it there. Open up the Windows Explorer, right click on "Computer" on the left hand side and click "Map Network Drive...". Once the Map Network Drive dialog appears, choose the drive letter you wish to use and in the "Folder" dropdown paste in the url to the SharePoint site.

enter image description here

enter image description here

Then in your code, point your path to this shared drive and where the document library is located.

Fich = "S:\DocumentLibrary\"

Hope that helps!

Upvotes: 0

Related Questions