| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro, slow performance on terminal server...
I'm having some trouble with a Excel macro, which transfers data from
one worksheet to another in the same workbook. The macro is embedded in the workbook. It is a rather large workbook with several, big worksheets. On my own PC i open 10 copies of the same workbook and run the macro it takes less than 1 second (Excel 2003) If i open 20 workbooks it still takes less than a second. On a terminal server, where i am the only user i open 10 copies of the same workbook and run the macro it takes less than 1 second (Excel 2003) If i open 20 workbooks it still takes less than a second. No problem so far. But on another terminal server (Excel 2003), the macro just runs slower and slower the more workbooks i open. For example: - 1 workbook, execution time less than 1 second as expected. - 5 workbooks, execution time around 3 seconds - 10 workbooks, execution time around 9 seconds - 20 workbooks, execution time around 23 seconds If i log in locally on this terminalserver performance is fast even with 20 workbooks as i would expect. Does anyone have any idea what's going on? Why is the second terminal slowing down so dramatically? A copy of the Workbook can be found at: http://home19.inet.tele.dk/skankkid/slow_ts.zip The Macro in question is module1.WriteToTablesOut. I don't think that it is the macro itself which is the problem, i think it is some setting in either Excel or on the terminalserver. Any ideas?? Lars G. This is the macro itself: Public Function WriteToTablesOut() As Boolean Application.ScreenUpdating = False Application.Calculation = xlManual 'Define first line in TABLE_OUT Features table line_cnt = 28 'Delete items in table For i = line_cnt To 56 For j = 1 To 10 ThisWorkbook.Worksheets("TABLES_OUT").Cells(line_c nt, j + 1) = "" Next line_cnt = line_cnt + 1 Next line_cnt = 28 'Write active feature dimensions from 'DRAWING' to features table 'TABLES_OUT' For i = 18 To 105 checkval = ThisWorkbook.Worksheets("DRAWING").Cells(i, 24) If checkval = 1 Then For j = 1 To 10 If ThisWorkbook.Worksheets("DRAWING").Cells(i, 24 + j) = 0 And (j = 3 Or j = 4 Or j = 5) Then ThisWorkbook.Worksheets("TABLES_OUT").Cells(line_c nt, j + 1) = " " Else ThisWorkbook.Worksheets("TABLES_OUT").Cells(line_c nt, j + 1) = ThisWorkbook.Worksheets("DRAWING").Cells(i, 24 + j) End If Next line_cnt = line_cnt + 1 End If Next 'Calculate and set X-Y coordinates for cutouts in plank surface where there is a flag Cutout_Centered = 0 'Plank center X/Y coords Center_Xcoord = (ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(2, 2) / 2) - 0.1 Center_Ycoord = (ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(3, 2) / 2) - 0.1 'If no centering is required then skip For i = 561 To 566 Cutout_Centered = ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(i, 2) If Cutout_Centered = 1 Then GoTo Centered_Found End If Next Centered_Found: If Cutout_Centered < 1 Then GoTo CenteringEnd End If 'RNDHL_01_PLNK_CENTERED 'Change RNDHL_01_PLNK_H on Line 80 of INVENTOR PARAMETER to Plank Center Y-coord 'Change RNDHL_01_PLNK_W on Line 81 of INVENTOR PARAMETER to Plank Center X-coord If i = 561 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(80, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(81, 2) = Center_Xcoord End If 'RNDHL_02_PLNK_CENTERED 'Change RNDHL_02_PLNK_H on Line 89 of INVENTOR PARAMETER to Plank Center Y-coord 'Change RNDHL_02_PLNK_W on Line 90 of INVENTOR PARAMETER to Plank Center X-coord If i = 562 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(89, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(90, 2) = Center_Xcoord End If 'RCTNGL_01_PLNK_CENTERED 'Change RCTNGL_01_PLNK_H on Line 97 of INVENTOR PARAMETER to Plank Center Y-coord 'Change RCTNGL_01_PLNK_W on Line 98 of INVENTOR PARAMETER to Plank Center X-coord If i = 563 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(97, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(98, 2) = Center_Xcoord End If 'RCTNGL_02_PLNK_CENTERED 'Change RCTNGL_02_PLNK_H on Line 107 of INVENTOR PARAMETER to Plank Center Y-coord 'Change RCTNGL_02_PLNK_W on Line 108 of INVENTOR PARAMETER to Plank Center X-coord If i = 564 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(107, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(108, 2) = Center_Xcoord End If 'INFRM_CENTERED 'Change INFRM_W on Line 119 of INVENTOR PARAMETER to Plank Center Y-coord 'Change INFRM_H on Line 120 of INVENTOR PARAMETER to Plank Center X-coord If i = 565 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(119, 2) = Center_Xcoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(120, 2) = Center_Ycoord End If 'DMPL_CENTERED 'Change DMPL_H on Line 128 of INVENTOR PARAMETER to Plank Center Y-coord 'Change DMPL_W on Line 129 of INVENTOR PARAMETER to Plank Center X-coord If i = 566 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(128, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(129, 2) = Center_Xcoord End If CenteringEnd: 'Update Inner and Outer Frame calculations ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(13, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(45, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(14, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(46, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(15, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(47, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(16, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(48, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(18, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(49, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(19, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(50, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(20, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(51, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(21, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(52, 2) 'Update flatpattern dims ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(5, 2) = ThisWorkbook.Worksheets("DRAWING").Cells(9, 3) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(6, 2) = ThisWorkbook.Worksheets("DRAWING").Cells(9, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(7, 2) = ThisWorkbook.Worksheets("DRAWING").Cells(12, 3) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(8, 2) = ThisWorkbook.Worksheets("DRAWING").Cells(12, 2) 'Update plank type data - Work order & Perf type ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(567, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(6, 5) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(568, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(7, 5) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(569, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(4, 5) WriteToTablesOut = True Application.ScreenUpdating = True Application.Calculation = xlAutomatic ThisWorkbook.Worksheets("INVENTOR PARAMETER").Calculate ThisWorkbook.Worksheets("TABLES_OUT").Calculate End Function |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Macro, slow performance on terminal server...
Check the memory usege when you run on both servers. the slowerr server
probably is doing a lot of memory swapping beause it doesn't have enough memory to run that many copies of the program. "Lars Gaarde" wrote: I'm having some trouble with a Excel macro, which transfers data from one worksheet to another in the same workbook. The macro is embedded in the workbook. It is a rather large workbook with several, big worksheets. On my own PC i open 10 copies of the same workbook and run the macro it takes less than 1 second (Excel 2003) If i open 20 workbooks it still takes less than a second. On a terminal server, where i am the only user i open 10 copies of the same workbook and run the macro it takes less than 1 second (Excel 2003) If i open 20 workbooks it still takes less than a second. No problem so far. But on another terminal server (Excel 2003), the macro just runs slower and slower the more workbooks i open. For example: - 1 workbook, execution time less than 1 second as expected. - 5 workbooks, execution time around 3 seconds - 10 workbooks, execution time around 9 seconds - 20 workbooks, execution time around 23 seconds If i log in locally on this terminalserver performance is fast even with 20 workbooks as i would expect. Does anyone have any idea what's going on? Why is the second terminal slowing down so dramatically? A copy of the Workbook can be found at: http://home19.inet.tele.dk/skankkid/slow_ts.zip The Macro in question is module1.WriteToTablesOut. I don't think that it is the macro itself which is the problem, i think it is some setting in either Excel or on the terminalserver. Any ideas?? Lars G. This is the macro itself: Public Function WriteToTablesOut() As Boolean Application.ScreenUpdating = False Application.Calculation = xlManual 'Define first line in TABLE_OUT Features table line_cnt = 28 'Delete items in table For i = line_cnt To 56 For j = 1 To 10 ThisWorkbook.Worksheets("TABLES_OUT").Cells(line_c nt, j + 1) = "" Next line_cnt = line_cnt + 1 Next line_cnt = 28 'Write active feature dimensions from 'DRAWING' to features table 'TABLES_OUT' For i = 18 To 105 checkval = ThisWorkbook.Worksheets("DRAWING").Cells(i, 24) If checkval = 1 Then For j = 1 To 10 If ThisWorkbook.Worksheets("DRAWING").Cells(i, 24 + j) = 0 And (j = 3 Or j = 4 Or j = 5) Then ThisWorkbook.Worksheets("TABLES_OUT").Cells(line_c nt, j + 1) = " " Else ThisWorkbook.Worksheets("TABLES_OUT").Cells(line_c nt, j + 1) = ThisWorkbook.Worksheets("DRAWING").Cells(i, 24 + j) End If Next line_cnt = line_cnt + 1 End If Next 'Calculate and set X-Y coordinates for cutouts in plank surface where there is a flag Cutout_Centered = 0 'Plank center X/Y coords Center_Xcoord = (ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(2, 2) / 2) - 0.1 Center_Ycoord = (ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(3, 2) / 2) - 0.1 'If no centering is required then skip For i = 561 To 566 Cutout_Centered = ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(i, 2) If Cutout_Centered = 1 Then GoTo Centered_Found End If Next Centered_Found: If Cutout_Centered < 1 Then GoTo CenteringEnd End If 'RNDHL_01_PLNK_CENTERED 'Change RNDHL_01_PLNK_H on Line 80 of INVENTOR PARAMETER to Plank Center Y-coord 'Change RNDHL_01_PLNK_W on Line 81 of INVENTOR PARAMETER to Plank Center X-coord If i = 561 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(80, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(81, 2) = Center_Xcoord End If 'RNDHL_02_PLNK_CENTERED 'Change RNDHL_02_PLNK_H on Line 89 of INVENTOR PARAMETER to Plank Center Y-coord 'Change RNDHL_02_PLNK_W on Line 90 of INVENTOR PARAMETER to Plank Center X-coord If i = 562 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(89, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(90, 2) = Center_Xcoord End If 'RCTNGL_01_PLNK_CENTERED 'Change RCTNGL_01_PLNK_H on Line 97 of INVENTOR PARAMETER to Plank Center Y-coord 'Change RCTNGL_01_PLNK_W on Line 98 of INVENTOR PARAMETER to Plank Center X-coord If i = 563 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(97, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(98, 2) = Center_Xcoord End If 'RCTNGL_02_PLNK_CENTERED 'Change RCTNGL_02_PLNK_H on Line 107 of INVENTOR PARAMETER to Plank Center Y-coord 'Change RCTNGL_02_PLNK_W on Line 108 of INVENTOR PARAMETER to Plank Center X-coord If i = 564 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(107, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(108, 2) = Center_Xcoord End If 'INFRM_CENTERED 'Change INFRM_W on Line 119 of INVENTOR PARAMETER to Plank Center Y-coord 'Change INFRM_H on Line 120 of INVENTOR PARAMETER to Plank Center X-coord If i = 565 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(119, 2) = Center_Xcoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(120, 2) = Center_Ycoord End If 'DMPL_CENTERED 'Change DMPL_H on Line 128 of INVENTOR PARAMETER to Plank Center Y-coord 'Change DMPL_W on Line 129 of INVENTOR PARAMETER to Plank Center X-coord If i = 566 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(128, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(129, 2) = Center_Xcoord End If CenteringEnd: 'Update Inner and Outer Frame calculations ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(13, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(45, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(14, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(46, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(15, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(47, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(16, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(48, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(18, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(49, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(19, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(50, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(20, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(51, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(21, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(52, 2) 'Update flatpattern dims ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(5, 2) = ThisWorkbook.Worksheets("DRAWING").Cells(9, 3) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(6, 2) = ThisWorkbook.Worksheets("DRAWING").Cells(9, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(7, 2) = ThisWorkbook.Worksheets("DRAWING").Cells(12, 3) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(8, 2) = ThisWorkbook.Worksheets("DRAWING").Cells(12, 2) 'Update plank type data - Work order & Perf type ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(567, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(6, 5) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(568, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(7, 5) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(569, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(4, 5) WriteToTablesOut = True Application.ScreenUpdating = True Application.Calculation = xlAutomatic ThisWorkbook.Worksheets("INVENTOR PARAMETER").Calculate ThisWorkbook.Worksheets("TABLES_OUT").Calculate End Function |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Macro, slow performance on terminal server...
Unfortunately this is not the case.
We are still in the test phase and i am the only one using the server, The server has 2GB RAM and there is loads of free physical memory when running. Lars G. On Fri, 20 Apr 2007 06:36:01 -0700, Joel wrote: Check the memory usege when you run on both servers. the slowerr server probably is doing a lot of memory swapping beause it doesn't have enough memory to run that many copies of the program. "Lars Gaarde" wrote: I'm having some trouble with a Excel macro, which transfers data from one worksheet to another in the same workbook. The macro is embedded in the workbook. It is a rather large workbook with several, big worksheets. On my own PC i open 10 copies of the same workbook and run the macro it takes less than 1 second (Excel 2003) If i open 20 workbooks it still takes less than a second. On a terminal server, where i am the only user i open 10 copies of the same workbook and run the macro it takes less than 1 second (Excel 2003) If i open 20 workbooks it still takes less than a second. No problem so far. But on another terminal server (Excel 2003), the macro just runs slower and slower the more workbooks i open. For example: - 1 workbook, execution time less than 1 second as expected. - 5 workbooks, execution time around 3 seconds - 10 workbooks, execution time around 9 seconds - 20 workbooks, execution time around 23 seconds If i log in locally on this terminalserver performance is fast even with 20 workbooks as i would expect. Does anyone have any idea what's going on? Why is the second terminal slowing down so dramatically? A copy of the Workbook can be found at: http://home19.inet.tele.dk/skankkid/slow_ts.zip The Macro in question is module1.WriteToTablesOut. I don't think that it is the macro itself which is the problem, i think it is some setting in either Excel or on the terminalserver. Any ideas?? Lars G. This is the macro itself: Public Function WriteToTablesOut() As Boolean Application.ScreenUpdating = False Application.Calculation = xlManual 'Define first line in TABLE_OUT Features table line_cnt = 28 'Delete items in table For i = line_cnt To 56 For j = 1 To 10 ThisWorkbook.Worksheets("TABLES_OUT").Cells(line_c nt, j + 1) = "" Next line_cnt = line_cnt + 1 Next line_cnt = 28 'Write active feature dimensions from 'DRAWING' to features table 'TABLES_OUT' For i = 18 To 105 checkval = ThisWorkbook.Worksheets("DRAWING").Cells(i, 24) If checkval = 1 Then For j = 1 To 10 If ThisWorkbook.Worksheets("DRAWING").Cells(i, 24 + j) = 0 And (j = 3 Or j = 4 Or j = 5) Then ThisWorkbook.Worksheets("TABLES_OUT").Cells(line_c nt, j + 1) = " " Else ThisWorkbook.Worksheets("TABLES_OUT").Cells(line_c nt, j + 1) = ThisWorkbook.Worksheets("DRAWING").Cells(i, 24 + j) End If Next line_cnt = line_cnt + 1 End If Next 'Calculate and set X-Y coordinates for cutouts in plank surface where there is a flag Cutout_Centered = 0 'Plank center X/Y coords Center_Xcoord = (ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(2, 2) / 2) - 0.1 Center_Ycoord = (ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(3, 2) / 2) - 0.1 'If no centering is required then skip For i = 561 To 566 Cutout_Centered = ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(i, 2) If Cutout_Centered = 1 Then GoTo Centered_Found End If Next Centered_Found: If Cutout_Centered < 1 Then GoTo CenteringEnd End If 'RNDHL_01_PLNK_CENTERED 'Change RNDHL_01_PLNK_H on Line 80 of INVENTOR PARAMETER to Plank Center Y-coord 'Change RNDHL_01_PLNK_W on Line 81 of INVENTOR PARAMETER to Plank Center X-coord If i = 561 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(80, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(81, 2) = Center_Xcoord End If 'RNDHL_02_PLNK_CENTERED 'Change RNDHL_02_PLNK_H on Line 89 of INVENTOR PARAMETER to Plank Center Y-coord 'Change RNDHL_02_PLNK_W on Line 90 of INVENTOR PARAMETER to Plank Center X-coord If i = 562 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(89, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(90, 2) = Center_Xcoord End If 'RCTNGL_01_PLNK_CENTERED 'Change RCTNGL_01_PLNK_H on Line 97 of INVENTOR PARAMETER to Plank Center Y-coord 'Change RCTNGL_01_PLNK_W on Line 98 of INVENTOR PARAMETER to Plank Center X-coord If i = 563 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(97, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(98, 2) = Center_Xcoord End If 'RCTNGL_02_PLNK_CENTERED 'Change RCTNGL_02_PLNK_H on Line 107 of INVENTOR PARAMETER to Plank Center Y-coord 'Change RCTNGL_02_PLNK_W on Line 108 of INVENTOR PARAMETER to Plank Center X-coord If i = 564 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(107, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(108, 2) = Center_Xcoord End If 'INFRM_CENTERED 'Change INFRM_W on Line 119 of INVENTOR PARAMETER to Plank Center Y-coord 'Change INFRM_H on Line 120 of INVENTOR PARAMETER to Plank Center X-coord If i = 565 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(119, 2) = Center_Xcoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(120, 2) = Center_Ycoord End If 'DMPL_CENTERED 'Change DMPL_H on Line 128 of INVENTOR PARAMETER to Plank Center Y-coord 'Change DMPL_W on Line 129 of INVENTOR PARAMETER to Plank Center X-coord If i = 566 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(128, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(129, 2) = Center_Xcoord End If CenteringEnd: 'Update Inner and Outer Frame calculations ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(13, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(45, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(14, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(46, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(15, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(47, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(16, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(48, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(18, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(49, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(19, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(50, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(20, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(51, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(21, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(52, 2) 'Update flatpattern dims ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(5, 2) = ThisWorkbook.Worksheets("DRAWING").Cells(9, 3) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(6, 2) = ThisWorkbook.Worksheets("DRAWING").Cells(9, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(7, 2) = ThisWorkbook.Worksheets("DRAWING").Cells(12, 3) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(8, 2) = ThisWorkbook.Worksheets("DRAWING").Cells(12, 2) 'Update plank type data - Work order & Perf type ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(567, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(6, 5) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(568, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(7, 5) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(569, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(4, 5) WriteToTablesOut = True Application.ScreenUpdating = True Application.Calculation = xlAutomatic ThisWorkbook.Worksheets("INVENTOR PARAMETER").Calculate ThisWorkbook.Worksheets("TABLES_OUT").Calculate End Function |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Macro, slow performance on terminal server...
It doesn't matter how much memory is availabble, it matters how much is
assigned to the users. "Lars Gaarde" wrote: Unfortunately this is not the case. We are still in the test phase and i am the only one using the server, The server has 2GB RAM and there is loads of free physical memory when running. Lars G. On Fri, 20 Apr 2007 06:36:01 -0700, Joel wrote: Check the memory usege when you run on both servers. the slowerr server probably is doing a lot of memory swapping beause it doesn't have enough memory to run that many copies of the program. "Lars Gaarde" wrote: I'm having some trouble with a Excel macro, which transfers data from one worksheet to another in the same workbook. The macro is embedded in the workbook. It is a rather large workbook with several, big worksheets. On my own PC i open 10 copies of the same workbook and run the macro it takes less than 1 second (Excel 2003) If i open 20 workbooks it still takes less than a second. On a terminal server, where i am the only user i open 10 copies of the same workbook and run the macro it takes less than 1 second (Excel 2003) If i open 20 workbooks it still takes less than a second. No problem so far. But on another terminal server (Excel 2003), the macro just runs slower and slower the more workbooks i open. For example: - 1 workbook, execution time less than 1 second as expected. - 5 workbooks, execution time around 3 seconds - 10 workbooks, execution time around 9 seconds - 20 workbooks, execution time around 23 seconds If i log in locally on this terminalserver performance is fast even with 20 workbooks as i would expect. Does anyone have any idea what's going on? Why is the second terminal slowing down so dramatically? A copy of the Workbook can be found at: http://home19.inet.tele.dk/skankkid/slow_ts.zip The Macro in question is module1.WriteToTablesOut. I don't think that it is the macro itself which is the problem, i think it is some setting in either Excel or on the terminalserver. Any ideas?? Lars G. This is the macro itself: Public Function WriteToTablesOut() As Boolean Application.ScreenUpdating = False Application.Calculation = xlManual 'Define first line in TABLE_OUT Features table line_cnt = 28 'Delete items in table For i = line_cnt To 56 For j = 1 To 10 ThisWorkbook.Worksheets("TABLES_OUT").Cells(line_c nt, j + 1) = "" Next line_cnt = line_cnt + 1 Next line_cnt = 28 'Write active feature dimensions from 'DRAWING' to features table 'TABLES_OUT' For i = 18 To 105 checkval = ThisWorkbook.Worksheets("DRAWING").Cells(i, 24) If checkval = 1 Then For j = 1 To 10 If ThisWorkbook.Worksheets("DRAWING").Cells(i, 24 + j) = 0 And (j = 3 Or j = 4 Or j = 5) Then ThisWorkbook.Worksheets("TABLES_OUT").Cells(line_c nt, j + 1) = " " Else ThisWorkbook.Worksheets("TABLES_OUT").Cells(line_c nt, j + 1) = ThisWorkbook.Worksheets("DRAWING").Cells(i, 24 + j) End If Next line_cnt = line_cnt + 1 End If Next 'Calculate and set X-Y coordinates for cutouts in plank surface where there is a flag Cutout_Centered = 0 'Plank center X/Y coords Center_Xcoord = (ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(2, 2) / 2) - 0.1 Center_Ycoord = (ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(3, 2) / 2) - 0.1 'If no centering is required then skip For i = 561 To 566 Cutout_Centered = ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(i, 2) If Cutout_Centered = 1 Then GoTo Centered_Found End If Next Centered_Found: If Cutout_Centered < 1 Then GoTo CenteringEnd End If 'RNDHL_01_PLNK_CENTERED 'Change RNDHL_01_PLNK_H on Line 80 of INVENTOR PARAMETER to Plank Center Y-coord 'Change RNDHL_01_PLNK_W on Line 81 of INVENTOR PARAMETER to Plank Center X-coord If i = 561 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(80, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(81, 2) = Center_Xcoord End If 'RNDHL_02_PLNK_CENTERED 'Change RNDHL_02_PLNK_H on Line 89 of INVENTOR PARAMETER to Plank Center Y-coord 'Change RNDHL_02_PLNK_W on Line 90 of INVENTOR PARAMETER to Plank Center X-coord If i = 562 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(89, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(90, 2) = Center_Xcoord End If 'RCTNGL_01_PLNK_CENTERED 'Change RCTNGL_01_PLNK_H on Line 97 of INVENTOR PARAMETER to Plank Center Y-coord 'Change RCTNGL_01_PLNK_W on Line 98 of INVENTOR PARAMETER to Plank Center X-coord If i = 563 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(97, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(98, 2) = Center_Xcoord End If 'RCTNGL_02_PLNK_CENTERED 'Change RCTNGL_02_PLNK_H on Line 107 of INVENTOR PARAMETER to Plank Center Y-coord 'Change RCTNGL_02_PLNK_W on Line 108 of INVENTOR PARAMETER to Plank Center X-coord If i = 564 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(107, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(108, 2) = Center_Xcoord End If 'INFRM_CENTERED 'Change INFRM_W on Line 119 of INVENTOR PARAMETER to Plank Center Y-coord 'Change INFRM_H on Line 120 of INVENTOR PARAMETER to Plank Center X-coord If i = 565 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(119, 2) = Center_Xcoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(120, 2) = Center_Ycoord End If 'DMPL_CENTERED 'Change DMPL_H on Line 128 of INVENTOR PARAMETER to Plank Center Y-coord 'Change DMPL_W on Line 129 of INVENTOR PARAMETER to Plank Center X-coord If i = 566 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(128, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(129, 2) = Center_Xcoord End If CenteringEnd: 'Update Inner and Outer Frame calculations ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(13, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(45, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(14, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(46, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(15, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(47, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(16, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(48, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(18, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(49, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(19, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(50, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(20, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(51, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(21, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(52, 2) 'Update flatpattern dims ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(5, 2) = ThisWorkbook.Worksheets("DRAWING").Cells(9, 3) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(6, 2) = ThisWorkbook.Worksheets("DRAWING").Cells(9, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(7, 2) = ThisWorkbook.Worksheets("DRAWING").Cells(12, 3) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(8, 2) = ThisWorkbook.Worksheets("DRAWING").Cells(12, 2) 'Update plank type data - Work order & Perf type ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(567, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(6, 5) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(568, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(7, 5) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(569, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(4, 5) WriteToTablesOut = True Application.ScreenUpdating = True Application.Calculation = xlAutomatic ThisWorkbook.Worksheets("INVENTOR PARAMETER").Calculate ThisWorkbook.Worksheets("TABLES_OUT").Calculate End Function |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Macro, slow performance on terminal server...
OK i didn't realise that.
I'll look into it and check if it helps. Lars G. On Mon, 23 Apr 2007 03:44:01 -0700, Joel wrote: It doesn't matter how much memory is availabble, it matters how much is assigned to the users. "Lars Gaarde" wrote: Unfortunately this is not the case. We are still in the test phase and i am the only one using the server, The server has 2GB RAM and there is loads of free physical memory when running. Lars G. On Fri, 20 Apr 2007 06:36:01 -0700, Joel wrote: Check the memory usege when you run on both servers. the slowerr server probably is doing a lot of memory swapping beause it doesn't have enough memory to run that many copies of the program. "Lars Gaarde" wrote: I'm having some trouble with a Excel macro, which transfers data from one worksheet to another in the same workbook. The macro is embedded in the workbook. It is a rather large workbook with several, big worksheets. On my own PC i open 10 copies of the same workbook and run the macro it takes less than 1 second (Excel 2003) If i open 20 workbooks it still takes less than a second. On a terminal server, where i am the only user i open 10 copies of the same workbook and run the macro it takes less than 1 second (Excel 2003) If i open 20 workbooks it still takes less than a second. No problem so far. But on another terminal server (Excel 2003), the macro just runs slower and slower the more workbooks i open. For example: - 1 workbook, execution time less than 1 second as expected. - 5 workbooks, execution time around 3 seconds - 10 workbooks, execution time around 9 seconds - 20 workbooks, execution time around 23 seconds If i log in locally on this terminalserver performance is fast even with 20 workbooks as i would expect. Does anyone have any idea what's going on? Why is the second terminal slowing down so dramatically? A copy of the Workbook can be found at: http://home19.inet.tele.dk/skankkid/slow_ts.zip The Macro in question is module1.WriteToTablesOut. I don't think that it is the macro itself which is the problem, i think it is some setting in either Excel or on the terminalserver. Any ideas?? Lars G. This is the macro itself: Public Function WriteToTablesOut() As Boolean Application.ScreenUpdating = False Application.Calculation = xlManual 'Define first line in TABLE_OUT Features table line_cnt = 28 'Delete items in table For i = line_cnt To 56 For j = 1 To 10 ThisWorkbook.Worksheets("TABLES_OUT").Cells(line_c nt, j + 1) = "" Next line_cnt = line_cnt + 1 Next line_cnt = 28 'Write active feature dimensions from 'DRAWING' to features table 'TABLES_OUT' For i = 18 To 105 checkval = ThisWorkbook.Worksheets("DRAWING").Cells(i, 24) If checkval = 1 Then For j = 1 To 10 If ThisWorkbook.Worksheets("DRAWING").Cells(i, 24 + j) = 0 And (j = 3 Or j = 4 Or j = 5) Then ThisWorkbook.Worksheets("TABLES_OUT").Cells(line_c nt, j + 1) = " " Else ThisWorkbook.Worksheets("TABLES_OUT").Cells(line_c nt, j + 1) = ThisWorkbook.Worksheets("DRAWING").Cells(i, 24 + j) End If Next line_cnt = line_cnt + 1 End If Next 'Calculate and set X-Y coordinates for cutouts in plank surface where there is a flag Cutout_Centered = 0 'Plank center X/Y coords Center_Xcoord = (ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(2, 2) / 2) - 0.1 Center_Ycoord = (ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(3, 2) / 2) - 0.1 'If no centering is required then skip For i = 561 To 566 Cutout_Centered = ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(i, 2) If Cutout_Centered = 1 Then GoTo Centered_Found End If Next Centered_Found: If Cutout_Centered < 1 Then GoTo CenteringEnd End If 'RNDHL_01_PLNK_CENTERED 'Change RNDHL_01_PLNK_H on Line 80 of INVENTOR PARAMETER to Plank Center Y-coord 'Change RNDHL_01_PLNK_W on Line 81 of INVENTOR PARAMETER to Plank Center X-coord If i = 561 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(80, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(81, 2) = Center_Xcoord End If 'RNDHL_02_PLNK_CENTERED 'Change RNDHL_02_PLNK_H on Line 89 of INVENTOR PARAMETER to Plank Center Y-coord 'Change RNDHL_02_PLNK_W on Line 90 of INVENTOR PARAMETER to Plank Center X-coord If i = 562 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(89, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(90, 2) = Center_Xcoord End If 'RCTNGL_01_PLNK_CENTERED 'Change RCTNGL_01_PLNK_H on Line 97 of INVENTOR PARAMETER to Plank Center Y-coord 'Change RCTNGL_01_PLNK_W on Line 98 of INVENTOR PARAMETER to Plank Center X-coord If i = 563 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(97, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(98, 2) = Center_Xcoord End If 'RCTNGL_02_PLNK_CENTERED 'Change RCTNGL_02_PLNK_H on Line 107 of INVENTOR PARAMETER to Plank Center Y-coord 'Change RCTNGL_02_PLNK_W on Line 108 of INVENTOR PARAMETER to Plank Center X-coord If i = 564 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(107, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(108, 2) = Center_Xcoord End If 'INFRM_CENTERED 'Change INFRM_W on Line 119 of INVENTOR PARAMETER to Plank Center Y-coord 'Change INFRM_H on Line 120 of INVENTOR PARAMETER to Plank Center X-coord If i = 565 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(119, 2) = Center_Xcoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(120, 2) = Center_Ycoord End If 'DMPL_CENTERED 'Change DMPL_H on Line 128 of INVENTOR PARAMETER to Plank Center Y-coord 'Change DMPL_W on Line 129 of INVENTOR PARAMETER to Plank Center X-coord If i = 566 And Cutout_Centered = 1 Then ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(128, 2) = Center_Ycoord ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(129, 2) = Center_Xcoord End If CenteringEnd: 'Update Inner and Outer Frame calculations ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(13, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(45, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(14, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(46, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(15, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(47, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(16, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(48, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(18, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(49, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(19, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(50, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(20, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(51, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(21, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(52, 2) 'Update flatpattern dims ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(5, 2) = ThisWorkbook.Worksheets("DRAWING").Cells(9, 3) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(6, 2) = ThisWorkbook.Worksheets("DRAWING").Cells(9, 2) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(7, 2) = ThisWorkbook.Worksheets("DRAWING").Cells(12, 3) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(8, 2) = ThisWorkbook.Worksheets("DRAWING").Cells(12, 2) 'Update plank type data - Work order & Perf type ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(567, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(6, 5) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(568, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(7, 5) ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(569, 2) = ThisWorkbook.Worksheets("NTI Calculation").Cells(4, 5) WriteToTablesOut = True Application.ScreenUpdating = True Application.Calculation = xlAutomatic ThisWorkbook.Worksheets("INVENTOR PARAMETER").Calculate ThisWorkbook.Worksheets("TABLES_OUT").Calculate End Function |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Error 400 on executing macro first time in terminal server | Excel Programming | |||
| Error message when using Excel on Terminal Server | Excel Discussion (Misc queries) | |||
| Excel Add-in Use on a Terminal Server | Excel Discussion (Misc queries) | |||
| Terminal Server 2003 + Excel 2000 | Excel Programming | |||
| Excel thru Terminal Server | Excel Programming | |||
Linear Mode
