Solved

Need to display part of a string in flexgrid

Posted on 2014-12-10
16
90 Views
Last Modified: 2014-12-11
I have an msflexgrid on my project within a form called Blocked Sites.  If you select a date range, it displays the sites within the grid, but the problem is that the content looks confusing.  I only need the information to the right of the :  on each record so that only the website is displayed.

Is it possible to edit the string to display only this information in the flexgrid?
CB.zip
0
Comment
Question by:al4629740
  • 8
  • 8
16 Comments
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40492874
Private Sub Command2_Click()
Dim iFiles As Long, sFirstFile$, sLastFile$, sFile$
Dim f As Integer
Dim strLine As String
Dim lngLines As Long
Dim arrKeys() As String
Dim bFound As Boolean
Dim bFirst As Boolean
Dim intCount As Integer
Dim strParts() As String
Dim intTot As Integer
Dim intPos As Integer
Dim strText As String

bFirst = True
f = FreeFile

MSFlexGrid1.Cols = 3
MSFlexGrid1.ColWidth(0) = 800
MSFlexGrid1.ColWidth(2) = 1200
MSFlexGrid1.ColWidth(1) = MSFlexGrid1.Width - (MSFlexGrid1.ColWidth(0) + MSFlexGrid1.ColWidth(2) + 360)
MSFlexGrid1.FixedCols = 0
MSFlexGrid1.FixedRows = 1
MSFlexGrid1.TextMatrix(0, 0) = "#Blocked"
MSFlexGrid1.TextMatrix(0, 1) = "Site"
MSFlexGrid1.TextMatrix(0, 2) = "IP Address"

sFirstFile = Format(DTPicker1.Value, "yymmdd")  'May 22, 2014  = 140522
sLastFile = Format(DTPicker2.Value, "yymmdd")   'June 15, 2014 = 140615
intCount = 0
ReDim arrKeys(1, 0)
For iFiles = Val(sFirstFile) To Val(sLastFile)  'loop will proceed if starting number <= ending number
   ' this is required since we're reading the YY first...
   sFile = Right(iFiles, 4) & Left(iFiles, 2)     'take the YY and put it at the end as in 140522 to 052214
   '// now construct the current filename FQN
   sFile = App.Path & "\test\" & sFile & ".txt"
   '// skip to the next file loop if current file does not exist
    
      If (Dir(sFile) <> "") Then
      Open sFile For Input As #f
      Do Until EOF(f)
         Line Input #f, strLine
         bFound = False
         If InStr(strLine, "BlockedIP") > 0 Then
            intTot = intTot + 1
            strParts = Split(strLine, "keyword")
            For lngLines = 0 To intCount - 1
               If arrKeys(1, lngLines) = strParts(1) Then
                  arrKeys(0, lngLines) = arrKeys(0, lngLines) + 1
                  bFound = True
                  Exit For
               End If
            Next
            If Not bFound Then
               If Not bFirst Then
                  ReDim Preserve arrKeys(1, intCount)
               End If
               arrKeys(1, intCount) = strParts(1)
               arrKeys(0, intCount) = 1
               bFirst = False
               intCount = intCount + 1
            End If
         End If
      Loop
      Close
   End If
Next iFiles

For lngLines = 0 To intCount - 1
    MSFlexGrid1.TextMatrix(lngLines + 1, 0) = arrKeys(0, lngLines)
    strText = Split(arrKeys(1, lngLines), " -> ")(0)
    intPos = InStr(1, strText, ":")
    MSFlexGrid1.TextMatrix(lngLines + 1, 1) = Mid$(strText, intPos + 2)
    MSFlexGrid1.TextMatrix(lngLines + 1, 2) = Split(arrKeys(1, lngLines), " -> ")(1)
    MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
Next

MSFlexGrid1.Rows = MSFlexGrid1.Rows - 1

MSFlexGrid1.Col = 2
MSFlexGrid1.ColSel = 0
MSFlexGrid1.Sort = flexSortGenericDescending


'Debug.Print "Total number found " & intTot
'Debug.Print "Total reported "; intIW
End Sub

Open in new window


Entries like the following wind up like 31.media.tumblr.com (right-justified because it starts with numbers)
BlockedIP response sent, keyword tumblr.com: 31.media.tumblr.com. -> 192.168.1.100

In cases like that would you like the .31 removed?
0
 

Author Comment

by:al4629740
ID: 40492914
it might be good if we can consolidate the number of times of the media.tumblr.com  into one.  for example

5     31.media.tumblr.com  192.168.1.100
2     24.media.tumblr.com  192.168.1.100  
4     42.media.tumblr.com   192.168.1.100

would translate to:

11   media.tumblr.com  192.168.1.100
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40492928
Is it possible that there could be other sites like that or is it specific to media.tumblr?
0
 

Author Comment

by:al4629740
ID: 40492936
I believe it is entirely possible for a site to start with a number, so we may want to stick with the format below...unless you have any other ideas

4     42.media.tumblr.com   192.168.1.100
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40492962
Well what you propose is certainly the easiest since that's what it does now, but another alternative is to summarize all sites that start with numbers into sets. In other words if the input contained

5     31.media.tumblr.com  192.168.1.100
2     24.media.tumblr.com  192.168.1.100  
4     42.media.tumblr.com  192.168.1.100
1     11.blah.yada.com  192.123.1.100
2     22.blah.yada.com  192.123.1.100  
3     33.blah.yada.com  192.123.1.100

the grid would show
11   media.tumblr.com  192.168.1.100
6   blah.yada.com   192.123.1.100
0
 

Author Comment

by:al4629740
ID: 40492976
I prefer the second one:

11   media.tumblr.com  192.168.1.100
6   blah.yada.com   192.123.1.100


However, what would happen if the site's name was   123.com   or 123.test.com
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40493020
Good catch, that would be a problem. However if you want there could be an internal "summarization" table that would indicate which sites should be summarized, and for now it would contain only "media.tumblr.com". In the future (in another question) a method for you to maintain that table could be implemented.

Let me know if for now you want me to summarize  "media.tumblr.com".
0
 

Author Comment

by:al4629740
ID: 40493021
I think it would be good not to summarize at this point due to the fact it will require too much unnecessary tracking.  If it appears that too many of those sites get "out of control" then maybe later.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 46

Expert Comment

by:Martin Liss
ID: 40493041
OK so is there  anything else you need here?
0
 

Author Comment

by:al4629740
ID: 40493046
Yes, but its not related to this question.  I'll post it later.  It has to do with if I choose a long date range I get an error.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40493194
OK, so please don't forget to accept my answer here.
0
 

Author Closing Comment

by:al4629740
ID: 40493747
Thought i graded already.  sorry
0
 

Author Comment

by:al4629740
ID: 40493786
One more thing.  Some of the records are not left justified.  How do I get all the records to be left justified in the grid?  New Q?
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40494323
They are the ones that start with numbers so they are normally right-justified, but I'll see what I can do.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40494422
I added lines 76 to 78.
Private Sub Command2_Click()
Dim iFiles As Long, sFirstFile$, sLastFile$, sFile$
Dim f As Integer
Dim strLine As String
Dim lngLines As Long
Dim arrKeys() As String
Dim bFound As Boolean
Dim bFirst As Boolean
Dim intCount As Integer
Dim strParts() As String
Dim intTot As Integer
Dim intPos As Integer
Dim strText As String

bFirst = True
f = FreeFile

MSFlexGrid1.Cols = 3
MSFlexGrid1.ColWidth(0) = 800
MSFlexGrid1.ColWidth(2) = 1200
MSFlexGrid1.ColWidth(1) = MSFlexGrid1.Width - (MSFlexGrid1.ColWidth(0) + MSFlexGrid1.ColWidth(2) + 360)
MSFlexGrid1.FixedCols = 0
MSFlexGrid1.FixedRows = 1
MSFlexGrid1.TextMatrix(0, 0) = "#Blocked"
MSFlexGrid1.TextMatrix(0, 1) = "Site"
MSFlexGrid1.TextMatrix(0, 2) = "IP Address"

sFirstFile = Format(DTPicker1.Value, "yymmdd")  'May 22, 2014  = 140522
sLastFile = Format(DTPicker2.Value, "yymmdd")   'June 15, 2014 = 140615
intCount = 0
ReDim arrKeys(1, 0)
For iFiles = Val(sFirstFile) To Val(sLastFile)  'loop will proceed if starting number <= ending number
   ' this is required since we're reading the YY first...
   sFile = Right(iFiles, 4) & Left(iFiles, 2)     'take the YY and put it at the end as in 140522 to 052214
   '// now construct the current filename FQN
   sFile = App.Path & "\test\" & sFile & ".txt"
   '// skip to the next file loop if current file does not exist
    
      If (Dir(sFile) <> "") Then
      Open sFile For Input As #f
      Do Until EOF(f)
         Line Input #f, strLine
         bFound = False
         If InStr(strLine, "BlockedIP") > 0 Then
            intTot = intTot + 1
            strParts = Split(strLine, "keyword")
            For lngLines = 0 To intCount - 1
               If arrKeys(1, lngLines) = strParts(1) Then
                  arrKeys(0, lngLines) = arrKeys(0, lngLines) + 1
                  bFound = True
                  Exit For
               End If
            Next
            If Not bFound Then
               If Not bFirst Then
                  ReDim Preserve arrKeys(1, intCount)
               End If
               arrKeys(1, intCount) = strParts(1)
               arrKeys(0, intCount) = 1
               bFirst = False
               intCount = intCount + 1
            End If
         End If
      Loop
      Close
   End If
Next iFiles

For lngLines = 0 To intCount - 1
    MSFlexGrid1.TextMatrix(lngLines + 1, 0) = arrKeys(0, lngLines)
    strText = Split(arrKeys(1, lngLines), " -> ")(0)
    intPos = InStr(1, strText, ":")
    MSFlexGrid1.TextMatrix(lngLines + 1, 1) = Mid$(strText, intPos + 2)
    MSFlexGrid1.TextMatrix(lngLines + 1, 2) = Split(arrKeys(1, lngLines), " -> ")(1)
    'new
    MSFlexGrid1.Row = lngLines
    MSFlexGrid1.Col = 1
    MSFlexGrid1.CellAlignment = flexAlignLeftBottom 'flexAlignLeftCenter

    MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
Next

MSFlexGrid1.Rows = MSFlexGrid1.Rows - 1

MSFlexGrid1.Col = 2
MSFlexGrid1.ColSel = 0
MSFlexGrid1.Sort = flexSortGenericDescending


'Debug.Print "Total number found " & intTot
'Debug.Print "Total reported "; intIW
End Sub

Open in new window

0
 

Author Comment

by:al4629740
ID: 40495510
Excellent.  Thank you!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now