On Sep 28, 10:06*am, Don Guillett Excel MVP
wrote:
On Sep 28, 6:52*am, LunaMoon wrote:



On Sep 27, 6:32*pm, LunaMoon 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

anybody? thanks- Hide quoted text -

- Show quoted text -

Your code appears ?? to be too complex . Why not just open the
fileconvert to valuecopy paste close withOUT saving. Something like
this

Sub GetValuesFromClosedWorkbookSAS()
Dim mf As String
Application.ScreenUpdating = False
mf = ActiveWorkbook.Name
Workbooks.Open Filename:="C:\yourfoldername\yourfilename.xls"
With ActiveWorkbook.Sheets("Sheet1").UsedRange
* .Value = .Value
* .Copy Workbooks(mf).Sheets("sheet33").Range("f1")
End With
ActiveWindow.Close False
Application.ScreenUpdating = True
End Sub

You copy only value, not formatting? Need both value and formatting...

And also, you copy "C:\yourfoldername\yourfilename.xls" to the current
workbook;

but I need to freeze 5 different files, i.e.

for each of the 5 different files, I need to freeze it, and save;

and then do the next one, one by one...

Any thoughts? Thank you!