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 task, but I want to be able to have the import automatically run on a schedule.   

excel
Macros and VBA

1 Reply

  • 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