Workbook with macros slow on server from remote location
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Workbook with macros slow on server from remote location

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Louisiana USA
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a workbook on a server that contains several macros that users access from remote locations. We have a 128 K pipe into these remote locations. The macros are only activated when a user clicks on a button to activate that certain macro. The workbook opens fast enough but when one of the macros is activated, it can take up to 20 minutes to complete its operation. I have no problem operating the macros from (my location) even though I have to access the server the same wat other users do. (I'm in the same building that houses the server.) None of the macros take more than a few seconds to complete their job.
    Now, some of the users computers are running different platforms. Some with Win 95, Win NT, Win 98, XP & 2000. All the computers are running MS 2000 Professional Edition. Could it be a problem with the different platforms? I had one user run a speed test on the network connection and the results were 94K on text from his location which should be fast enough for the macros to preform their operation in a minute or so. I'm enclosing all the code that I'm using in case there is a way to speed things up by editing it.

    Private Sub CommandButton2_Click()
    Sheets("Sheet1").Range("A1") = 1 + Sheets("Sheet1").Range("A1")
    Range("C4:E13,A16:I20").Select
    Range("A16").Activate
    ActiveWindow.SmallScroll Down:=18
    Range("C4:E13,A16:I20,A22:I38").Select
    Range("A22").Activate
    ActiveWindow.SmallScroll Down:=6
    Range("C4:E13,A16:I20,A22:I38,A40:I45,A47:I50").Select
    Range("A47").Activate
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-24
    Range("C4:E4").Select
    CleanComments
    End Sub

    Private Sub CommandButton3_Click()
    ThisFile = Range("G3") & Range("I3").Value
    ActiveWorkbook.SaveAs Filename:= _
    "L:MaintenanceEast Cam FieldMechanicWork OrdersUncompleted Work Orders" & ThisFile _
    , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    Application.DisplayAlerts = False
    ChDir "L:MaintenanceEast Cam FieldMechanicWork Orders"
    ActiveWorkbook.SaveAs Filename:= _
    "L:MaintenanceEast Cam FieldMechanicWork OrdersWork Orders.xls", _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    Application.DisplayAlerts = True
    End Sub


    Private Sub CommandButton4_Click()
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= _
    "L:MaintenanceEast Cam FieldMechanicWork OrdersWork Orders.xls", _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    Application.DisplayAlerts = True
    Application.Quit
    End Sub





  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-05 11:01, Sam W wrote:
    I have a workbook on a server that contains several macros that users access from remote locations. We have a 128 K pipe into these remote locations. The macros are only activated when a user clicks on a button to activate that certain macro. The workbook opens fast enough but when one of the macros is activated, it can take up to 20 minutes to complete its operation. I have no problem operating the macros from (my location) even though I have to access the server the same wat other users do. (I'm in the same building that houses the server.) None of the macros take more than a few seconds to complete their job.
    Now, some of the users computers are running different platforms. Some with Win 95, Win NT, Win 98, XP & 2000. All the computers are running MS 2000 Professional Edition. Could it be a problem with the different platforms? I had one user run a speed test on the network connection and the results were 94K on text from his location which should be fast enough for the macros to preform their operation in a minute or so. I'm enclosing all the code that I'm using in case there is a way to speed things up by editing it.

    Private Sub CommandButton2_Click()
    Sheets("Sheet1").Range("A1") = 1 + Sheets("Sheet1").Range("A1")
    Range("C4:E13,A16:I20").Select
    Range("A16").Activate
    ActiveWindow.SmallScroll Down:=18
    Range("C4:E13,A16:I20,A22:I38").Select
    Range("A22").Activate
    ActiveWindow.SmallScroll Down:=6
    Range("C4:E13,A16:I20,A22:I38,A40:I45,A47:I50").Select
    Range("A47").Activate
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-24
    Range("C4:E4").Select
    CleanComments
    End Sub

    Private Sub CommandButton3_Click()
    ThisFile = Range("G3") & Range("I3").Value
    ActiveWorkbook.SaveAs Filename:= _
    "L:MaintenanceEast Cam FieldMechanicWork OrdersUncompleted Work Orders" & ThisFile _
    , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    Application.DisplayAlerts = False
    ChDir "L:MaintenanceEast Cam FieldMechanicWork Orders"
    ActiveWorkbook.SaveAs Filename:= _
    "L:MaintenanceEast Cam FieldMechanicWork OrdersWork Orders.xls", _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    Application.DisplayAlerts = True
    End Sub


    Private Sub CommandButton4_Click()
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= _
    "L:MaintenanceEast Cam FieldMechanicWork OrdersWork Orders.xls", _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    Application.DisplayAlerts = True
    Application.Quit
    End Sub




    This code will run slightly faster (especially CommandButton2_Click):

    Private Sub CommandButton2_Click()
    Application.ScreenUpdating = False
    Sheets("Sheet1").Range("A1") = 1 + Sheets("Sheet1").Range("A1")
    Range("C4:E13,A16:I20,A22:I38,A40:I45,A47:I50").ClearContents
    Range("C4:E4").Select
    CleanComments
    Application.ScreenUpdating = True
    End Sub

    Private Sub CommandButton3_Click()
    ThisFile = Range("G3") & Range("I3").Value
    ActiveWorkbook.SaveAs FileName:= _
    "L:MaintenanceEast Cam FieldMechanicWork OrdersUncompleted Work Orders" & ThisFile _
    , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs FileName:= _
    "L:\Maintenance\East Cam Field\Mechanic\Work Orders\Work Orders.xls", _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    Application.DisplayAlerts = True
    End Sub


    Private Sub CommandButton4_Click()
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs FileName:= _
    "L:MaintenanceEast Cam FieldMechanicWork OrdersWork Orders.xls", _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    Application.DisplayAlerts = True
    Application.Quit
    End Sub


    Selecting and activating cells can slow down a macro. I also turned off the screen updating (Application.ScreenUpdating = False) so you won't have the window refreshing all the time. Not sure, however, how much quicker these changes will make the macro.

    Regards,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Louisiana USA
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thanks Barrie, By the way I like your web Sight

    Sam Wactor

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com