Link to home
Start Free TrialLog in
Avatar of Mirdurern
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!
Avatar of leonstryker
leonstryker
Flag of United States of America image

How do you access the text files?
Avatar of Mirdurern
Mirdurern

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
Avatar of Amro Osama
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
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
Can we see some code please.
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_Finish 'Mod13
   
    Call Viper_Time_Per_Period 'Mod16
   

    Windows("SLA_Viper_Macro.xls").Activate

    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.xls").Activate
    ActiveWorkbook.Save
   

    Windows("WORKING_BOOK.xls").Activate
    ActiveWorkbook.Save
    ActiveWindow.Close
   
    Windows("SLA_Viper_Macro.xls").Activate
   
   
    Final_Time_c = Now
   
    Worksheets("Input_Data").Cells(10, "B").Value = Start_Time_c
    Worksheets("Input_Data").Cells(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\BuildLog.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").Activate
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\WORKING_BOOK.xls"
                               
                                Windows("WORKING_BOOK.xls").Activate
                           
                                Sheet_Count = ActiveWorkbook.Sheets.Count
                                        If Sheet_Count = 1 Then
                                        GoTo HERE
                                        End If
                                  For Sheetnum = 2 To ActiveWorkbook.Sheets.Count
                                       
                                        Sheets(Sheetnum_2).Select
                                        ActiveWindow.SelectedSheets.Delete
                               
                                        'SendKeys "{ENTER}", True
                                    Next Sheetnum
                    End If

HERE:


    Windows("BuildLog.txt").Activate
    Sheets("BuildLog").Select
    Sheets("BuildLog").Copy After:=Workbooks("WORKING_BOOK.xls").Sheets(1)
    Windows("BuildLog.txt").Activate
    ActiveWindow.Close
    'Application.WindowState = xlMinimized
    Sheets("BuildLog").Select
    Sheets("BuildLog").Name = "Viper_" & vipernumber & "_Math"
   
   
    ViperFilename = "\\Viper" & vipernumber & "\c$\WINSLA\Output\BuildLog.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").Activate
 
    Windows("BuildLog.txt").Activate
    Sheets("BuildLog").Select
    Sheets("BuildLog").Copy After:=Workbooks("WORKING_BOOK.xls").Sheets(1)
    Windows("BuildLog.txt").Activate
    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.SelectedSheets.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).Cells(Count_Start, "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.xls").Activate
        Worksheets("Math").Cells(2, 1 + vipernumber).Value = Temp_Start_Date
       
        Else
       
        Windows("SLA_Viper_Macro.xls").Activate
        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).Cells(Count_Final, "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.xls").Activate
        Worksheets("Math").Cells(3, 1 + vipernumber).Value = Temp_Final_Date
        Else
        Windows("SLA_Viper_Macro.xls").Activate
        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).Cells(Count_Final, "A").Value
               
               
    Do Until Check_Date <> Temp_Final_Date
        Check_Date = Worksheets(Sheetname).Cells(iCount2, "A").Value
        iCount2 = iCount2 + 1

    Loop

Count_Final = iCount2
       
       
   
        Windows("SLA_Viper_Macro.xls").Activate
       
        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




ASKER CERTIFIED SOLUTION
Avatar of leonstryker
leonstryker
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
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
The code above will only show open workbooks in your debug window.  Take alook there if there are still workbooks which are open.

Leon
It doesnt appear to me that there is any open workbooks other then the 3 i have open.  I hope that helps.

Mike