• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Autoplay a scroll controlled chart and record as video?

C

chiukee

New Member
Hi,

I have a dynamic chart whereby a scrollbar is used to go to the next set of data. What I want to do is to hit "a play button" and the scrollbar will automatically go from 1 to 100 and the resulting changes in the dynamic chart should be recorded as a video.

Previously I scrolled through manually and saved each chart as a jpeg and then made a video out of it, but it is very time consuming.

Can anyone help?

Regards,

CK
 

chiukee​

Above is possible if ...
# You could send a sample Excel-file ,which has Your used sample data and charts
# from 1 to 100 ... do it mean two steps or more?
# Why do You need to record as a video? Could You use that Excel file?
# if You could accept to use VBA.
 
chiukee said:
Hi,

I have a dynamic chart whereby a scrollbar is used to go to the next set of data. What I want to do is to hit "a play button" and the scrollbar will automatically go from 1 to 100 and the resulting changes in the dynamic chart should be recorded as a video.

Previously I scrolled through manually and saved each chart as a jpeg and then made a video out of it, but it is very time consuming.

Can anyone help?

Regards,

CK
Click to expand...
Hello

Iam assuming your dynamic chart is embedded as a chart object in the worksheet named "Sheet1". Update the sheet name and chart object name accordingly and try


Code: 
Sub CreateChartVideo()
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim i As Integer
    
    ' Set the worksheet containing your dynamic chart
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Change "Sheet1" to your sheet name
    
    ' Assuming your dynamic chart is embedded as a chart object
    Set chartObj = ws.ChartObjects("Chart 1") ' Change "Chart 1" to your chart object name
    
    ' Clear any previous animations
    ws.Shapes("PlayButton").Delete
    
    ' Add a play button to the worksheet
    With ws.Buttons.Add(chartObj.Left, chartObj.Top + chartObj.Height + 10, 50, 20)
        .Name = "PlayButton"
        .Caption = "Play"
        .OnAction = "AnimateChart"
    End With
    
    ' Create and initialize scrollbar
    ws.Shapes.AddFormControl xlScrollBar, Left:=100, Top:=100, Width:=100, Height:=20
    ws.Shapes("ScrollBar").ControlFormat.Min = 1
    ws.Shapes("ScrollBar").ControlFormat.Max = 100
    ws.Shapes("ScrollBar").ControlFormat.Value = 1
    
    ' Loop through scrollbar values and capture screenshots
    For i = 1 To 100
        ws.Shapes("ScrollBar").ControlFormat.Value = i
        DoEvents ' Allow screen to update
        
        ' Capture screenshot
        ws.ChartObjects("Chart 1").Chart.Export "C:\Temp\Chart_" & i & ".jpg", "JPG"
    Next i
    
    ' Convert screenshots to video (You may need additional software or libraries for this step)
    ' Your code to combine screenshots into a video
    
    ' Cleanup - delete screenshots
    For i = 1 To 100
        Kill "C:\Temp\Chart_" & i & ".jpg"
    Next i
End Sub

Sub AnimateChart()
    ' Your code to animate the chart by incrementing the scrollbar value
    ' Example: ws.Shapes("ScrollBar").ControlFormat.Value = ws.Shapes("ScrollBar").ControlFormat.Value + 1
End Sub
 
Hi Monty,

thanks for your tips. I already have my own scrollbar, linked to data on sheet "DES6". The creatvideo works fine with scrolling, the data points in column "E" changes, but the chart does not change and it is not saving any of the charts as a jpg. Any thoughts on why? Thanks again for your help!
 

Attachments

  • test save charts as jpeg.xlsm
    55 KB · Views: 1

chiukee​

You replied some of my wonderings.
Press Your [ Play ]-button to get Your Chart live.
... You don't need that Scrollbar with [ Play ].
... My sample won't create those jpgs ... You could use this file instead of that.
... If You really would like to get those jpgs then other code should modify for You file.
 

Attachments

  • test save charts as jpeg.xlsm
    55.7 KB · Views: 6
Monty said:
Hello

Iam assuming your dynamic chart is embedded as a chart object in the worksheet named "Sheet1". Update the sheet name and chart object name accordingly and try


Code: 
Sub CreateChartVideo()
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim i As Integer
  
    ' Set the worksheet containing your dynamic chart
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Change "Sheet1" to your sheet name
  
    ' Assuming your dynamic chart is embedded as a chart object
    Set chartObj = ws.ChartObjects("Chart 1") ' Change "Chart 1" to your chart object name
  
    ' Clear any previous animations
    ws.Shapes("PlayButton").Delete
  
    ' Add a play button to the worksheet
    With ws.Buttons.Add(chartObj.Left, chartObj.Top + chartObj.Height + 10, 50, 20)
        .Name = "PlayButton"
        .Caption = "Play"
        .OnAction = "AnimateChart"
    End With
  
    ' Create and initialize scrollbar
    ws.Shapes.AddFormControl xlScrollBar, Left:=100, Top:=100, Width:=100, Height:=20
    ws.Shapes("ScrollBar").ControlFormat.Min = 1
    ws.Shapes("ScrollBar").ControlFormat.Max = 100
    ws.Shapes("ScrollBar").ControlFormat.Value = 1
  
    ' Loop through scrollbar values and capture screenshots
    For i = 1 To 100
        ws.Shapes("ScrollBar").ControlFormat.Value = i
        DoEvents ' Allow screen to update
      
        ' Capture screenshot
        ws.ChartObjects("Chart 1").Chart.Export "C:\Temp\Chart_" & i & ".jpg", "JPG"
    Next i
  
    ' Convert screenshots to video (You may need additional software or libraries for this step)
    ' Your code to combine screenshots into a video
  
    ' Cleanup - delete screenshots
    For i = 1 To 100
        Kill "C:\Temp\Chart_" & i & ".jpg"
    Next i
End Sub

Sub AnimateChart()
    ' Your code to animate the chart by incrementing the scrollbar value
    ' Example: ws.Shapes("ScrollBar").ControlFormat.Value = ws.Shapes("ScrollBar").ControlFormat.Value + 1
End Sub
Click to expand...
Hi,

automatic scrolling works, but the chart does not change even though the data is being scrolled through. Most importantly, it does not save any of the charts as a jpg. Can Anyone help?

Thanks!

Regards,
CK
 

chiukee​

Your Can Anyone help?
Did You test 'my file'?
You skipped to answer ... why do You need those jpgs ... instead of using Excel-file.
I added some code that it should save jpgs in PDF-folder.
... I cannot test it.
 

Attachments

  • test save charts as jpeg.xlsm
    54.1 KB · Views: 0
vletm said:

chiukee​

Your Can Anyone help?
Did You test 'my file'?
You skipped to answer ... why do You need those jpgs ... instead of using Excel-file.
I added some code that it should save jpgs in PDF-folder.
... I cannot test it.
Click to expand...
Hi vletm, yes your file works, but I need the jpgs so that I can make a film out of them. Showing the changing curve with respect to time. I will try your new stuff tonight. Thanks for helping!
 
vletm said:

chiukee​

Why You won't record (with any other program) selected range of sheet?
... instead of 100 jpg-files.
Click to expand...
Hi vletm, you are correct about this. But at work I do not have the video recording function of Windows 11 and all other useful apps are banned. Maybe, I can do this at home. Thanks!
 
You must log in or register to reply here.
Back
Top