Automated Excel .csv import on a schedule?

New Contributor

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.   

1 Reply

@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