Anybody thanks?
On Sep 27, 6:33 pm, LunaMoon <lunamoonm...@gmail.com> wrote:
> 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
