How to refresh daily data with additional text included in date filename

[chachie]

Hello,

We have a number of CSV files which are created daily by our modelling software. I would like to import the most recent csv data into their respective excel sheets. Each csv file is saved in a single folder generated by the model. An example of the saved csv file are saved like this:

C:\HGSensembleForecast\Module\postprocess\20190829UTC12\W0000097_HGA_A_HGS.csv

Note the folder with the date 2019 08 29 includes the text UTC12. Is there a way for excel to ignore the UTC12 text and copy the data from the csv file saved in the folder with the most recent date? The only thing that changes in the location path is the date stamp. The csv filename is always the same.

Thanks

 

[DanteAmor]

Try this

Sub refresh_daily_data()
  Dim wPath As String, wDate As String, wFile As String, wb As Workbook
  
  wPath = "C:\HGSensembleForecast\Module\postprocess\"
  wPath = "C:\trabajo\books\"
  wDate = Format(Date, "yyyymmdd") & "UTC12\"
  wFile = "W0000097_HGA_A_HGS.csv"
  
  Set wb = Workbooks.Open(wPath & wDate & wFile)
  wb.Sheets(1).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
  wb.Close
End Sub