Using Timer to Automate Getting Data from Closed Workbook

In the last two videos we learnt how to get data from a closed workbook. Today we learn how to start Excel automatically when you start your computer, how to open a specific Excel workbook automatically and use timer to automate getting data from closed workbook at specific intervals.

Before you study the VBA code watch the Excel training video:

 

Watch this video on YouTube.

 

Option Explicit

Private Function GetValueFromClosedWorkbook(path, file, sheet, ref)

Dim arg As String

‘ Let’s check whether the file exists
If Right(path, 1) <> “\” Then path = path & “\”
If Dir(path & file) = “” Then
GetValueFromClosedWorkbook = “File Not Found”
Exit Function
End If

‘ We create the argument
arg = “‘” & path & “[” & file & “]” & sheet & “‘!” & _
Range(ref).Range(“A1”).Address(, , xlR1C1)

‘MsgBox arg
‘ Now we execute an XLM macro
‘All references must be given as R1C1 strings.
GetValueFromClosedWorkbook = ExecuteExcel4Macro(arg)
End Function

Sub TestGetValueFromClosedWorkbook()
Dim p As String, f As String
Dim s As String, a As String

‘p = ThisWorkbook.path
p = “C:\Users\takyar\Desktop\”
f = “testworkbook.xlsx”
s = “Sheet1”
a = “C3”
‘ MsgBox GetValueFromClosedWorkbook(p, f, s, a)
ActiveSheet.Range(“G2”) = GetValueFromClosedWorkbook(p, f, s, a)
End Sub

Sub GetMultipleValuesFromClosedWorkbook()
Dim p As String, f As String
Dim s As String, a As String
Dim r As Long, c As Long

‘p = ThisWorkbook.path
p = “C:\Users\takyar\Desktop\”
f = “testworkbook.xlsx”
s = “Sheet1”
c = 10
Application.ScreenUpdating = False

For r = 1 To 12
a = Cells(r, c).Address
‘MsgBox a
Cells(r, 2) = GetValueFromClosedWorkbook(p, f, s, a)
Next r
End Sub

Sub TestGetValue2()
Dim p As String, f As String
Dim s As String, a As String
Dim r As Long, c As Long

p = ThisWorkbook.path
f = “testworkbook.xlsx”
s = “Sheet1”
Application.ScreenUpdating = False
For r = 1 To 12
For c = 1 To 10
a = Cells(r, c).Address
Cells(r, c) = GetValueFromClosedWorkbook(p, f, s, a)
Next c
Next r
‘MsgBox a
‘MsgBox “The last column is ” & (c – 1)
‘MsgBox “The last row is ” & (r – 1)
End Sub

Private Sub Workbook_Open()
‘Application.OnTime TimeValue(“9:40:00”), “GetMultipleValuesFromClosedWorkbook”
‘Application.OnTime TimeValue(“10:40:00”), “GetMultipleValuesFromClosedWorkbook”
‘Application.OnTime TimeValue(“11:40:00”), “GetMultipleValuesFromClosedWorkbook”
‘Application.OnTime TimeValue(“12:40:00”), “GetMultipleValuesFromClosedWorkbook”
‘Application.OnTime TimeValue(“13:40:00”), “GetMultipleValuesFromClosedWorkbook”
‘Application.OnTime TimeValue(“14:40:00”), “GetMultipleValuesFromClosedWorkbook”
‘Application.OnTime TimeValue(“15:40:00”), “GetMultipleValuesFromClosedWorkbook”
‘Application.OnTime TimeValue(“16:40:00”), “GetMultipleValuesFromClosedWorkbook”
‘Application.OnTime TimeValue(“17:45:00”), “GetMultipleValuesFromClosedWorkbook”
Application.OnTime TimeValue(“18:20:00”), “GetMultipleValuesFromClosedWorkbook”
‘Application.OnTime TimeValue(“19:40:00”), “GetMultipleValuesFromClosedWorkbook”

End Sub

Image showing where you should and paste the ‘excel.exe’ file to start Excel automatically when your Windows computer starts:

Further reading:

Application.Ontime Method

Customize how Excel starts