View Full Version : Solved: create a list from other files
AleemAM123
10-13-2008, 05:45 AM
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
10-13-2008, 06:15 AM
You seem to be just setting a reference to these new workbooks, so why do you tink you need to open them?
AleemAM123
10-13-2008, 06:17 AM
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
10-13-2008, 06:22 AM
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
10-13-2008, 08:19 AM
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
10-13-2008, 08:35 AM
getting stuck at
.Range("G20") = "=" & FilePath & "[" & FileName & "]Sheet1!D14"
Bob Phillips
10-13-2008, 08:50 AM
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
10-13-2008, 11:31 AM
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
10-13-2008, 11:51 AM
And it will be heaps faster without having to open all of those files.
AleemAM123
10-13-2008, 12:59 PM
ah :thumb , thank you very much.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.