Solved: create a list from other files

[AleemAM123]

I'm trying to create a spreadsheet that will retrieve values from a series of spreadsheets on another pc. the names of the spreadsheets on the other pc have the format Conv_2008_09_03_DCS.xls and the date is incremented. I want to be able to put in a start date and an end date, generate the file names, retrieve the value from a cell (let's say d14 from sheet1 of Conv_2008_09_xx_DCS where xx is the date being incremented) from all of the files from the start to the end date and list them in the spreadsheet retrieve.xls.

i only just started to try writing vb code, got hold of a book and am playing with the result as i move to each step but i got stuck with the do until loop.

I suspect the problem is i can't add 1 to the the date: thedate = thedate + 1

[Bob Phillips]

You seem to be just setting a reference to these new workbooks, so why do you tink you need to open them?

[AleemAM123]

i thought that i needed to open them to retrieve the value, I don't need to open them?


p.s. i only have a very very basic understanding of visual basic at this point

[Bob Phillips]

If you were retrieving the value, you would, but to just create a formula, no need.

Not tested but this should work



Sub retrieve()
Dim FileName As String
Dim theDate As Date
Dim i As Long
Dim monthlyDir As String, yearlyDir As Long
Dim startDate As Date
Dim endDate As Date

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With Worksheets("Sheet2")

'Get the start and end dates
startDate = .Range("B5").Value
endDate = .Range("B6").Value

theDate = startDate
Do Until theDate = endDate

'Build filename and path
yearlyDir = Year(theDate)
montlydir = Format(theDate, "mmm")
FileName = "Conv_" & Format(theDate, "YYYY_MM_DD") & "_DCS.xls"
FileName2 = "\\krypton\Ops_Daily_Rpt\" & yearlyDir & "\" & monthlyDir & "\" & FileName

'open file to retrieve data
.Range("G20") = "=[" & FileName2 & "]Sheet1!D14"

theDate = theDate + 1
Loop
End With

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

[AleemAM123]

small problem, the link created in cell g20 is coming out as :='[\\krypton\Ops_Daily_Rpt\2008\Sep2008\[Conv_2008_09_09_DCS.xls]Sheet1]Conv_2008_09_09_DCS.xls]Sheet1'!D14

how can i fix that?




Sub retrieve2()
Dim FileName As String
Dim theDate As Date
Dim i As Long
Dim monthlyDir As String, yearlyDir As Long
Dim startDate As Date
Dim endDate As Date
Dim FilePath As String

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With Worksheets("Sheet2")

'Get the start and end dates
startDate = .Range("B5").Value
endDate = .Range("B6").Value

theDate = startDate
Do Until theDate = endDate

'Build filename and path
yearlyDir = Year(theDate)
monthlyDir = Format(theDate, "mmm")
FileName = "Conv_" & Format(theDate, "YYYY_MM_DD") & "_DCS.xls"
FilePath = "\\krypton\Ops_Daily_Rpt\ (file://\\krypton\Ops_Daily_Rpt\)" & yearlyDir & "\" & monthlyDir & yearlyDir & "\"

FileName2 = FilePath & FileName

'open file to retrieve data
'.Range("G20") = "=[" & FileName2 & "]Sheet1!D14"


.Range("G20") = "=" & FilePath & "[" & FileName & "]Sheet1!D14"


theDate = theDate + 1
Loop
End With

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

[AleemAM123]

getting stuck at

.Range("G20") = "=" & FilePath & "[" & FileName & "]Sheet1!D14"

[Bob Phillips]

Sub retrieve()
Dim FileName As String
Dim FileName2 As String
Dim theDate As Date
Dim NextRow As Long
Dim i As Long
Dim monthlyDir As String, yearlyDir As Long
Dim startDate As Date
Dim endDate As Date

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With Worksheets("Sheet2")

'Get the start and end dates
startDate = .Range("B5").Value
endDate = .Range("B6").Value

theDate = startDate
Do Until theDate > endDate

'Build filename and path
yearlyDir = Year(theDate)
monthlyDir = Format(theDate, "mmm")
FileName = "Conv_" & Format(theDate, "YYYY_MM_DD") & "_DCS.xls"
FileName2 = "'\\krypton\Ops_Daily_Rpt\" & yearlyDir & "\" & monthlyDir & "\[" & FileName & "]"

'open file to retrieve data
.Range("G20").Offset(NextRow, 0).Formula = "=" & FileName2 & "Sheet1'!D14"
NextRow = NextRow + 1

theDate = theDate + 1
Loop
End With

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

[AleemAM123]

that is exactly what I was trying to do, that "offset" thing would have never occured to me because I have never seen it before.

[Bob Phillips]

And it will be heaps faster without having to open all of those files.

[AleemAM123]

ah :thumb , thank you very much.