Show graph in userform but via sharepoint

[AliAls]

Hello,

At the moment I am building a calculation tool with VBA and userforms. The idea is that this excel file will become a joint file that will be shared via teams (sharepoint). So that every user can pass on their data in the same file.

To get this working, the excel file must be opened via the desktop excel app, so that the userforms become visible. This works fine and I have tested it.


My problem now is that I want to display a graph in the userform, which is on a sheet. If I do this locally it works fine with the following code:

Sub ChangeChart(ChartName As String)


    Dim CurrentChart As Chart
    Dim FName As String
    Dim FNameSharePoint As String
       
    FName = ThisWorkbook.Path & "/temp.gif"
    
    Set CurrentChart = ThisWorkbook.Sheets("Grafieken").ChartObjects(ChartName).Chart
    
    CurrentChart.Export Filename:=FName, Filtername:="GIF"


    
    RegistratieForm.imgGrafiek.Picture = LoadPicture(FName)
    
    
End Sub

But when the excel file is opened via teams(sharepoint) I get an error:



https://im.ge/i/FYQDhL


I think I should change this filename to something else. Maybe to a URL? I hope someone can help me with this. Thanks in advance!

[AliAls]

Hello,

At the moment I am building a calculation tool with VBA and userforms. The idea is that this excel file will become a joint file that will be shared via teams (sharepoint). So that every user can pass on their data in the same file.

To get this working, the excel file must be opened via the desktop excel app, so that the userforms become visible. This works fine and I have tested it.


My problem now is that I want to display a graph in the userform, which is on a sheet. If I do this locally it works fine with the following code:

Sub ChangeChart(ChartName As String)


    Dim CurrentChart As Chart
    Dim FName As String
    Dim FNameSharePoint As String
       
    FName = ThisWorkbook.Path & "/temp.gif"
    
    Set CurrentChart = ThisWorkbook.Sheets("Grafieken").ChartObjects(ChartName).Chart
    
    CurrentChart.Export Filename:=FName, Filtername:="GIF"


    
    RegistratieForm.imgGrafiek.Picture = LoadPicture(FName)
    
    
End Sub

But when the excel file is opened via teams(sharepoint) I get an error:



https://im.ge/i/FYQDhL


I think I should change this filename to something else. Maybe to a URL? I hope someone can help me with this. Thanks in advance!

Luckily I got it working with the following code:

Sub ChangeChart(ChartName As String)


    Dim CurrentChart As Chart
    Dim ImageURL As String
    Dim FName As String
 
    ImageURL = "https://sharepointURL" & "/temp.gif"
    FName = Environ("temp") & "" & Mid(ImageURL, InStrRev(ImageURL, "/") + 1)
    
    Set CurrentChart = ThisWorkbook.Sheets("Grafieken").ChartObjects(ChartName).Chart
    
    CurrentChart.Export Filename:=FName, Filtername:="GIF"


    
    RegistratieForm.imgGrafiek.Picture = LoadPicture(FName)
    
    
End Sub