Possible? Copy/paste to&from moving sources/destinations

[LiAD]

Hi,

I have a list of data contained in a series of files stored on a network.
There is one file for every month, ( file name Jan, feb etc). Each file has
31 sheets (sheet names 1,2,3 etc) to record certain events every day of the
month. Some days may be empty and have nothing written.

There is then another file which I would like to fill automatically from
this series of files per month. This new file, called Summary in which i
have 5 sheets (a,b,c,d,e). I would like to find a way of automatically
copying data
from - Jan/1 cells d12, e12 & m12
to - Summary/a cells f2, g2 & d2 (row 2 to is for 1 Jan)

The next day, Jan 2 i need to take the data from the same file (if its the
same month) but a different sheet, Jan/2 cells d12, e12 & m12
and copy to summary/a cells f3, g3 & d3 (row 3 is for 2 Jan). Alternatively
if its the 31 Jan it copies from Jan/31, the next day it needs to find a
different file and sheet, Feb/1.

So basically I need a way of searching through a network by date to take
data from a certain folder and sheet to copy into a corresponding row by date
in another folder. The only static items are the cells to copy from,
(source file and sheet change) and the file name and sheet to paste to (cells
to paste to increase by one per day.

I have looked at Ron de Bruins site but come up blank so far.

Is this possible?

Thanks
LiAD

 

[Dave Peterson]

One of Ron's suggestion is to build formulas that return the values from closed
workbooks:
http://www.rondebruin.nl/summary.htm

I think you tried to simplify your description, but I think that this will do
what you describe--maybe not what you want. (Why do the worksheets b, c, d, e
in the summary workbook make a difference? You're only putting data in
worksheet a?)



Option Explicit
Sub testme01()

Dim SummWks As Worksheet
Dim myPath As String
Dim myFileName As String
Dim mySheetName As String
Dim myExtension As String
Dim myInputCols As Variant
Dim myOutputCols As Variant
Dim myFormula As String
Dim myDate As Date
Dim oRow As Long
Dim iRow As Long
Dim iCtr As Long

myInputCols = Array("F", "G", "D")
myOutputCols = Array("D", "E", "M")
If UBound(myInputCols) <> UBound(myOutputCols) Then
MsgBox "Design error!"
Exit Sub
End If
iRow = 2 'always pick up data from row 2

myDate = Application.InputBox(Prompt:="Enter date to retrieve", Type:=1)

'some minor date checking
If Year(myDate) < 2009 _
Or Year(myDate) > 2011 Then
Exit Sub
End If

oRow = myDate - DateSerial(Year(myDate) - 1, 12, 31) + 1

Set SummWks = ThisWorkbook.Worksheets("a")

myExtension = ".xls"

myPath = "C:\my documents\excel\"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

myFileName = Format(myDate, "MMM")
mySheetName = Day(myDate)

'trying for a formula like:
'='C:\My Documents\excel\[book2.xls]Sheet1'!A1
myFormula = "='" & myPath & "[" & myFileName & myExtension _
& "]" & mySheetName & "'!"

For iCtr = LBound(myInputCols) To UBound(myInputCols)
SummWks.Cells(oRow, myOutputCols(iCtr)).Formula _
= myFormula & myInputCols(iCtr) & iRow
Next iCtr

End Sub


All it's really doing is building that formula for each of the cells.

Maybe it'll get you closer.

 

[LiAD]

Sorry pasted in wrong place