Oct 28 2021 04:32 PM
Does Excel have the ability to automatically import a .csv file on a schedule?
I have a .csv file import that needs to run every morning. I know that there is a way to automate the import task, but I want to be able to have the import automatically run on a schedule.
Oct 29 2021 03:02 AM
One option is to keep the workbook open 24/7.
In a standard module:
Public dtmNext As Date
Sub ImportCSV()
' Code to import CSV goes here
' ...
' Next run at 9 AM tomorrow
dtmNext = Date + 1 + TimeSerial(9, 0, 0)
Application.OnTime EarliestTime:=dtmNext, Procedure:="ImportCSV"
End Sub
In the ThisWorkbook module:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
' Cancel scheduled run
Application.OnTime EarliestTime:=dtmNext, Procedure:="ImportCSV", Schedule:=False
End Sub
Private Sub Workbook_Open()
' Next run tomorrow at 9 AM
dtmNext = Date + 1 + TimeSerial(9, 0, 0)
' Or if it's not 9 AM yet, today
If Hour(Now) < 9 Then
dtmNext = dtmNext - 1
End If
Application.OnTime EarliestTime:=dtmNext, Procedure:="ImportCSV"
End Sub