Mirdurern
asked on
Excel 2000 Lag after running a macro across the network
After I go out to 7 network drives and parsar a text file. I have lag in Excel until I close the program and restart it. After re-starting Excel the lag is gone. Any idea what is causing this lag? I see the lag on XP and Win-2000 machines.
Thanks!
Thanks!
How do you access the text files?
ASKER
yes it works great until the program finishes. Then when I go and look at the information I have super bad lag. Like it still tring to connect to the network or something.
Thanks
Thanks
Mirdurern ....
Maybe in your Code you are opening an Instance of Excel
and you forget to clean it Up after Finishing ...
you should clean it Up or else it'll cause a Memory Leak and the Excel Instance will stil be running and that maybe the cause of the Lag.
If you created an Excel Object in VB Named "ObjExcel"
then after Finishing your Code you should Clean it up like:
ObjExcel.Quit
Set ObjExcel = Nothing
sorry If I understood you wrong but you didn't Provide us with enough Information about the Problem
OHDev
Maybe in your Code you are opening an Instance of Excel
and you forget to clean it Up after Finishing ...
you should clean it Up or else it'll cause a Memory Leak and the Excel Instance will stil be running and that maybe the cause of the Lag.
If you created an Excel Object in VB Named "ObjExcel"
then after Finishing your Code you should Clean it up like:
ObjExcel.Quit
Set ObjExcel = Nothing
sorry If I understood you wrong but you didn't Provide us with enough Information about the Problem
OHDev
ASKER
Hello... I am new to programming so i will try to give more information. Here is what i do in the set of macros. I go out onto the network to 7 machines. These machines run Window NT. I read a machine log file on start-stop of the machine. I then import, that into excel and clean it up. I then place it a graph form. When I finish the macro, I switch between windows. The machine comes to a stop. The processor will jump to 100%. If I close out of Excel and reopen the files, I do not see any lag.
I hope this is more helpful.
Thanks again for you all of your ideas on this issues
I hope this is more helpful.
Thanks again for you all of your ideas on this issues
Can we see some code please.
ASKER
Here is the main macro ... please dont laugh to hard at the bad code writing :) I am still learning.
Global ViperFdate As Date
Global ViperSdate As Date
Global Path As String
Global Filename As String
Global ViperFilename As String
Global vipernumber As Integer
Sub Main()
Dim Viper_Count As Integer
Dim Start_Time_c As Date
Dim Final_Time_c As Date
Application.ScreenUpdating = False
Start_Time_c = Now
ViperSdate = Sheets("Input_Data").Cells (1, 2).Value
ViperFdate = Sheets("Input_Data").Cells (2, 2).Value
If ViperSdate > ViperFdate Then
MsgBox ("Final Date is before the Start Date... Please Enter New Values")
GoTo END_SUB
End If
Viper_Count = 1
Call Clear_Math_data 'Mod9
Call opening_main_book 'Mod8
Do
vipernumber = Viper_Count
Call Open_Viper_Log 'Mod2
Call Tranfer ' Mod2
Call FIX_Date_new_Sheet 'Mod5
Call BuildStatus 'Mod4
Call Fix_Double_Starts 'Mod6
Call Find_Date_Date 'Mod3
Call Moving_to_new_sheet 'Mod7
Call Carry_Over_Time 'Mod10
Call Math_Time_BuildSize 'Mod11
Call Date_Start_final 'Mod12
Call Filling_in_time_start_Fini sh 'Mod13
Call Viper_Time_Per_Period 'Mod16
Windows("SLA_Viper_Macro.x ls").Activ ate
Sheets("Math").Select
Viper_Count = Viper_Count + 1
Loop Until Viper_Count = 9
END_SUB:
Windows("WORKING_BOOK.xls" ).Activate
ActiveWorkbook.Save
Windows("SLA_Viper_Macro.x ls").Activ ate
ActiveWorkbook.Save
Windows("WORKING_BOOK.xls" ).Activate
ActiveWorkbook.Save
ActiveWindow.Close
Windows("SLA_Viper_Macro.x ls").Activ ate
Final_Time_c = Now
Worksheets("Input_Data").C ells(10, "B").Value = Start_Time_c
Worksheets("Input_Data").C ells(11, "B").Value = Final_Time_c
Windows("Viper_On_Off.xls" ).Activate
Sheets("Summary").Select
Application.ScreenUpdating = True
End Sub
Sub Open_Viper_Log()
ViperFilename = "\\Viper" & vipernumber & "\c$\WINSLA\Output\BuildLo g.txt"
Workbooks.OpenText Filename:=ViperFilename, _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True , Tab:=False, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 3), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1))
Windows("BuildLog.txt").Ac tivate
End Sub
Sub Tranfer()
Dim Sheetnum As Integer
Dim Sheet_Count As Integer
Dim Sheetnum_2 As Integer
Application.DisplayAlerts = False
Sheetnum_2 = 2
If vipernumber = 1 Then
Workbooks.Open Filename:= _
"R:\Viper_Utilization\WORK ING_BOOK.x ls"
Windows("WORKING_BOOK.xls" ).Activate
Sheet_Count = ActiveWorkbook.Sheets.Coun t
If Sheet_Count = 1 Then
GoTo HERE
End If
For Sheetnum = 2 To ActiveWorkbook.Sheets.Coun t
Sheets(Sheetnum_2).Select
ActiveWindow.SelectedSheet s.Delete
'SendKeys "{ENTER}", True
Next Sheetnum
End If
HERE:
Windows("BuildLog.txt").Ac tivate
Sheets("BuildLog").Select
Sheets("BuildLog").Copy After:=Workbooks("WORKING_ BOOK.xls") .Sheets(1)
Windows("BuildLog.txt").Ac tivate
ActiveWindow.Close
'Application.WindowState = xlMinimized
Sheets("BuildLog").Select
Sheets("BuildLog").Name = "Viper_" & vipernumber & "_Math"
ViperFilename = "\\Viper" & vipernumber & "\c$\WINSLA\Output\BuildLo g.txt"
Workbooks.OpenText Filename:=ViperFilename, _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True , Tab:=True, Semicolon:=False, _
Comma:=True, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 3), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1))
Windows("BuildLog.txt").Ac tivate
Windows("BuildLog.txt").Ac tivate
Sheets("BuildLog").Select
Sheets("BuildLog").Copy After:=Workbooks("WORKING_ BOOK.xls") .Sheets(1)
Windows("BuildLog.txt").Ac tivate
ActiveWindow.Close
'Application.WindowState = xlMinimized
Sheets("BuildLog").Select
Sheets("BuildLog").Name = "Viper_" & vipernumber & "_Math_1"
Windows("WORKING_BOOK.xls" ).Activate
Sheets("Viper_" & vipernumber & "_Math_1").Select
Columns("A:A").Select
Selection.Cut
Sheets("Viper_" & vipernumber & "_Math").Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "mm/dd/yy"
Sheets("Viper_" & vipernumber & "_Math_1").Select
ActiveWindow.SelectedSheet s.Delete
Application.DisplayAlerts = True
Windows("Working_Book.xls" ).Activate
End Sub
Sub Find_Date_Date()
Dim Check_Time As Date
Dim Sheetname As String
'Dim ViperNumber As Integer
Dim Count_Start As Integer
Dim Count_Final As Integer
Dim Max As Integer
Dim Status As Integer
Dim Temp_Start_Date As Date
Dim Temp_Final_Date As Date
Dim Check_Date As Date
Dim STOP_LOOP As Integer
Dim Max_Stop_LOOP As Integer
Dim iCount1 As Integer
Dim iCount2 As Integer
Dim Total_number_days As Double
Dim Start_Date_Math As Date
Dim Final_Date_Math As Date
Max_Stop_LOOP = 20
Count_Start = 1
'ViperNumber = 5
Windows("Working_Book.xls" ).Activate
Sheets("Viper_" & vipernumber & "_Math").Select
Do
iCount1 = iCount1 + 1
Loop Until IsEmpty(Cells(iCount1, 1))
Max = iCount1
Status = 1
STOP_LOOP = 1
Sheetname = "Viper_" & vipernumber & "_Math"
'ViperSdate = "4/17/2005"
'ViperFdate = "4/24/2005"
Temp_Start_Date = ViperSdate
Temp_Final_Date = ViperFdate
Do
Do Until Check_Date = Temp_Start_Date
Check_Date = Worksheets(Sheetname).Cell s(Count_St art, "A").Value
Count_Start = Count_Start + 1
If Count_Start = Max Then
Status = Status + 1
Exit Do
End If
Loop
If Status > 1 Then
Status = Status - 1
Temp_Start_Date = Temp_Start_Date + 1
Count_Start = 1
Check_Date = "1/05/1900"
STOP_LOOP = STOP_LOOP + 1
End If
If Check_Date = Temp_Start_Date Then GoTo ENDLOOP
If STOP_LOOP = Max_Stop_LOOP Then
MsgBox "Please try a different starting date"
Exit Sub
End If
Loop Until Status > 1
ENDLOOP:
If Temp_Start_Date <> ViperSdate Then
Windows("SLA_Viper_Macro.x ls").Activ ate
Worksheets("Math").Cells(2 , 1 + vipernumber).Value = Temp_Start_Date
Else
Windows("SLA_Viper_Macro.x ls").Activ ate
Worksheets("Math").Cells(2 , 1 + vipernumber).Value = ViperSdate
Windows("Working_Book.xls" ).Activate
End If
Status = 1
Count_Final = 1
STOP_LOOP = 1
'Find final date
Windows("Working_Book.xls" ).Activate
Do
Do Until Check_Date = Temp_Final_Date
Check_Date = Worksheets(Sheetname).Cell s(Count_Fi nal, "A").Value
Count_Final = Count_Final + 1
If Count_Final = Max Then
Status = Status + 1
Exit Do
End If
Loop
If Status > 1 Then
Status = Status - 1
Temp_Final_Date = Temp_Final_Date + 1
Count_Final = 1
Check_Date = "1/05/1900"
STOP_LOOP = STOP_LOOP + 1
End If
If Check_Date = Temp_Final_Date Then GoTo ENDLOOP1
If STOP_LOOP = Max_Stop_LOOP Then
MsgBox "Please try a different final date"
Exit Sub
End If
Loop Until Status > 1
ENDLOOP1:
If Temp_Final_Date <> ViperFdate Then
Windows("SLA_Viper_Macro.x ls").Activ ate
Worksheets("Math").Cells(3 , 1 + vipernumber).Value = Temp_Final_Date
Else
Windows("SLA_Viper_Macro.x ls").Activ ate
Worksheets("Math").Cells(3 , 1 + vipernumber).Value = ViperFdate
Windows("Working_Book.xls" ).Activate
End If
iCount2 = Count_Final - 1
'Find the final end date in the list
Windows("Working_Book.xls" ).Activate
Check_Date = Worksheets(Sheetname).Cell s(Count_Fi nal, "A").Value
Do Until Check_Date <> Temp_Final_Date
Check_Date = Worksheets(Sheetname).Cell s(iCount2, "A").Value
iCount2 = iCount2 + 1
Loop
Count_Final = iCount2
Windows("SLA_Viper_Macro.x ls").Activ ate
Start_Date_Math = Worksheets("Math").Cells(2 , 1 + vipernumber).Value
Final_Date_Math = Worksheets("Math").Cells(3 , 1 + vipernumber).Value
Total_number_days = Final_Date_Math - Start_Date_Math
Worksheets("Math").Cells(8 , 1 + vipernumber).Value = Total_number_days
Worksheets("Math").Cells(4 , 1 + vipernumber).Value = Count_Start - 1
Worksheets("Math").Cells(5 , 1 + vipernumber).Value = Count_Final - 2
End Sub
I hope that is enough code if not i can copy the rest of it. Thanks
Global ViperFdate As Date
Global ViperSdate As Date
Global Path As String
Global Filename As String
Global ViperFilename As String
Global vipernumber As Integer
Sub Main()
Dim Viper_Count As Integer
Dim Start_Time_c As Date
Dim Final_Time_c As Date
Application.ScreenUpdating
Start_Time_c = Now
ViperSdate = Sheets("Input_Data").Cells
ViperFdate = Sheets("Input_Data").Cells
If ViperSdate > ViperFdate Then
MsgBox ("Final Date is before the Start Date... Please Enter New Values")
GoTo END_SUB
End If
Viper_Count = 1
Call Clear_Math_data 'Mod9
Call opening_main_book 'Mod8
Do
vipernumber = Viper_Count
Call Open_Viper_Log 'Mod2
Call Tranfer ' Mod2
Call FIX_Date_new_Sheet 'Mod5
Call BuildStatus 'Mod4
Call Fix_Double_Starts 'Mod6
Call Find_Date_Date 'Mod3
Call Moving_to_new_sheet 'Mod7
Call Carry_Over_Time 'Mod10
Call Math_Time_BuildSize 'Mod11
Call Date_Start_final 'Mod12
Call Filling_in_time_start_Fini
Call Viper_Time_Per_Period 'Mod16
Windows("SLA_Viper_Macro.x
Sheets("Math").Select
Viper_Count = Viper_Count + 1
Loop Until Viper_Count = 9
END_SUB:
Windows("WORKING_BOOK.xls"
ActiveWorkbook.Save
Windows("SLA_Viper_Macro.x
ActiveWorkbook.Save
Windows("WORKING_BOOK.xls"
ActiveWorkbook.Save
ActiveWindow.Close
Windows("SLA_Viper_Macro.x
Final_Time_c = Now
Worksheets("Input_Data").C
Worksheets("Input_Data").C
Windows("Viper_On_Off.xls"
Sheets("Summary").Select
Application.ScreenUpdating
End Sub
Sub Open_Viper_Log()
ViperFilename = "\\Viper" & vipernumber & "\c$\WINSLA\Output\BuildLo
Workbooks.OpenText Filename:=ViperFilename, _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 3), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1))
Windows("BuildLog.txt").Ac
End Sub
Sub Tranfer()
Dim Sheetnum As Integer
Dim Sheet_Count As Integer
Dim Sheetnum_2 As Integer
Application.DisplayAlerts = False
Sheetnum_2 = 2
If vipernumber = 1 Then
Workbooks.Open Filename:= _
"R:\Viper_Utilization\WORK
Windows("WORKING_BOOK.xls"
Sheet_Count = ActiveWorkbook.Sheets.Coun
If Sheet_Count = 1 Then
GoTo HERE
End If
For Sheetnum = 2 To ActiveWorkbook.Sheets.Coun
Sheets(Sheetnum_2).Select
ActiveWindow.SelectedSheet
'SendKeys "{ENTER}", True
Next Sheetnum
End If
HERE:
Windows("BuildLog.txt").Ac
Sheets("BuildLog").Select
Sheets("BuildLog").Copy After:=Workbooks("WORKING_
Windows("BuildLog.txt").Ac
ActiveWindow.Close
'Application.WindowState = xlMinimized
Sheets("BuildLog").Select
Sheets("BuildLog").Name = "Viper_" & vipernumber & "_Math"
ViperFilename = "\\Viper" & vipernumber & "\c$\WINSLA\Output\BuildLo
Workbooks.OpenText Filename:=ViperFilename, _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True
Comma:=True, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 3), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1))
Windows("BuildLog.txt").Ac
Windows("BuildLog.txt").Ac
Sheets("BuildLog").Select
Sheets("BuildLog").Copy After:=Workbooks("WORKING_
Windows("BuildLog.txt").Ac
ActiveWindow.Close
'Application.WindowState = xlMinimized
Sheets("BuildLog").Select
Sheets("BuildLog").Name = "Viper_" & vipernumber & "_Math_1"
Windows("WORKING_BOOK.xls"
Sheets("Viper_" & vipernumber & "_Math_1").Select
Columns("A:A").Select
Selection.Cut
Sheets("Viper_" & vipernumber & "_Math").Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "mm/dd/yy"
Sheets("Viper_" & vipernumber & "_Math_1").Select
ActiveWindow.SelectedSheet
Application.DisplayAlerts = True
Windows("Working_Book.xls"
End Sub
Sub Find_Date_Date()
Dim Check_Time As Date
Dim Sheetname As String
'Dim ViperNumber As Integer
Dim Count_Start As Integer
Dim Count_Final As Integer
Dim Max As Integer
Dim Status As Integer
Dim Temp_Start_Date As Date
Dim Temp_Final_Date As Date
Dim Check_Date As Date
Dim STOP_LOOP As Integer
Dim Max_Stop_LOOP As Integer
Dim iCount1 As Integer
Dim iCount2 As Integer
Dim Total_number_days As Double
Dim Start_Date_Math As Date
Dim Final_Date_Math As Date
Max_Stop_LOOP = 20
Count_Start = 1
'ViperNumber = 5
Windows("Working_Book.xls"
Sheets("Viper_" & vipernumber & "_Math").Select
Do
iCount1 = iCount1 + 1
Loop Until IsEmpty(Cells(iCount1, 1))
Max = iCount1
Status = 1
STOP_LOOP = 1
Sheetname = "Viper_" & vipernumber & "_Math"
'ViperSdate = "4/17/2005"
'ViperFdate = "4/24/2005"
Temp_Start_Date = ViperSdate
Temp_Final_Date = ViperFdate
Do
Do Until Check_Date = Temp_Start_Date
Check_Date = Worksheets(Sheetname).Cell
Count_Start = Count_Start + 1
If Count_Start = Max Then
Status = Status + 1
Exit Do
End If
Loop
If Status > 1 Then
Status = Status - 1
Temp_Start_Date = Temp_Start_Date + 1
Count_Start = 1
Check_Date = "1/05/1900"
STOP_LOOP = STOP_LOOP + 1
End If
If Check_Date = Temp_Start_Date Then GoTo ENDLOOP
If STOP_LOOP = Max_Stop_LOOP Then
MsgBox "Please try a different starting date"
Exit Sub
End If
Loop Until Status > 1
ENDLOOP:
If Temp_Start_Date <> ViperSdate Then
Windows("SLA_Viper_Macro.x
Worksheets("Math").Cells(2
Else
Windows("SLA_Viper_Macro.x
Worksheets("Math").Cells(2
Windows("Working_Book.xls"
End If
Status = 1
Count_Final = 1
STOP_LOOP = 1
'Find final date
Windows("Working_Book.xls"
Do
Do Until Check_Date = Temp_Final_Date
Check_Date = Worksheets(Sheetname).Cell
Count_Final = Count_Final + 1
If Count_Final = Max Then
Status = Status + 1
Exit Do
End If
Loop
If Status > 1 Then
Status = Status - 1
Temp_Final_Date = Temp_Final_Date + 1
Count_Final = 1
Check_Date = "1/05/1900"
STOP_LOOP = STOP_LOOP + 1
End If
If Check_Date = Temp_Final_Date Then GoTo ENDLOOP1
If STOP_LOOP = Max_Stop_LOOP Then
MsgBox "Please try a different final date"
Exit Sub
End If
Loop Until Status > 1
ENDLOOP1:
If Temp_Final_Date <> ViperFdate Then
Windows("SLA_Viper_Macro.x
Worksheets("Math").Cells(3
Else
Windows("SLA_Viper_Macro.x
Worksheets("Math").Cells(3
Windows("Working_Book.xls"
End If
iCount2 = Count_Final - 1
'Find the final end date in the list
Windows("Working_Book.xls"
Check_Date = Worksheets(Sheetname).Cell
Do Until Check_Date <> Temp_Final_Date
Check_Date = Worksheets(Sheetname).Cell
iCount2 = iCount2 + 1
Loop
Count_Final = iCount2
Windows("SLA_Viper_Macro.x
Start_Date_Math = Worksheets("Math").Cells(2
Final_Date_Math = Worksheets("Math").Cells(3
Total_number_days = Final_Date_Math - Start_Date_Math
Worksheets("Math").Cells(8
Worksheets("Math").Cells(4
Worksheets("Math").Cells(5
End Sub
I hope that is enough code if not i can copy the rest of it. Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It doesnt hang up until after i finish the macro. Then the system is slow and laggy until I stop/shut down excel and re-open excel. Any idea if i am leaving some network connection open.
Thanks
Mike
Thanks
Mike
At the end of you code try this
Dim wkb As Workbook
For Each wkb In Workbooks
DEbug.Print wkb.Name
Next
Lets see if any of them are still open.
Leon
Dim wkb As Workbook
For Each wkb In Workbooks
DEbug.Print wkb.Name
Next
Lets see if any of them are still open.
Leon
ASKER
I added in above code and it still lags. Do i have to do that for each open wookbook? I do have 3 wookbook opened. I also have this same probelm on Win-2000 and wind-xp computers. I tested it again on win-2000.
Thanks
Mike
Thanks
Mike
The code above will only show open workbooks in your debug window. Take alook there if there are still workbooks which are open.
Leon
Leon
ASKER
It doesnt appear to me that there is any open workbooks other then the 3 i have open. I hope that helps.
Mike
Mike