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

chachie

New Member
Joined
Nov 21, 2006
Messages
10
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,926
Office Version
2007
Platform
Windows
Try this

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,100,058
Messages
5,472,212
Members
406,809
Latest member
haf19

This Week's Hot Topics

Top