memory issues

I have a macro that opens sequentially 34 quite large spreadsheets and copies
data from up to 10 cells in each and pastes it to one spreadsheet. I am
using the
sourcedata.copy to destinationrange
this is causing terrible memory problems.
Does anyone have any ideas how i get around this. The destination
spreadsheet also has about 400 linked cells to 3 other workbooks.

I also have another issue in that the spreadsheets that are being opened to
copy data from have a macro that is fired on closing the spreadsheet that
produces a message box asking if the user wishes to update. The code on this
spreadsheet is protected how do i avoid having to cancel each message box to
enable the spreadsheet to close.

Any help will be much appreciated

Regards

Spike
--
with kind regards

Spike

You don't need to open a workbook to get data from it. Ron Debruin has code
that allows you to obtain data without opening the wb:

http://www.rondebruin.nl/ado.htm

The following is another example of how to get data from closed wbs. Assumed
is that the data from all source wbs is in column A starting at A1. Also
assumed is that you want to copy to the destination wb into column A starting
in cell A1 for the first source wb and then to column B for the second source
wb and so forth. Minimal testing:

Sub TransferData()
Dim c As Range, cc As Range
Dim ws As Worksheet
Dim P As String, FN As String
Dim FileNameArr As Variant
Dim i As Long, ii As Long, Pos As Long

On Error GoTo ExitProc
FileNameArr = Application.GetOpenFilename _
("Excel Files(*.xls), *.xls", MultiSelect:=True)
If VarType(FileNameArr) = vbBoolean Then Exit Sub
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
Set ws = ThisWorkbook.Sheets("Sheet1") 'Destination ws
Set c = ws.Range("A1") 'Top cell in destination range
Pos = InStrRev(FileNameArr(1), "\")
P = Left(FileNameArr(1), Pos - 1) 'Path of first source wb
For i = LBound(FileNameArr) To UBound(FileNameArr)
FN = Dir(FileNameArr(i)) 'Iterate through source wb names
ii = 1
Do
Set cc = c(ii, i)
cc.Formula = "='" & P & "\[" & FN & "]Sheet1'!" & c(ii,
1).Address & ""
cc.Value = cc.Value 'Transform formulas to values
ii = ii + 1
Loop Until cc.Value = 0
cc.ClearContents
Next i
ExitProc:
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub

Regards,
Greg

"Spike" wrote:

> I have a macro that opens sequentially 34 quite large spreadsheets and copies
> data from up to 10 cells in each and pastes it to one spreadsheet. I am
> using the
> sourcedata.copy to destinationrange
> this is causing terrible memory problems.
> Does anyone have any ideas how i get around this. The destination
> spreadsheet also has about 400 linked cells to 3 other workbooks.
>
> I also have another issue in that the spreadsheets that are being opened to
> copy data from have a macro that is fired on closing the spreadsheet that
> produces a message box asking if the user wishes to update. The code on this
> spreadsheet is protected how do i avoid having to cancel each message box to
> enable the spreadsheet to close.
>
> Any help will be much appreciated
>
> Regards
>
> Spike
> --
> with kind regards
>
> Spike

Spike:

you can disable even and change Calculation property

try,

Dim wbk As Workbook
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Set wbk = Workbooks.Open("C:\123.xls")
ThisWorkbook.Sheets("Sheet1").Range("A1:A10").Copy _
wbk.Sheets("Sheet1").Range("A1:A10")
wbk.Close SaveChanges:=True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

--
天行健，君�*�以自強不息
地勢坤，君�*�以厚德載物

http://www.vba.com.tw/plog/


"Spike" wrote:

> I have a macro that opens sequentially 34 quite large spreadsheets and copies
> data from up to 10 cells in each and pastes it to one spreadsheet. I am
> using the
> sourcedata.copy to destinationrange
> this is causing terrible memory problems.
> Does anyone have any ideas how i get around this. The destination
> spreadsheet also has about 400 linked cells to 3 other workbooks.
>
> I also have another issue in that the spreadsheets that are being opened to
> copy data from have a macro that is fired on closing the spreadsheet that
> produces a message box asking if the user wishes to update. The code on this
> spreadsheet is protected how do i avoid having to cancel each message box to
> enable the spreadsheet to close.
>
> Any help will be much appreciated
>
> Regards
>
> Spike
> --
> with kind regards
>
> Spike

For the OP

Ado is a good option, another option is to create links to the workbooks
http://www.rondebruin.nl/summary2.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Greg Wilson" <[email protected]> wrote in message news:[email protected]...
> You don't need to open a workbook to get data from it. Ron Debruin has code
> that allows you to obtain data without opening the wb:
>
> http://www.rondebruin.nl/ado.htm
>
> The following is another example of how to get data from closed wbs. Assumed
> is that the data from all source wbs is in column A starting at A1. Also
> assumed is that you want to copy to the destination wb into column A starting
> in cell A1 for the first source wb and then to column B for the second source
> wb and so forth. Minimal testing:
>
> Sub TransferData()
> Dim c As Range, cc As Range
> Dim ws As Worksheet
> Dim P As String, FN As String
> Dim FileNameArr As Variant
> Dim i As Long, ii As Long, Pos As Long
>
> On Error GoTo ExitProc
> FileNameArr = Application.GetOpenFilename _
> ("Excel Files(*.xls), *.xls", MultiSelect:=True)
> If VarType(FileNameArr) = vbBoolean Then Exit Sub
> With Application
> .ScreenUpdating = False
> .Calculation = xlCalculationManual
> Set ws = ThisWorkbook.Sheets("Sheet1") 'Destination ws
> Set c = ws.Range("A1") 'Top cell in destination range
> Pos = InStrRev(FileNameArr(1), "\")
> P = Left(FileNameArr(1), Pos - 1) 'Path of first source wb
> For i = LBound(FileNameArr) To UBound(FileNameArr)
> FN = Dir(FileNameArr(i)) 'Iterate through source wb names
> ii = 1
> Do
> Set cc = c(ii, i)
> cc.Formula = "='" & P & "\[" & FN & "]Sheet1'!" & c(ii,
> 1).Address & ""
> cc.Value = cc.Value 'Transform formulas to values
> ii = ii + 1
> Loop Until cc.Value = 0
> cc.ClearContents
> Next i
> ExitProc:
> .ScreenUpdating = True
> .Calculation = xlCalculationAutomatic
> End With
> End Sub
>
> Regards,
> Greg
>
> "Spike" wrote:
>
>> I have a macro that opens sequentially 34 quite large spreadsheets and copies
>> data from up to 10 cells in each and pastes it to one spreadsheet. I am
>> using the
>> sourcedata.copy to destinationrange
>> this is causing terrible memory problems.
>> Does anyone have any ideas how i get around this. The destination
>> spreadsheet also has about 400 linked cells to 3 other workbooks.
>>
>> I also have another issue in that the spreadsheets that are being opened to
>> copy data from have a macro that is fired on closing the spreadsheet that
>> produces a message box asking if the user wishes to update. The code on this
>> spreadsheet is protected how do i avoid having to cancel each message box to
>> enable the spreadsheet to close.
>>
>> Any help will be much appreciated
>>
>> Regards
>>
>> Spike
>> --
>> with kind regards
>>
>> Spike