1. Registered User
Join Date
Sep 2002
Location
NJ
Posts
139

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```

2. Registered User
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)

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
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
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("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. Registered User
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.

4. Registered User
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

5. Registered User
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

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. Registered User
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
•