Workbook with macros slow on server from remote location

Sam W

New Member
Joined
Feb 18, 2002
Messages
26
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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,
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top