Workbook with macros slow on server from remote location

[Sam W]

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

 

[Barrie Davidson]

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,

 

[Sam W]

Thanks Barrie, By the way I like your web Sight

Sam Wactor