Forum Discussion

VailA45987's avatar
VailA45987
Copper Contributor
Oct 28, 2021

Automated Excel .csv import on a schedule?

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 tas...
excel
Macros and VBA
HansVogelaar's avatar
HansVogelaar
MVP
Oct 29, 2021

VailA45987 

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

Resources