Variable workbook declaration

[hammy77]

Hello
I have a workbook that contains data and a VBA to do some tidying up and formatting. I need to be able to open up 2 other workbooks to do a vlookup on, the problem I have is the 2 workbooks change name everyday. How do I declare these other 2 in my main workbook.

Any suggestions would be grateful

 

[jkpieterse]

What logic is there to those 2 workbooks? e.g. a filename containing today's date, the most recent file in folder x, ...

 

[hammy77]

The file names are xxx DD.mm.yy and stored in SharePoint

 

[jkpieterse]

Record a macro while opening one of those workbooks. The recorder will give you something like this:

    Workbooks.Open Filename:= _
        "https://SomePathToYourSharepoint/Book1 26.11.2019.xlsx"

Modify that to:

Workbooks.Open Filename:= _
"https://SomePathToYourSharepoint/Book1 " & Format(Date, "dd.mm.yyyy") & ".xlsx"

Repeat for the other one, making a similar change to the recorded code.

 

[hammy77]

Thank you, That sounds straight forward. This may be a silly question, I have done this but it looks for today's date, how can I do this for other dates. And how would I be able to reference these with a workbook.activate. I apologise if I don't understand

 

[jkpieterse]

So how would you know which date to use?
To later refer to an opened workbook in your code:

Dim Wb1 as Workbook
Set Wb1 = Workbooks.Open("Filenamegoeshere")
'Other code
'Now to address the new workbook, you use Wb1 and to address the workbook containing the code you use "ThisWorkbook".
'For instance, this copies A1:A10 from sheet Sheet1 to that same sheet in the workbook which contains the code
Wb1.Worksheets("Sheet1").Range("A1:A10").Copy
ThisWorkbook.Worksheets("Sheet1").Range("A1").PasteSpecial xlPasteValues