→ Oct 14 Monday 16:51 Δ16h → Oct 15 Tuesday 23:01 Δ15h → Oct 17 Thursday 03:00 Δ13h → Oct 18 Friday 04:57 Δ12h → Oct 19 Saturday 05:02 Δ12h → Oct 20 Sunday 03:23 Δ11h → Oct 21 Monday 00:07 Δ10h 19:21 Δ9h → Oct 22 Tuesday 13:11 Δ9h → Oct 23 Wednesday 05:45 Δ8h 21:06 Δ7h → Oct 24 Thursday 11:21 Δ7h → Oct 25 Friday 00:34 Δ6h 12:50 Δ6h → Oct 26 Saturday 00:13 Δ5h 10:46 Δ5h 20:33 Δ5h → Oct 27 Sunday 05:39 Δ4h 14:04 Δ4h 21:53 Δ4h → Oct 28 Monday 05:09 Δ3h 11:53 Δ3h 18:07 Δ3h 23:55 Δ3h → Oct 29 Tuesday 05:17 Δ3h
Here is another sample with a fully synced timescale containing two intervals with suspicious prefixes. VBA script The entire script, all subroutines and functions of the VBA is presented and commented below. Code Comments Option Explicit All variables must be declared Sub Oval3_Click() Schedule End Sub The sole user interface to activate or deactivate the background periodic script Sub shdata() ThisWorkbook.Activate ThisWorkbook.Sheets(1).Activate range("log_range").Cells(2, 3).Activate End Sub This subroutine activates the data sheet and in it, activates the most recent log record. For an unknown reason, CopyFromRecordset method of a range (see below in the code) generates an error when the active worksheet is not the one where the range belongs to. Sub shchart() ThisWorkbook.Activate ThisWorkbook.Sheets(2).Activate End Sub Activates the sheet. Sub datalock() ThisWorkbook.Activate ThisWorkbook.Sheets(1).Unprotect vba_out "protect worksheet" ThisWorkbook.Sheets(1).Protect password:="" ThisWorkbook.Sheets(2).Protect password:="" The worksheet is protected after the execution of the script. In the Excel file, only the input cells of the user are unlocked.
filename = chart_path & "\" & chart_name vba_out "exporting to " & chart_name If Then Worksheets(1).ChartObjects(1).Activate ActiveChart.Export filename Else Charts(1).Export filename End If Exporting the chart image into the PNG file. In our case we export the chart object represented by the chart sheet instead of exporting the chart located in the data worksheet. Select Case Int(Rnd * 3) + 1 Case 1 Set smtp_account = Case 2 Set smtp_account = Case 3 Set smtp_account = range("smtp_account3") End Select Set the SMTP account range to one of the three user defined SMTP accounts for transmission of email. We use three accounts in our example, a google.com account, a switzernet.com account, and a yahoo.com account. In case of a failure of transmission there is a greater chance of a successful retransmission with more than one account. Note: in the new version of this script (refer to the excel file available for downloading), the number of accounts is not fixed. Instead of using multiple named ranges we use a single range with multiple areas. User can define this single range with any given number of areas. Each area must contain seven rows with credentials of the SMTP account. vba_out "emailing..." If SMTP( _ filename, _ smtp_account) Then vba_out "email sent" Chart_Email = True Else vba_err "failed to email" Chart_Email = False End If End Function Parsing the SMTP account selection and the filename to the email transmission function described below. If the email transmission is unsuccessful we quit the function with the false return value. This permits the main job subroutine to attempt another retransmission of the email. Function SMTP(ByVal filename As String, ByVal smtp_account) As Boolean Dim iMsg As Object This function receives the filename with the SMTP account information area as arguments and returns true if it succeeds to email the file.