AleemAM123
08-22-2008, 04:37 PM
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
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