Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: performance and additions

    HI

    EXCEL2K
    WIN2K AND XP

    I HAVE 2 ?

    1. LOOK AT THE ATTACHED ( the other code will follow this post) . WHEN I CLICK ON THE EMPLOYEE SCHEDULE BUTTON, I SEE THE FORM IN THE ATTACHED. THEN I DOUBLE-LEFT CLICK ON
    THE NAME LIST AND I SHOWS ME THE ALL THE INFORMATION IT PICKS UP FROM A FLAT FILE IN A DIRECTORY. NOW THE PROBLEM IS THAT WHEN I CLOSE
    THE EXCEL APP AND THEN OPEN IT, IT TAKES LESS THAN A SECON TO FOR THE INFORMATION TO BE PICKED UP, PARSED AND THEN PUT INTO SHEET7 AND FINALLY TO BE LISTED MY FORM. OTHER TIMES IT TAKES ALMOST 15 SECONDS TO DO THE SAME THING. IS THERE A WAY TO REMEDY THIS PROBLEM, KNOWING THAT THERE IS NO PROBLEM ON THE NETWORK AND THE APP'S MACROS ARE NOT RUNING?

    2, WHEN ADDING TWO VALUE, LIKE MyRVD - MyVaccationDay, the balance is negative. I want it to be positeve instead. How can I do that?


    HERE IS THE CODE WHEN I DOUBLE

    Code:
    Sub ParseLine(ByVal Text As String, ByRef Data() As Variant)
    On Error Resume Next
        Dim Pos1 As Integer
        Dim Pos2 As Integer
        Dim i As Integer
        
        Pos1 = InStr(Text, "|")
        If Pos1 = 0 Then Exit Sub
        
        Data(0) = Left(Text, Pos1 - 1)
        i = 1
        Pos2 = InStr(Pos1 + 1, Text, "|")
            
        Do Until Pos1 = 0
            
            If Pos2 = 0 Then 'Pos2 = Len(Text) + 1
                Data(i) = Mid(Text, Pos1 + 1)
            Else
                Data(i) = Mid(Text, Pos1 + 1, Pos2 - 1 - Pos1)
            End If
            
            Pos1 = Pos2
            Pos2 = InStr(Pos1 + 1, Text, "|")
            i = i + 1
            If i > UBound(Data) Then Exit Sub
        Loop
    End Sub
    Attached Thumbnails Attached Thumbnails EXAMPLE.bmp  

  2. #2
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    following code

    Code:
    Sub test()
     Application.ScreenUpdating = False
     Sheet3.Activate
     
        Dim MyCells As Range
            For Each MyCells In Sheet3.Range("D2:O32")
                MyCells.Clear
            Next
     Dim TextLIne As Variant
     Dim hFile As Integer
     Dim sFilename As String
     Dim i, j As Integer
     Dim myvar As String
     
                              
     hFile = FreeFile
                'sFilename = "Z:\USERSTATS\TEST.txt"
                sFilename = "Z:\USERSTATS\TEST.txt"
                    Open sFilename For Input As #hFile
                     Line Input #1, TextLIne
                     Do While Not EOF(1)
                     
                        Dim UserName As Integer
                        For UserName = 1 To 10
                               Line Input #1, TextLIne
                                                   
                               Dim sp1, strlen As Integer
                               Dim one, two As String
                               '*********
                               sp1 = InStr(1, TextLIne, "|")
                               one = Left(TextLIne, sp1 - 1)
                               strlen = Len(TextLIne)
                               two = Mid(TextLIne, sp1 + 1, strlen - sp1)
                               
                               If UCase(one) <> Sheet3.Range("A" & frmNames.lstName.List(frmNames.lstName.ListIndex, 1)).Value Then
                                   Exit For
                               Else
                                frmNames.txtName = Sheet3.Range("A" & frmNames.lstName.List(frmNames.lstName.ListIndex, 1)).Value
                                frmNames.txtDate = Format(Date, "MM/DD/YYYY")
                                frmNames.lblUserName.Caption = "User Schedule for: " & Sheet3.Range("A" & frmNames.lstName.List(frmNames.lstName.ListIndex, 1)).Value
                               End If
                                                   
                               ParseLine TextLIne, Data()
                                                  
                               Dim l, MyMonth As Integer
                               Dim month As String
                               
                               j = 4   'column
                               l = 1  'row
                               
                               'For i = 1 To 365
                               For i = 1 To 373
    nexti:
                                   If Data(i) = "" Or Data(i) <> "" Then
                                        
                                        If i >= 366 Then
                                            If i = 366 Then
                                                MyRPD = Data(i)
                                                i = i + 1
                                                GoTo nexti
                                            ElseIf i = 367 Then
                                                MyRPD = Data(i)
                                                i = i + 1
                                                GoTo nexti
                                            ElseIf i = 368 Then
                                                MyRSD = Data(i)
                                                i = i + 1
                                                GoTo nexti
                                            ElseIf i = 369 Then
                                                MyRVD = Data(i)
                                                i = i + 1
                                                GoTo nexti
                                            ElseIf i = 370 Then
                                                frmNames.txtAddress.Value = Data(i)
                                                i = i + 1
                                                GoTo nexti
                                            ElseIf i = 371 Then
                                                frmNames.txtCityStatZip.Value = Data(i) 'this field does not accept comma's
                                                i = i + 1
                                                GoTo nexti
                                            ElseIf i = 372 Then
                                                frmNames.txtHomePhone.Value = Data(i)
                                                i = i + 1
                                                GoTo nexti
                                            ElseIf i = 373 Then
                                                frmNames.txtEmergencyContact.Value = Data(i)
                                                'i = i + 1
                                                Exit For
                                            End If
                                                
                                        End If
                                   'Else
                                       month = Sheet3.Cells(l, j).Value
                                       If Sheet3.Cells(l, j).Value = "JAN" Or _
                                          Sheet3.Cells(l, j).Value = "MAR" Or _
                                          Sheet3.Cells(l, j).Value = "MAY" Or _
                                          Sheet3.Cells(l, j).Value = "JUL" Or _
                                          Sheet3.Cells(l, j).Value = "AUG" Or _
                                          Sheet3.Cells(l, j).Value = "OCT" Or _
                                          Sheet3.Cells(l, j).Value = "DEC" Then
                                               MyMonth = 33
                                       End If
                                       If Sheet3.Cells(l, j).Value = "FEB" Then
                                               MyMonth = 30
                                       End If
                                       If Sheet3.Cells(l, j).Value = "APR" Or _
                                           Sheet3.Cells(l, j).Value = "JUN" Or _
                                           Sheet3.Cells(l, j).Value = "SEP" Or _
                                           Sheet3.Cells(l, j).Value = "NOV" Then
                                               MyMonth = 32
                                       End If
                                           
                                       Sheet3.Cells(l, j).Select
                                       If Sheet3.Cells(l, j).Value = "JAN" Or _
                                            Sheet3.Cells(l, j).Value = "FEB" Or _
                                            Sheet3.Cells(l, j).Value = "MAR" Or _
                                            Sheet3.Cells(l, j).Value = "APR" Or _
                                            Sheet3.Cells(l, j).Value = "MAY" Or _
                                            Sheet3.Cells(l, j).Value = "JUN" Or _
                                            Sheet3.Cells(l, j).Value = "JUL" Or _
                                            Sheet3.Cells(l, j).Value = "AUG" Or _
                                            Sheet3.Cells(l, j).Value = "SEP" Or _
                                            Sheet3.Cells(l, j).Value = "OCT" Or _
                                            Sheet3.Cells(l, j).Value = "NOV" Or _
                                            Sheet3.Cells(l, j).Value = "DEC" Then
                                                ActiveCell.Offset(1, 0).Select
                                                l = l + 1
                                            Sheet3.Cells(l, j).Value = Data(i)
                                            
                                       Else
                                           Sheet3.Cells(l, j).Value = Data(i)
                                           If Sheet3.Cells(l, j).Value = "W" Then
                                               Sheet3.Cells(l, j).Font.Bold = True
                                           End If
                                           If Sheet3.Cells(l, j).Value = "S" Then
                                               MySickDAy = MySickDAy + 1
                                           ElseIf Sheet3.Cells(l, j).Value = "P" Then
                                               MyPersonalDay = MyPersonalDay + 1
                                           ElseIf Sheet3.Cells(l, j).Value = "V" Then
                                               MyVaccationDay = MyVaccationDay + 1
                                           ElseIf Sheet3.Cells(l, j).Value = "C" Then
                                               MyCompDay = MyCompDay + 1
                                           ElseIf Sheet3.Cells(l, j).Value = "NP" Then
                                               MyNonPayDay = MyNonPayDay + 1
                                           End If
                                       End If
                                       l = l + 1
                                       If l = MyMonth Then
                                           j = j + 1
                                           l = 1
                                       End If
                                       
                                   End If
    
                               Next i
                               Next UserName
                            Loop
                        Close #1
            '           Exit For
     
    
    'Call write_to_personal_info
    Application.ScreenUpdating = True
    Sheet3.Range("D35").Value = MySickDAy
    Sheet3.Range("E35").Value = MySickDAy
    Sheet3.Range("E35").Value = MyRSD - MySickDAy
    Sheet3.Range("F35").Value = MyPersonalDay
    Sheet3.Range("G35").Value = MyPersonalDay
    Sheet3.Range("G35").Value = MyPersonalDay - MyRPD
    Sheet3.Range("H35").Value = MyVaccationDay
    Sheet3.Range("I35").Value = MyVaccationDay - MyRVD
    Sheet3.Range("J35").Value = MyCompDay
    Sheet3.Range("K35").Value = MyCompDay - MyRCD
    Sheet3.Range("L35").Value = MyAbscentDay
    Sheet3.Range("M35").Value = MyRAD
    Sheet3.Range("N35").Value = MyNonPayDay
    frmNames.txtSickday.Text = MySickDAy
    frmNames.txtVacationd.Text = MyVaccationDay
    frmNames.txtPersonalDays.Text = MyPersonalDay
    frmNames.txtPersonalRPD.Text = MyPersonalDay - MyRPD
    frmNames.txtSickRSD.Text = MyRSD - MySickDAy
    frmNames.txtVacationRVD.Text = MyVaccationDay - MyRVD
    
    frmNames.Repaint
    
    Sheet1.Activate
    
    End Sub

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    MyRVD - MyVaccationDay, the balance is negative. I want it to be positeve instead
    Not sure if I understand fully. But you could just reverse the two items.

    MyVaccationDay - MyRVD

    Or if you want it this way, then use ABS function.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi alex ive had a quick look at your code and modified it
    i havnt tested it yet so there will still be the debugging to do

    see if this is a bit faster

    HTH Dave
    Attached Files Attached Files

  5. #5
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139
    david

    i HAVE TYPE MISMATCH ERROR AT MAXLINE AT THIS LINE IN YOUR CODE:

    If Not MaxLines Is Nothing Then

    WHILE DEBUGGING.

    I AM NOT VERY CLEAR ON OBJECT ORIENTED PROGRAMMING, THOUGH I UNDERSTAND WHERE YOU ARE GOING WITH THIS.


    Code:
    Private Sub ReadText()
    'read text from a text file and outputs as an integer
    Dim num As Integer, i As Integer
    Dim TextLine As String
        
        num = FreeFile
        
        Open FromTo For Input As #num
            'check to see if there is a set number of lines to read
            If Not MaxLines Is Nothing Then
                   
                'if yes read max lines
                For i = 1 To MaxLines
                    Line Input #num, TextLine
                    strNote = strNote & Chr(10) & TextLine
                Next i
            Else
                'if not read all of file
                Do While Not EOF(num)
                    Line Input #num, TextLine
                    strNote = strNote & Chr(10) & TextLine
                Loop
            End If
        Close #num
    
    End Sub

  6. #6
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    ok i dont have excel here lets try a couple of things

    try isempty(maxlines) or maxlines =0 or similar

    or else just get rid of the if block as we are passng maxlines anyway
    as i said i hadnt debugged so you may get a few of these
    Dave

Posting Permissions

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