Scheduled Import a online csv file into Workbook
For a client I needed a solution for a scheduled import of a online csv file into a workbook. There are many ways to download files from the internet but it needed to be in excel as there were a whole lot of macro’s allready in place to transform and process the downloaded data. They had a machine running at all time ( not a server ) to be used.
So this is the code I came up with. It runs every day after it is started by running the StartSchedule Procedure
Option Explicit Public tTime As Date Sub StartSchedule() tTime = Now() Schedule End Sub Sub Schedule() tTime = tTime + TimeSerial(23, 59, 59) Application.OnTime tTime, "ImportCSV" End Sub Sub ImportCSV() Dim tmpSheet As Worksheet Set tmpSheet = ThisWorkbook.Sheets.Add(,,, "http://www.dummy.com/temp/file.csv") tmpSheet.Cells.EntireColumn.AutoFit tmpSheet.Name = tmpSheet.Name & " " & Format(Now(), "yyyy-mm-dd hh-mm-ss") Schedule End Sub