|
SOS! Help! Loading another workbook in the current workbook - cellsnot updating!
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 |
SOS! Help! Loading another workbook in the current workbook -cells not updating!
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 |
SOS! Help! Loading another workbook in the current workbook -cells not updating!
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 |
SOS! Help! Loading another workbook in the current workbook -cells not updating!
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! |
SOS! Help! Loading another workbook in the current workbook -cells not updating!
On Sep 28, 10:46*am, LunaMoon wrote:
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!- Hide quoted text - - Show quoted text - I may NOT be clear about what you mean by "freeze" You modify code to get your 5 files. As I understand it you really don't need to change the source workbooks to values but get the values & number formats (freeze as you say) from each source workbook. This simple change should do it. If this is really NOT what you want, send me a very clear explanation along with the destination file and 2 source files with before/after examples. Try this first. Sub GetValuesFromClosedWorkbookSAS() Dim mf As String Application.ScreenUpdating = False mf = ActiveWorkbook.Name Workbooks.Open Filename:="C:\PERSONAL\book1.xls" With ActiveWorkbook.Sheets("Sheet1").UsedRange ..Value = .Value ..Copy 'Workbooks(mf).Sheets("sheet33").Range("f1") Workbooks(mf).Sheets("sheet33").Range("f1") _ ..PasteSpecial xlPasteValuesAndNumberFormats End With ActiveWindow.Close False Application.ScreenUpdating = True End Sub |
SOS! Help! Loading another workbook in the current workbook -cells not updating!
On Sep 28, 4:52*pm, Don Guillett Excel MVP
wrote: On Sep 28, 10:46*am, LunaMoon wrote: 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!- Hide quoted text - - Show quoted text - I may NOT be clear about what you mean by "freeze" You modify code to get your 5 files. As I understand it you really don't need to change the source workbooks to values but get the values & number formats (freeze as you say) from each source workbook. This simple change should do it. If this is really NOT what you want, send me a very clear explanation along with the destination file and 2 source files with before/after examples. Try this first. Sub GetValuesFromClosedWorkbookSAS() Dim mf As String Application.ScreenUpdating = False mf = ActiveWorkbook.Name Workbooks.Open Filename:="C:\PERSONAL\book1.xls" With ActiveWorkbook.Sheets("Sheet1").UsedRange .Value = .Value .Copy 'Workbooks(mf).Sheets("sheet33").Range("f1") Workbooks(mf).Sheets("sheet33").Range("f1") _ .PasteSpecial xlPasteValuesAndNumberFormats End With ActiveWindow.Close False Application.ScreenUpdating = True End Sub Thanks. How about this? Let me describe the whole workflow using pseudo code. Now you have opened a file called A.xls, and in the "sheet1" of A.xls, you have the first 5 cells (A1 to A5) containing the 5 file names. For each of the file names, you want to: 1. Open up that Excel file; 2. For each sheet in that Excel file Do: 2.1 Select all cells; 2.2 Copy all cells; 2.3 PasteSpecial ValuesAndFormats on the same sheet (i.e. freeze all the values on that sheet); 3. Save the Excel fille and close it; and then move to the next Excel file. That's all. ----------------------------------- Again, the main difficulty of my program (shown in my previous post) was that when I open the 5 Excel file programmatically, the celll values don't show numbers, but show "!VALUE#". I think this is because these cells use Bloomberg's datafeed (API or addin, etc.) and they should be real-time ticking... Any more thoughts? Thanks! |
| All times are GMT +1. The time now is 02:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com