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
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
