Input from form into code.

Poco_90 · Jan 20, 2014

Hi ,
I have being requested to create a from that displays the number of files in different folders. I can accomplish this somewhat with the code below. In the example below it is only showing the code for the first 3 folders. I have 7 folders in total to count.

I have 2 problems that I hope you can help me with?

1. Each week the location of the data will change. In the example below it is showing 2014-W03, the following week it will be 2014-W04 etc. etc. I was thinking of having an input box in the form that the user type the date into, to trigger the code, but how would I go about changing the code to include this variable?
2. I need to repeat this code for 9 other products, so my form will be displaying data for 10 products in total. I know I could copy this code 9 times and modify it accordingly, but is it possible to loop it in some way?

Thank in advance,
Poco

Code:

' Returns the total number of fails
Private Sub Form_Load()
Dim file As Object
Dim fileCount As Long

With CreateObject("Scripting.FileSystemObject").getfolder("\\testmachine\Product1\ FAILS\2014-W03")
     
    For Each file In .Files
         
        If file.Name Like "*.*" Then
             
            fileCount = fileCount + 1
        End If
    Next file
End With

 Me.FailTotal = fileCount

' Returns the total number of passes
Dim file2 As Object
Dim fileCount2 As Long

With CreateObject("Scripting.FileSystemObject").getfolder("\\testmachine\Product1\ PASS\2014-W03")
     
    For Each file2 In .Files
         
        If file2.Name Like "*.*" Then
             
            fileCount2 = fileCount2 + 1
        End If
    Next file2
End With

 Me.Total = fileCount2

' Returns the total number of fail1 fails
Dim filef1 As Object
Dim fileCountf1 As Long

With CreateObject("Scripting.FileSystemObject").getfolder("\\testmachine\Product1\ FAILS\2014-W03")
     
    For Each filef1 In .Files
         
        If filef1.Name Like "*FAIL1*" Then
             
            fileCountf1 = fileCountf1 + 1
        End If
    Next filef1
End With

 Me.Energise = fileCountf1

pr2-eugin · Jan 20, 2014

Poco_90, Allen Browne has a neat little functionality for this called "List Files recursively", that will go through all folders and count. You can use that 9 times 20 times as much as you want. That is the beauty of Code re-useability. If you have any problem understanding that post back.

nanscombe · Jan 20, 2014

If you've got blocks of code that repeat, as in your code above, it could be turned into a Sub (if it doesn't return a value) or a Function (if it does return values) in its own right.

As you want to return a count I would create a Function.

The main difference to each block of code is the path and wildcard so I would pass them as parameters.

Code:

Private Function getFileCount(byVal gfcPath as String, byVal gfcWildcard as String)
Dim file As Object
Dim fileCount As Long

  fileCount = 0

  With CreateObject("Scripting.FileSystemObject").getfolder(gfcPath)
     
      For Each file In .Files
         
          If file.Name Like gfcWildcard Then
             
              fileCount = fileCount + 1

          End If

      Next file

  End With

  getFileCount = fileCount
End Function

Now that the code is available as a Function you can call it multiple times quite easily.

Code:

  Me.FailTotal = getFileCount("\\testmachine\Product1\ FAILS\2014-W03", "*.*")
  Me.Total = getFileCount("\\testmachine\Product1\ PASS\2014-W03", "*.*")
  Me.Energise = getFileCount("\\testmachine\Product1\ FAILS\2014-W03", "*FAIL1*")

namliam · Jan 20, 2014

Why not calculate the weeknumber on the fly with something like:

Code:

format(date(), "YYYY") & "-W" & right("0" & format(date(), "WW"), 2)

You would include the weeknumber here and thus:

Code:

With CreateObject("Scripting.FileSystemObject").getfolder("\\testmachine\Product1\ FAILS\[COLOR="DarkRed"]" & WEEKNUMBER[/COLOR])

Thru any means you see fit either above format code or thru input from the user.

I would create a function, like so:

Code:

Function fReturnFolderCount(UNCPath As String) As Double
    Dim fso As FileSystemObject
    Dim F As Files
    Set fso = CreateObject("Scripting.FileSystemObject")
    fReturnFolderCount = fso.GetFolder("D:\").Files.Count
    Set fso = Nothing
End Function

That returns the number of files in the folder

And I would probably make a table containing 3 columns for Product SubFolder and Control or perhaps 2 columns, Path (without week) and Control
Then loop the table thru the function to write the returns into the proper controls

Code:

Me.Controls(Controlenamefromtable) = fReturnFolderCount(PathfromTable & Weeknumber)

Galaxiom · Jan 20, 2014

It is possible to use a file selection dialogue for them to choose the file directly from Windows or from the Office file dialog.

This is the Office file dialog. The folder version might be a click away.

nanscombe · Jan 20, 2014

Since the paths are similar too you could do this.

Code:

Dim fileRoot as String, weekNo as Long, theYear as Long

  fileRoot = "\\testmachine\Product1\"
  theYear = Format(now(), "YYYY")

  ' Prompt for a weekNo
  weekNo = nz(InputBox("Enter a week no between 1 and 52"), 0)

  ' Traps the Week No, but could be more elegant
  If (WeekNo >=1) AND (WeekNo <=52) Then

    Me.FailTotal = getFileCount(fileRoot & "FAILS\" & theYear & "-W" & Format(weekNo,"00"), "*.*")
    Me.Total = getFileCount(fileRoot & "PASS\" & theYear & "-W" & Format(weekNo,"00"), "*.*")
    Me.Energise = getFileCount(fileRoot & "FAILS\" & theYear & "-W" & Format(weekNo,"00"), "*FAIL1*")

  Endif

nanscombe · Jan 20, 2014

An improvement to the code ...

Code:

Private Function getFileCount(byVal gfcPath as String, byVal gfcWildcard as String)
Dim file As Object
Dim fileCount As Long

  fileCount = 0

  With CreateObject("Scripting.FileSystemObject").getfolder(gfcPath)

    If gfcWildcard = "*.*" Then

  ' If wildcard is *.* just use .Files.Count
      fileCount = .Files.Count

    Else

  ' If wildcard is not *.* loop through to count matching files
      For Each file In .Files
         
        If file.Name Like gfcWildcard Then
             
          fileCount = fileCount + 1

        End If

      Next file

    Endif

  End With

  getFileCount = fileCount
End Function

Poco_90 · Jan 20, 2014

Thanks for all your detailed replies. Just when i start getting places, my brief is changed. Now I have to send a daily report on the file counts of the previous day. Thanks for all your inputs.
Poco

Poco_90 · Feb 13, 2014

Namliam, your code is beyond me and I tried to get it to work but couldn't. I don't know how to jump between or combine functions, no mind linking it to tables with file paths etc. so I have to give up on your method.

I have Nigel's code working only because Nigel spoon fed me. What I would like to do is modify Nigel's code to automatically calculate the week number on the fly as Namliam suggested. I tried merging both pieces of code, but unfortunately the computer says no

. How would I accomplish this with the below code

Code:

 [SIZE=3][FONT=Calibri]Private Sub Form_Load()[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]Dim fileRoot As String, weekNo As Long, theYear As Long[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]  fileRoot = "\\TestPC\c\ DATA\Product1\"[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]  theYear = Format(Now(), "YYYY")[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]  ' Prompt for a weekNo[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]  weekNo = Nz(InputBox("Enter a week no between 1 and 52"), 0)[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]  ' Traps the Week No, but could be more elegant[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]  If (weekNo >= 1) And (weekNo <= 52) Then[/FONT][/SIZE]
 
 
 [SIZE=3][FONT=Calibri]    Me.FailTotal = getFileCount(fileRoot & "TRAFILES_FAILS\" & theYear & "-W" & Format(weekNo, "00"), "*.*")[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]    Me.Total = getFileCount(fileRoot & "TRAFILES\" & theYear & "-W" & Format(weekNo, "00"), "*.*")[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]    Me.Force = getFileCount(fileRoot & "TRAFILES_FAILS\" & theYear & "-W" & Format(weekNo, "00"), "*FAIL2*")[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]    Me.Energise = getFileCount(fileRoot & "TRAFILES_FAILS\" & theYear & "-W" & Format(weekNo, "00"), "*FAIL1*")[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]    Me.Hystersys = getFileCount(fileRoot & "TRAFILES_FAILS\" & theYear & "-W" & Format(weekNo, "00"), "*FAIL3*")[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]    Me.Bernuli = getFileCount(fileRoot & "TRAFILES_FAILS\" & theYear & "-W" & Format(weekNo, "00"), "*FAIL4*")[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]    Me.Slope = getFileCount(fileRoot & "TRAFILES_FAILS\" & theYear & "-W" & Format(weekNo, "00"), "*FAIL5*")[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]   Me.CountDate = weekNo & theYear[/FONT][/SIZE]
 
 
 
 [SIZE=3][FONT=Calibri]  End If[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]End Sub[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]Private Function getFileCount(ByVal gfcPath As String, ByVal gfcWildcard As String)[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Dim file As Object[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Dim fileCount As Long[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]  fileCount = 0[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]  With CreateObject("Scripting.FileSystemObject").GetFolder(gfcPath)[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]      For Each file In .Files[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]          If file.Name Like gfcWildcard Then[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]              fileCount = fileCount + 1[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]          End If[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]      Next file[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]  End With[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]  getFileCount = fileCount[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]End Function[/FONT][/SIZE]

My other issue would be, how do I repeat this code several times for each product? I tried to double it up but I am getting errors. My aim is to get this code to run on a daily basis and record all the data into a table, and use the data in the table for my daily report. Thanks in advance,
Poco

namliam · Feb 13, 2014

WeekNo = right("0" & format(date(), "WW"), 2)

That what you are looking for?

Me.CountDate = weekNo & theYear
I wouldnt do this thisway, this is going to cause problems in your ordering if you ever need to order by this field

Poco_90 · Feb 13, 2014

Hi Namliam,
That is perfect, thank you. How can I repeat this code 10 times in the one form for each product? Thanks,
Poco

pr2-eugin · Feb 13, 2014

You can use For..Next? http://www.databison.com/vba-for-loop-for-next-and-for-each-in-next/

Input from form into code.

Poco_90

Registered User.

pr2-eugin

Super Moderator

nanscombe

Registered User.

namliam

The Mailman - AWF VIP

Galaxiom

Super Moderator

nanscombe

Registered User.

nanscombe

Registered User.

Poco_90

Registered User.

Poco_90

Registered User.

namliam

The Mailman - AWF VIP

Poco_90

Registered User.

pr2-eugin

Super Moderator

Similar threads

Users who are viewing this thread