Macro to automatically do equation and plot the graph with correct formatting and trendlin

Hello All,

I am relatively new to writing macros. I have several excel files that have a time, displacement, voltage and current column. I need to calculate a velocity column. "Velocity" in cell E1. "mm/s" in cell E2. And "(B4-B3)/(A4-A3) in cell E3 and it needs to go all the way down for however many rows the data set has. Each file has a different amount of rows of data since these are time based data sets. I then need to graph the data titled "Velocity" with velocity on the y-axis and displacement on the x-axis. Then, current on a secondary y-axis and displacement on the x-axis. Then I need to add a trendline to the velocity graph with a moving average with a period of 5. Axis titles, legend, and graph title need to be added. The graph title should be "Job 8305 (NextLine) Motor Bridge Ramp Test (NextLine) (Name of the excel file)". Then, the velocity data series can be hidden from the graph and the legend so you only see the solid trendline.

I started writing the macro in portions by using the macro recorder but was not able to successfully add the trendline.

I attached 2 files. 1 File is a raw file, the next is a processed one which is how we want it to look when it is finished. Could someone help me to achieve this goal?

If the trendline part is too difficult to add, it can be left out if need be.

Thank you so much!!

You may use such a code in your already processed file (i saved it with a meaningful name :-) and attached to the post):

Const FOLDER_PATH = "C:\Users\Kaper\Tmp_Excelforum\raw_results\"  'REMEMBER END BACKSLASH


Sub Create_files_from_template()
   '=============================================
   'Process all Excel files in specified folder
   '=============================================
   Dim sFile As String           'file to process
   Dim wsTarget As Worksheet
   Dim wbSource As Workbook
   Dim wsSource As Worksheet
   Dim lastrow As Long
   
   
   'check the folder exists
   If Not FileFolderExists(FOLDER_PATH) Then
      MsgBox "Specified folder does not exist, exiting!"
      Exit Sub
   End If
   
   'reset application settings in event of error
   On Error GoTo errHandler
   Application.ScreenUpdating = False
   Application.DisplayAlerts = False
   
   'set up the target worksheet
   Set wsTarget = Sheets("Data1")
   
   'loop through the Excel files in the folder
   sFile = Dir(FOLDER_PATH & "*.xls*")
   Do Until sFile = ""
      
      'open the source file and set the source worksheet
      Set wbSource = Workbooks.Open(FOLDER_PATH & sFile)
      Set wsSource = wbSource.Worksheets("Data1")
      
      'import the data
      With wsSource
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
      End With
      With wsTarget
         .Range("A4:E" & lastrow).ClearContents
         .Range("A3:D" & lastrow).Value = wsSource.Range("A3:D" & lastrow).Value
         .Range("E3").AutoFill .Range("E3:E" & lastrow)
      End With
      
      'close the source workbook
      wbSource.Close SaveChanges:=False
      'modify existing graph
      ActiveSheet.ChartObjects(1).Activate
      ActiveChart.FullSeriesCollection(1).XValues = "=Data1!$B$3:$B$" & lastrow
      ActiveChart.FullSeriesCollection(1).Values = "=Data1!$E$3:$E$" & lastrow
      ActiveChart.FullSeriesCollection(2).XValues = "=Data1!$B$3:$B$" & lastrow
      ActiveChart.FullSeriesCollection(2).Values = "=Data1!$D$3:$D$" & lastrow
      ActiveChart.ChartTitle.Text = "Job 8305" & Chr(13) & "Motor Bridge Ramp Test" _
        & Chr(13) & Left(sFile, InStr(sFile, ".xls") - 1)
      ThisWorkbook.SaveAs ThisWorkbook.Path & "\!_" & sFile, xlOpenXMLWorkbook
      sFile = Dir()
   Loop
MsgBox "Done the work. You shall close this workbook," & vbLf & "and check your source folder for result files"
errHandler:
   On Error Resume Next
   'no real error handling, only tidy up
   Application.ScreenUpdating = True
   Application.DisplayAlerts = True
   Set wsSource = Nothing
   Set wbSource = Nothing
   Set wsTarget = Nothing
End Sub

Private Function FileFolderExists(strPath As String) As Boolean
    If Not Dir(strPath, vbDirectory) = vbNullString Then FileFolderExists = True
End Function

Keep all the "raw results" in one folder, and this file outside of this folder (may be in another folder. all processed files will be stored in this location).
Of course use correct raw folder path in the beginning of the code

So it is not recreating the graph, but just uses the existing one and only load the new data and then adjust series and title accordingly.

PS. Don't worry about the shape of graph in master file. just make a copy of your raw data (may be some 3-4 files) to separate folder, this master file into another folder (where the resulsting files will be stored) and test the code with real data.
I've noticed that may be some formatting is missing, so after copying data you may add cells formatting.
instead of

      With wsTarget
         .Range("A4:E" & lastrow).ClearContents
         .Range("A3:D" & lastrow).Value = wsSource.Range("A3:D" & lastrow).Value
         .Range("E3").AutoFill .Range("E3:E" & lastrow)
      End With

write

     With wsTarget
         .Range("A4:E" & lastrow).ClearContents
         .Range("A3:D" & lastrow).Value = wsSource.Range("A3:D" & lastrow).Value
         .Range("E3").AutoFill .Range("E3:E" & lastrow)
         .Range("A3:E" & lastrow).NumberFormat = "General"
      End With