Open a workbook with a reference to a date in a cell

[Mange]

Dear all

I tried to create a code that will open a workbook. I don't want to use the exact file location, but instead, I want to work with a reference to a date in a particular cell.

So e.g. I need to open a file called 'Test_20160630_V2'. Because I will need to do this every month, I don't want to copy/paste the exact file location every month, but I want to include a reference to the particular date, in this case 30/06/2016.

Sub Test()

Dim Test As Worksheet
Dim wbB As Workbook
Dim YYYY As String
Dim MM As String
Dim DD As String
Dim DatReport As Range

Set DatReport = ActiveWorkbook.Worksheets("Test").Range("B2")

YYYY = Format(DatReport, "yyyy")
MM = Format(DatReport, "mm")
DD = Format(DatReport, "dd")

Set wbB = Workbooks.Open("F:\TestMcTest\Test_report\YYYY\YYYYMMDD\2_TestieTest\Test_YYYYMMDD_V2.xls")

End Sub

So YYYY returns 2016, MM 06 and DD 30.. Do I need to convert it to string, because Excel doesn't find the file.

 

[steve the fish]

Yeah. For example:

YYYYMMDD = YYYY & MM & DD
myFile = "F:\TestMcTest\Test_report\" & YYYY & "\" & YYYYMMDD & "\2_TestieTest\Test_" & YYYYMMDD & "_V2.xls"
Set wbB = Workbooks.Open(myFile)

 

[Mange]

I get the following error code: "Object variable or With block variable not set".

 

[steve the fish]

Which line? Paste your code now.

 

[Mange]

At the MyFile line.

Sub Test()

Dim Test As Worksheet
Dim wbB As Workbook
Dim YYYY As String
Dim MM As String
Dim DD As String
Dim YYYYMMDD As String
Dim DatReport As Range
Dim myFile As Workbook

Set DatReport = ActiveWorkbook.Worksheets("Test").Range("B2")

YYYY = Format(DatReport, "yyyy")
MM = Format(DatReport, "mm")
DD = Format(DatReport, "dd")

YYYYMMDD = YYYY & MM & DD
 
myFile = "F:\Risk_controlling\CALM_report\" & YYYY & "\" & YYYYMMDD & "\2_calculation\Switcher_" & YYYYMMDD & "_v0.1.xls"
Set wbB = Workbooks.Open(myFile)

End Sub

 

[steve the fish]

myFile isn't a workbook. Its a string. The workbook is wbB. Change your dim statements.

 

[Mange]

Hah indeed.. It works now, thanks!

Could you just explain why you need to code it like this: " & YYYYMMDD & " ?

 

[steve the fish]

Its because that is a variable. If you kept it in the string excel would just look for the folder called literally YYYYDDMM rather than the string represented by the variable. So to excel it would be searching for this folder:

"F:\Risk_controlling\CALM_report\YYYY\YYYYMMDD"

when you actually wanted:

"F:\Risk_controlling\CALM_report\2016\20160630"

 

[Mange]

Ok, I get it. Thanks for your help!