| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
01-27-05, 15:39
|
|
Registered User
|
|
Join Date: Sep 2002
Location: NJ
Posts: 139
|
|
|
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
|
|
01-27-05, 15:40
|
|
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)
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
|
|
01-27-05, 16:29
|
|
Registered User
|
|
Join Date: Oct 2003
Posts: 1,091
|
|
|
|
Quote:
|
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.
|
|
01-28-05, 07:18
|
|
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
|
|
01-28-05, 14:33
|
|
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
'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
|
|
01-28-05, 14:38
|
|
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
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
performance and additions
