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