Get data from closed workbook

[JohnRyan]

I'm trying to get data from Cell A4 in a closed workbook named "Test1.xlsm"
When I use the name "Test1.xlsm" it gets the data
But when I put the file name "Test1.xlsm" into a string it doesn't return the data from Cell A4


Sub LoopThroughFiles_for_Total_Values()


Dim Date2 As String
Dim strFileName As String


strFileName = "Test1.xlsm"

'Date2 = "='F:\[strFileName]Sheet1'!a4" This line doesn't return the contents from the closed workbook "Test1.xlsm"
Date2 = "='F:\[Test1.xlsm]Sheet1'!a4" This line returns the contents form the closed workbook "Test1.xlsm"


Range("A1") = [Date2]


End Sub

 

[CalcSux78]

Try:

Date2 = "='F:\[" & strFileName & "]Sheet1'!a4"

 

[Domenic]

Try...

Date2 = "='F:\[" & strFileName & "]Sheet1'!a4"

Hope this helps!

 

[JohnRyan]

Try:

Date2 = "='F:\[" & strFileName & "]Sheet1'!a4"

Fantastic !

Thanks for your help - saved me hours of research