Saving an exported chart as the current date file name

[weiser386]

I'm trying to export a screenshot with my macro and I'd like it to save as a date and time format...I think I'm some what close but I need help.

Thank you in advance

Sub CopyRangeToGIF()
' save a range from Excel as a picture
Dim rng As Excel.Range
Dim cht As Excel.ChartObject
Dim FName As String

FName = "S:\folder\folder\ Screenshots\" & _
        Format(Date, "ddmmmyyyy") & ".xlsm"

Const strPath As String = "S:\folder\folder\ Screenshots\"
 
Application.ScreenUpdating = False
 
Set rng = Worksheets("WorkSheet1").Range("A1:K21")
 
rng.CopyPicture xlScreen, xlPicture
 
Set cht = ActiveSheet.ChartObjects.Add(0, 0, rng.Width + 10, rng.Height + 10)
 
cht.Chart.Paste
 
cht.Chart.Export strPath & FName
 
cht.Delete
 
ExitProc:
Application.ScreenUpdating = True
Set cht = Nothing
Set rng = Nothing
End Sub

 

[Stubble Kid]

You've not explained what the problem or error is explicitly. But here's some observations:

The file name for the exported screenshot should presumably have a picture extension e.g. ".jpg" instead of ".xlsm".

If you want to incorporate the time element in the export file name, you can use Format(Now,"ddmmyyyyhhmmss")

In the cht.Chart.Export line you've concatenated the strPath and FName, but Fname already includes the full path description so this line would throw an error.

 

[weiser386]

Thank you for your reply, I have fixed the issue I was originally looking at (timestamps as file save name) but now my question is how do i get this screenshot macro to run at 6:59 am and 6:59 pm everyday on a loop...the file is open 95% of the day and night it only gets closed when the computer restarts around 3am but i have the excel sheet in the "start up" folder so it automatically opens after restart.

Here is my code that's located in "THISWORKBOOK MODULE"

Private Sub Workbook_Open()

Application.OnTime TimeValue("018:59:15"), "KPI_Screenshot"
Application.OnTime TimeValue("06:59:15"), "KPI_Screenshot"

Call MyMacro

End Sub

Here is the code that is located in a regular module:

Private dTime As Date

Sub MyMacro()

If ActiveWorkbook.Name = "test sheet.xlsm" Then

  dTime = Now + TimeValue("00:00:03")
  Application.OnTime dTime, "MyMacro"
  
  Calculate

  Application.DisplayFullScreen = True

End If

End Sub

And here is the code for my screenshot macro that is also located in a regular module:

Sub KPI_Screenshot()

' save a range from Excel as a picture
Dim rng As Excel.Range
Dim cht As Excel.ChartObject
Dim strpath As String
Dim dtDate As Date
Dim Savetime As Integer
Savetime = Round(Timer / 3600, 0)

    Dim AMPM As String: AMPM = " AM"
    If Savetime >= 12 Then
        AMPM = " PM"
        If Savetime > 12 Then
            Savetime = Savetime - 12
        End If
    End If

strpath = "S:\Departments\Production\Nutek\Supervisors\KPI Screenshots\" & Format(Date, "m.dd.yy - ") & _
                    Savetime & AMPM & ".GIF"

Set rng = Worksheets("WIPES DAILY BRIEF").Range("A1:K21")
rng.CopyPicture xlScreen, xlPicture
Set cht = ActiveSheet.ChartObjects.Add(0, 0, rng.Width + 10, rng.Height + 10)
cht.Chart.Paste

cht.Chart.Export strpath

cht.Delete
 
ExitProc:
Application.ScreenUpdating = True
Set cht = Nothing
Set rng = Nothing
End Sub

 

[Jon Peltier]

I think your OnTime needs a date-time, not just a time.

Try:

Application.OnTime Int(Now) + TimeValue("018:59:15"), "KPI_Screenshot"
Application.OnTime Int(Now) + TimeValue("06:59:15"), "KPI_Screenshot"