Solved: getting values from files with changing names

[AleemAM123]

Hi everyone i just know a little bit of visual basic so i need a lot of help.

i would like to have a spreadsheet Aleem.xls that pulls values from another spreadsheet that has a date in its name e.g. Aleem_2008_08_19_dcs.xls. there is a file for every date.

so i will have a cell (a1) in aleem.xls where i will specify a date, i've written some code that will generate the filename of the file to be opened using the date in a1 and open the file with the date in the name.

how do i get the cell value in aleem.xls to be set to the value in the opened file? i don't want to have a static filename in the code: "=[Aleem_2008_08_19_dcs.xls]Sheet1!


this is the code i have so far:

Sub dynamicopen()

Dim Rep_Name As Variant
Dim Today As String
Dim thedate As String
Dim myMonth, myYear, number, monthlyDir, yearlyDir As Variant
Dim FileName As String
Dim FileName2 As String
Dim oldName, newFile


thedate = Worksheets("Sheet1").Range("A1").Value


FileName = "Aleem_" & Format(thedate, "YYYY_MM_DD")
FileName2 = "Aleem_" & Format(thedate, "YYYY_MM_DD") & "_DCS" & ".xls"
Rep_Name = "C:\Documents and Settings\Aleem Mohammed\Desktop\" & FileName & "_DCS"

Workbooks.Open ("C:\Documents and Settings\Aleem Mohammed\Desktop\" & FileName & "_DCS" & ".xls")


Windows("Aleem.xls").Activate
Worksheets("Sheet1").Range("c8") = "=[Aleem_2008_08_19_dcs.xls]Sheet1!R1C1"

Worksheets("Sheet1").Range("c9") = "=[Aleem_2008_08_19_dcs.xls]Sheet1!R2C1"

Windows("Aleem_2008_08_19_dcs.xls").Activate
ActiveWindow.Close
End Sub

thanks in advance, if i'm not totally clear with my problem statement let me know, that happens sometimes :thumb

[AleemAM123]

other file

[mdmackillop]

Try adding single quotes

Worksheets("Sheet1").Range("c8") = "='[Aleem_2008_08_19_dcs.xls]Sheet1'!R1C1"
Worksheets("Sheet1").Range("c9") = "='[Aleem_2008_08_19_dcs.xls]Sheet1'!R2C1"

[AleemAM123]

i wanted to replace that text in red with something like "=[FileName2]Sheet1!R1C1" which should be a new filename when i change the date

[mdmackillop]

Worksheets("Sheet1").Range("c8") = "=[" & FileName2 & "]Sheet1!R1C1"

[AleemAM123]

ok, that works great. now how do i shut the file i opened:

Worksheets("Sheet1").Range("c9") = "=[" & FileName2 & "]Sheet1!R2C1"

Windows("& FileName2 &").Activate
ActiveWindow.Close
End Sub


the Windows("& FileName2 &").Activate line doesn't work

oh wiat i think i know how

[mdmackillop]

FileName2.Close False

[AleemAM123]

i tried:

Workbooks("C:\Documents and Settings\Aleem Mohammed\Desktop\" & FileName & "_DCS" & ".xls").Close SaveChanges:=False


End Sub

but that didn't work and your suggestion isn't working either

it say compile error: invalid qualifier

[mdmackillop]

Forgot it was a string


Workbooks(FileName2).Close False

[AleemAM123]

thx mdmackillop, works great