• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 494
  • Last Modified:

Count Files where...

Hello,

=====BACKGROUND======
I hold scans of documents sent to me by external companies, these scans are in a format of:

[Document Reference] - [Year] - [3 Digit Page Number] .pdf

eg:

12345678-2006-001.pdf, 12345678-2006-002.pdf, 12345678-2006-003.pdf, 12345678-2006-004.pdf, 12345678-2006-005.pdf

They are always numbered so that they go... 001, 002, 003, 004, 005, 006, 007, 008, 009, 010, 011, 012 etc...

=====WHAT I'VE GOT======
I have a set of variables:

ScanYear = 2006
ScanFormat = .pdf
ScanPath = C:\Scans\
ScanMaxDocs = 6 (this is currently the maxiumum pages to a series of documents)
ScanReference = 12345678

=====WHAT I'D LIKE======
I'd like to be able to take the variables and look for the scans... i.e.

[ScanPath] & [ScanReference] & "-" & [ScanYear] & "-" & {count between 1 & [ScanMaxDocs]} & [ScanFormat]

I'd like it to loop through and where visible put the documents into variables, so if there were 3 documents for 12345678 in year 2006, i'd have:

Scan1 = C:\Scans\12345678-2006-001.pdf
Scan2 = C:\Scans\12345678-2006-002.pdf
Scan3 = C:\Scans\12345678-2006-003.pdf

I hope this makes sense?

Many Thanks for any help offered.
0
ukkrew
Asked:
ukkrew
  • 5
  • 4
  • 2
2 Solutions
 
GrahamSkanCommented:
This will put the file names into an array instead of prenamed individual variables. That way you don't need to worry about a maximum number.

Private Sub CommandButton1_Click()
    Dim ScanYear As String
    Dim ScanFormat  As String
    Dim ScanPath  As String
    Dim ScanMaxDocs  As String
    Dim ScanReference As String
    Dim Scans() As String
    Dim strFileName As String
    Dim i As Integer
    Dim strScans() As String
   
    ScanYear = "2006"
    ScanFormat = ".pdf"
    ScanPath = "C:\Scans\"
    ScanMaxDocs = "6" '(this is currently the maxiumum pages to a series of documents)
    ScanReference = "12345678"
   
    strFileName = Dir$(ScanPath & ScanReference & "-" & ScanYear & "-" & "*" & ScanFormat)
    Do Until strFileName = ""
        ReDim Preserve Scans(i)
        i = i + 1
        strFileName = Dir$()
    Loop
    If i = 1 Then
        MsgBox "There is one file in the folder"
    Else
        MsgBox "There are " & i & "files in the folder"
    End If
End Sub

0
 
ukkrewAuthor Commented:
Thanks GrahamSkan,

That is fantastic!!

The next part is to get the files linked up to the 6 buttons on the form... when the form loads, all 6 buttons are made invisible, so that the correct number of buttons can be displayed. The 6 buttons are called... Scan1 => Scan6.

What I'd like is to only make the right number of buttons visible, then have the caption of the button to say the file name in assending order by file name, and then have path put into a variable, so that when the button is pressed, it'll be possible to launch the pdf in the pdf viewer.

Many thanks.
0
 
harris_cCommented:
Hi,

Building upon Graham's code:

1.  Add a listbox named lstFiles --> for sorting =) or you can create your own sorting function.
2.  Create command buttons named scan --> array of buttons, ie.
scan(0)
scan(1)
...
scan(5)


'====================

Dim i As Integer

Const ScanMaxDocs = "6"    '(this is currently the maxiumum pages to a series of documents)
Const ScanYear = "2006"
Const ScanFormat = ".pdf"
Const ScanPath = "C:\Scans\"
Const ScanReference = "12345678"


Private Sub Form_Load()
    CommandButton1_Click
   
    showButtonsAndCaptions
End Sub

Private Sub showButtonsAndCaptions()
Dim ctr As Integer

    For ctr = 0 To ScanMaxDocs - 1
        'put label here
        scan(ctr).Caption = lstFiles.List(ctr)
        'hide if greater than files
        scan(ctr).Visible = ctr < i
    Next
End Sub

Private Sub CommandButton1_Click()
    Dim strFileName As String
   
    lstFiles.Clear
     
    strFileName = Dir$(ScanPath & ScanReference & "-" & ScanYear & "-" & "*" & ScanFormat)
    Do Until strFileName = ""
        lstFiles.AddItem (strFileName)
'        ReDim Preserve Scans(i)
        i = i + 1
        strFileName = Dir$()
    Loop
    If i = 1 Then
        'MsgBox "There is one file in the folder"
    Else
        'MsgBox "There are " & i & "files in the folder"
    End If
   
    'lstFiles.Sorted
End Sub

Private Sub scan_Click(Index As Integer)
    MsgBox ScanPath & lstFiles.List(Index)
End Sub
'==================

hec",)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
harris_cCommented:
Set lstFiles SORTED property to TRUE at design time.


hec",)
0
 
GrahamSkanCommented:
You need a listbox as a quick way to sort. You can set its visble property to false. You won't then need an array

I think that an array of command buttons is easier to handle. Make them all the same name and set their Index properties to 0 to 5. A quick way to do that is to copy the first button and paste it back on to the form.


Option Explicit

Private Sub cmdScan_Click(Index As Integer)
MsgBox "Working on file " & cmdScan(Index).Caption
End Sub

Private Sub Command1_Click()
    Dim ScanYear As String
    Dim ScanFormat  As String
    Dim ScanPath  As String
    Dim ScanMaxDocs  As String
    Dim ScanReference As String
    Dim strFileName As String
    Dim i As Integer
    Dim strScans() As String
   
    ScanYear = "2006"
    ScanFormat = ".pdf"
    ScanPath = "C:\Scans\"
    ScanMaxDocs = "6" '(this is currently the maxiumum pages to a series of documents)
    ScanReference = "12345678"
    Me.List1.Clear
    For i = 0 To 5
        cmdScan(i).Visible = False
    Next i
    strFileName = Dir$(ScanPath & ScanReference & "-" & ScanYear & "-" & "*" & ScanFormat)
    Do Until strFileName = ""
        List1.AddItem strFileName
        strFileName = Dir$()
    Loop
    For i = 0 To List1.ListCount - 1
        cmdScan(i).Visible = True
        cmdScan(i).Caption = List1.List(i)
    Next i
End Sub

Private Sub Form_Load()
Dim i As Integer
    Me.List1.Clear 'Me means this Form. It can be omitted here.
    For i = 0 To 5
        Me.cmdScan(i).Visible = False
    Next i
End Sub

0
 
ukkrewAuthor Commented:
many thanks for the replies - this works well in vb, however i was thinking that it'd be good to have this on one of our smaller applications which runs via access - is it possible to convert this code so that it works with vba? It stumbles when it reaches the listbox for sorting... maybe the array would be the work around?

Many thanks.
0
 
GrahamSkanCommented:
The VBA listbox does not have a Sorted property, so the listbox doesn't buy anything, and you might as well use an array.
0
 
ukkrewAuthor Commented:
what would i need to change to implement an array?

Many thanks.
0
 
ukkrewAuthor Commented:
Don't worry about the above... I've sorted it :)
0
 
GrahamSkanCommented:
Thanks.

I had realised that I was wrong to say that there was minimal difference between VB and VBA use - there are no controls arrays in VBA.

Also, I had forgotten that the way that  Forms are used in Access is quite different from their use in the other Office products, and reacquanting myself was taking a while.
0
 
ukkrewAuthor Commented:
There are some huge differences, but they can all be worked around.

Thanks for the help.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now