On 2002-08-26 09:58, dbrull wrote:
I will have 10 workbooks that on a monthly basis, will need to have data copied and put into a master workbook.
Is the data in the Same sheet and Range?
I read that you can get data from a closed workbook, but my workbooks will have passwords. Does this matter?
No as long as you know the password, are they
the same for each workbook
Also i need to be able to copy only one months data, not all. While the workbook is closed, can i run a macro in it to select only the data i want?
thanks
This is doable but...you will need to do a bit
of work......what have you got so far..
To get you thinking in the right direction
here is a routine that gets Values from a set range from ALL xlfiles in a certain Dir.
<pre/>
Sub GetValue_ViaFormula()
Dim sDir As String
Dim ShtCellLoc As String
Dim DRg As Range
Dim Files
Dim x As Double
'This is the Dir to search in
sDir = "C:Excelfiles"
'This is the Location/cell address
ShtCellLoc = "Sheet1'!$A$1"
Files = Dir(sDir & "*.xls")
'Clear area Column A to place data in
'Change this as required
Columns("A:A").Clear
'speed things up
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
x = 1
On Error GoTo FileError
Do While Len(Files) > 0
Cells(x, 1) = Files
Cells(x, 2) = "='" & sDir & "[" & Files & "]" & ShtCellLoc
x = x + 1
Files = Dir()
Loop
Application.Calculate
Set DRg = Range(Range("A1:B1"), Range("A1:B1").End(xlDown)) '.Copy
DRg.Copy
DRg.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Columns("A:B").Columns.EntireColumn.AutoFit
Application.CutCopyMode = False
Set DRg = Nothing
Application.Calculation = xlCalculationAutomatic
Application.Calculate
Application.ScreenUpdating = True
MsgBox "Done!"
Exit Sub
FileError:
MsgBox Err.Number & Chr(13) & _
Err.Description & Chr(13) _
, vbCritical + vbMsgBoxHelpButton, _
"File Error", _
Err.HelpFile, _
Err.HelpContext
End Sub
</pre>
