Automating HMI data transfer between data logging web server to excel spreadsheet

[wmcdonald]

I'm an engineer at a manufacturing plant who's trying to automate one of our more troublesome processes. We use an HMI system to monitor all industrial process data and export the desired data tags to a logging directory. This web server directory contains a maximum of 30 .csv files, which is constantly syncing with our HMI to store data every 60s - a new date stamped file is generated every other day. My boss created an excel macro worksheet to transfer this data from .csv to an excel tab and graph that data, however this is a manual process... the directory file shortcut is copied from the web browser, the user pastes this .csv link into the excel cell, and the macro will establish an updating connection between the excel worksheet and the new directory file. The problem is that we engineers tend to be sidetracked by other activities, occasionally forgetting to make the trip over to the computer every other day to copy/paste this new link into excel.

I'm looking for a macro that can automate this process...

1. Be able to scan and sync with the directory every 120s to pull the current .csv file and send it to the excel spreadsheet ("Imported Coating Data" Tab)
2. Take the contents of the .csv file and input them into the correct cells (Columns A2:AB2 -> Fill the rows with available time data)
3. Be able to identify and establish a connection with the new date stamped file generated every other day:
- 19020300.csv
- 19020100.csv

I have little to no experience with excel macros unfortunately. If any of you can point me in the right direction I would be very grateful.

Thank you,
Warren