SOS! Help! Loading another workbook in the current workbook - cells
not updating!

Hi all,

Please help me. I have spent quite a few days on this but couldn't get
it work.

On Excel file A.xls, I have 5 file names(Excel files) in A1 to A5 on
"sheet1"... and in that A.xls file I have the following VBA code.

The goal is to load the 5 Excel files one by one, and freeze all the
sheets therein. i.e. copy and pastespecial as values with formatting
and color etc.

That's to say, the goal is to take static snapshots of the dynamically
changing cells and sheets.

Each sheet have cells that are dynamically linked to external data-
source. And the data-source is real-time ticking.

Ideally, when I open those Excel files, the cells should show real-
time ticking numerical values upon refreshing and updating...

However, when I open those Excel files from within A.xls, non of the
cells got updated and therefore, the "frozen" values are all
"#VALUE!"...

[

I also run the following code in debugging mode. During the period
when the code is running, the newly opened sheets never got updated
and the values are all "#VALUE!",

but as soon as the debugging mode exits, the numbers are updating
realtime ticking...

therefore the problem is: if I do everything manually, the numbers are
ticking; but if I do it programmatically, the numbers aren't ticking
and updating...

]
What's the problem? Please help me!

------------------------------------------


Public Sub CopyAndFreezeSheets()
Dim WBsrc As Workbook
Dim WBthis As Workbook
Dim WSthis As Worksheet
Dim i As Integer
Dim strFileName As String
Dim wsSheet As Worksheet

Dim WSsrc As Worksheet

'Set WBsrc = Workbooks.Open("c:\Program Files\blp\API\Office Tools
\blpxladdin.xll")
'WBsrc.RunAutoMacros 1


Set WBthis = ThisWorkbook

Set WSthis = WBthis.Sheets("sheet1")

For i = 1 To 5



WBthis.Activate
WSthis.Activate
strFileName = WSthis.Range("A" + CStr(i)).Value
Set WBsrc = Workbooks.Open(strFileName, False)
Application.CalculateFull

Set WBsrc = ActiveWorkbook

'Application.Run "BLPLinkReset"

For Each wsSheet In WBsrc.Sheets

wsSheet.Activate
wsSheet.Calculate
wsSheet.UsedRange.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Next

WBsrc.Save
WBsrc.Close


Next


End Sub