Retrieve values from workbooks saved in another drive

[deduwa]

Hello,

I get 4 Excel files at 3am each morning (Mon-Fri and not weekend) saved in a drive through SFTP. If today is the 2nd of Feb for example, the latest saved version will always be as of previous work day so 1st of Feb in this example.

I have a Master workbook that I want to extract certain data from each of the 4 overnight files. The daily overnight files have a constant filename except that the date tag attached to them will change each day and will be as of yesterdays close date.

File1. Prices_mmddyyyy.xls
File2. Returns_mmddyyyy.xlsx
File3. Performance_mmddyyyy.xlsx
File4. Stats_mmddyyyy.xlsx

I need a code that will go to the folder where these overnight files are saved and in the most recently received/saved version of each of the 4 files do the following in the Master file;

1a. In cell H18 of active worksheet of Master file, open up the latest File1 and in the USD worksheet, go to the very last row and extract the figure from column C
1b. In cell I18 of active worksheet of Master file, open up the latest File1 and in the USD worksheet, go to the very last row and extract the figure from column D

2a. In cell H19 of active worksheet of Master file, open up the latest File2 and in the GBP worksheet, go to the very last row and extract the figure from column C
2b. In cell I19 of active worksheet of Master file, open up the latest File2 and in the GBP worksheet, go to the very last row and extract the figure from column D

3a. In cell H20 of active worksheet of Master file, open up the latest File3 and in the EUR worksheet, go to the very last row and extract the figure from column C
3b. In cell I20 of active worksheet of Master file, open up the latest File3 and in the EUR worksheet, go to the very last row and extract the figure from column D

4a. In cell H21 of active worksheet of Master file, open up the latest File4 and in the AUD worksheet, go to the very last column and extract the figure from row 8
4b. In cell I21 of active worksheet of Master file, open up the latest File4 and in the AUD worksheet, go to the very last column and extract the figure from row 11

Finally (if the code needs to open these overnight files first), close the overnight files without saving.

This process of extracting the above 8 datapoints into the Master file will be repeated each day so a code will be a huge time save.

Thanks in advance.

Dedu

 

[awoohaw]

Have you looked into POWER QUERY? Power query easily grabs workbooks (stored in a defined location, and even with the a template type name as you describe).

Youtube has many great lessons on power query from reputable channels like Mr. Excel, ExcelIsFun, Leila Gharani, and MyOnlineTrainingHub.
This one, by MyOnlineTrainingHub, I think will show you how to do what you need could do:

Additionally, Mr. Excel Bulletin Board has a forum for Power Query separate from this one. Any questions you have about can be asked there.
But, look at this video, you'll be amazed.

And here is a complete playlist by ExcelIsFun:

https://youtube.com/playlist?list=PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK&si=KzpQotaGkna0msEp

 

[deduwa]

Thanks for the youtube link. It doesn't quite capture what I am looking for as I don't really need to combine files, instead just need to extract certain cells from them. Is there a solution using vba code instead?

 

[awoohaw]

I thought you had a master worksheet? Aren't you combining data from files?
How much of the tutorials did you review?

Even if you get 1 cell (to add to a list, or overwrite existing data) from different workbooks and bring it to your master, Power Query will do what you want.

 

[mumps]

Place this macro in the Master workbook. It assumes that the four files are saved in the same folder as your Master workbook. If they are saved in a different folder, you will have to change the folder path (in red) to suit your needs.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim desWS  As Worksheet, srcWB As Workbook, lRow As Long, lCol As Long
    Set desWS = ThisWorkbook.ActiveSheet
    Workbooks.Open (ThisWorkbook.Path & "\Prices_" & Format(Date - 1, "mmddyyyy" & "*.*"))
    With Sheets("USD")
        lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        desWS.Range("H18:I18") = Array(.Range("C" & lRow), .Range("D" & lRow))
        ActiveWorkbook.Close False
    End With
    Workbooks.Open (ThisWorkbook.Path & "\Returns_" & Format(Date - 1, "mmddyyyy" & "*.*"))
    With Sheets("GPB")
        lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        desWS.Range("H19:I19") = Array(.Range("C" & lRow), .Range("D" & lRow))
        ActiveWorkbook.Close False
    End With
    Workbooks.Open (ThisWorkbook.Path & "\Performance_" & Format(Date - 1, "mmddyyyy" & "*.*"))
    With Sheets("EUR")
        lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        desWS.Range("H20:I20") = Array(.Range("C" & lRow), .Range("D" & lRow))
        ActiveWorkbook.Close False
    End With
    Workbooks.Open (ThisWorkbook.Path & "\Stats_" & Format(Date - 1, "mmddyyyy" & "*.*"))
    With Sheets("AUD")
        lCol = ActiveSheet.Cells(8, Columns.Count).End(xlToLeft).Column
        desWS.Range("H21:I21") = Array(.Cells(8, lCol), .Cells(11, lCol))
        ActiveWorkbook.Close False
    End With
    Application.ScreenUpdating = True
End Sub

 

[deduwa]

Thanks mumps very helpful. Just for extra clarity the Master is in the G drive and the 4 overnight files are in the same folder in the E drive.

My apologies - forgot to also mention (and I think it adds an extra level of complexity) that when a new month arrives, a new folder is automatically created which houses files received in that month. So basically the file path will change at the turn of each month. The file path currently is the following (which will change to \2024-03\ in March)....

E:\Data\2024-02\Prices_02012024.xls
E:\Data\2024-02\Returns_02012024.xlsx
E:\Data\2024-02\Performance_02012024.xlsx
E:\Data\2024-02\Stats_02012024.xlsx

In B18:B21 of Master, I have a formula that gives the new file path to the correct tab each day (if this helps);

E:\Data\2024-02\[Prices_02012024.xls]USD
E:\Data\2024-02\[Returns_02012024.xlsx]GBP
E:\Data\2024-02\[Performance_02012024.xlsx]EUR
E:\Data\2024-02\[Stats_02012024.xlsx]AUD

Is this possible to do through code?

 

[mumps]

This version should automatically adjust the file path to take the current year and month into account as long as it is run during the month in question.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim desWS  As Worksheet, srcWB As Workbook, lRow As Long, lCol As Long
    Set desWS = ThisWorkbook.ActiveSheet
    Workbooks.Open ("E:\Data\" & Year(Date) & "-" & Format(Month(Date), "mm") & "\Prices_" & Format(Date - 1, "mmddyyyy" & "*.*"))
    With Sheets("USD")
        lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        desWS.Range("H18:I18") = Array(.Range("C" & lRow), .Range("D" & lRow))
        ActiveWorkbook.Close False
    End With
    Workbooks.Open ("E:\Data\" & Year(Date) & "-" & Format(Month(Date), "mm") & "\Returns_" & Format(Date - 1, "mmddyyyy" & "*.*"))
    With Sheets("GPB")
        lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        desWS.Range("H19:I19") = Array(.Range("C" & lRow), .Range("D" & lRow))
        ActiveWorkbook.Close False
    End With
    Workbooks.Open ("E:\Data\" & Year(Date) & "-" & Format(Month(Date), "mm") & "\Performance_" & Format(Date - 1, "mmddyyyy" & "*.*"))
    With Sheets("EUR")
        lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        desWS.Range("H20:I20") = Array(.Range("C" & lRow), .Range("D" & lRow))
        ActiveWorkbook.Close False
    End With
    Workbooks.Open ("E:\Data\" & Year(Date) & "-" & Format(Month(Date), "mm") & "\Stats_" & Format(Date - 1, "mmddyyyy" & "*.*"))
    With Sheets("AUD")
        lCol = ActiveSheet.Cells(8, Columns.Count).End(xlToLeft).Column
        desWS.Range("H21:I21") = Array(.Cells(8, lCol), .Cells(11, lCol))
        ActiveWorkbook.Close False
    End With
    Application.ScreenUpdating = True
End Sub

 

[deduwa]

Thanks mumps. When I run this code I get an error on;

Workbooks.Open ("E:\Data\" & Year(Date) & "-" & Format(Month(Date), "mm") & "\Prices_" & Format(Date - 1, "mmddyyyy" & "*.*"))

The file path looks at the correct file (yesterday) but in the Jan folder and not the Feb folder. Apologies - I am a quite new to this. What could be the reason?

Also, can the following code be amended such that for the E:\Data\2024-02\[Stats_02012024.xlsx]AUD file, it searches for the last available date in the current folder instead of searching for yesterdays date? Reason is that this file is not a daily file and is received weekly;

Workbooks.Open ("E:\Data\" & Year(Date) & "-" & Format(Month(Date), "mm") & "\Stats_" & Format(Date - 1, "mmddyyyy" & "*.*"))
With Sheets("AUD")
lCol = ActiveSheet.Cells(8, Columns.Count).End(xlToLeft).Column
desWS.Range("H21:I21") = Array(.Cells(8, lCol), .Cells(11, lCol))
ActiveWorkbook.Close False
End With

 

[mumps]

The file path looks at the correct file (yesterday) but in the Jan folder and not the Feb folder.

I'm not sure why that is happening. The code is taking the year and month from the current date. If you run the macro in February, the year will be 2024 and the month will be 02 so it should be looking in the February folder. Post the exact full path to your February folder.