Get Data from workbook with dynamic name

[seldem]

Hello,

I am trying to create a dashboard worksheet collecting data from 2 types of closed workbooks:
1. Getting data from constant name which is not changing. Since the range is always same, It is easy, I can get to the selected cell on the dashboard worksheet with a linked formula connected to data workbook
2. Getting data from a workbook with dynamic name changing based on the date (such as 'C:\Documents\Report 23 12 2021.xlsx' or 'C:\Documents\Report 24 12 2021.xlsx' etc. The cell address is always same on these workbooks. Let's say A2:A2. But the file names are changing with "Report DD MM YYYY" format. I think for this process I have use VBA.

Could you help me on 2nd option which is getting data from workbook changing named dynamicly.

Best Regards,

Selim Demirpençe

 

[Joe4]

Welcome to the Board!

Don't know if you are still looking for an answer, but how about something like this?

Sub BuildReference()
    
    Dim dte As String

'   Get current date in DD MM YYYY format
    dte = Format(Date, "dd mm yyyy")

'   Place direct reference in cell A2
    Range("A2").Formula = "'C:\Documents\Report " & dte & ".xlsx]Sheet1'!$A$2"
    
End Sub

 

[seldem]

Welcome to the Board!

Don't know if you are still looking for an answer, but how about something like this?

Sub BuildReference()
   
    Dim dte As String

'   Get current date in DD MM YYYY format
    dte = Format(Date, "dd mm yyyy")

'   Place direct reference in cell A2
    Range("A2").Formula = "'C:\Documents\Report " & dte & ".xlsx]Sheet1'!$A$2"
   
End Sub

Hello Joe,
Sorry for my late reply. Thanks for your suggestion. I will try and let you know.

Best Regards,

Selim

 

[seldem]

Dear Joe,
I've tried your suggestion and it worked -with a little touch-. I add a '=' before direct reference in cell A2. Otherwise it gives just the address of the reference.
The is the final version of the code. I put here in order to help somebody who may need for a similar case. (I marked in bold red the little corrections)

VBA Code:
Sub BuildReference()

Dim dte As String

' Get current date in DD MM YYYY format
dte = Format(Date, "dd mm yyyy")

' Place direct reference in cell A2
Range("A2").Formula = "='C:\Documents\[Report " & dte & ".xlsx]Sheet1'!$A$2"

End Sub


Thanks Again,

Best Regards,

Selim

 

[Joe4]

Sorry about the typo, and forgetting the equals sign.
And yes, if the file name contains spaces, it will need to be enclosed in square brackets.

Glad you got it working.