[Hellhound4]

I work for a document imaging company that uses a single Access table to inventory our projects. What I find myself doing about once every twenty boxes is manually going through the table and comparing it to the pdfs we've generated to insure none got lost. There must be a way for Access to do this for me. What I would ideally like is to create something like this:

enter reference field x // this will be the field that contains the file names
enter box #'s y-z// the projects are subdivided into boxes and our directory structure is set up in a
// drive/project/pdfs/Box_#/File_Name.pdf formation
length_of_box# array = number of boxes entered // this is awkwardly worded. I want to create a variable to store how many boxes were entered

for i < length_of_box# i++ //for each box number entered

open directory /drive/project/pdf/(current entry in box array)/ //
search directory for first entry in reference field
if found go to next record
if not store name in NOT_Found array then go to next record
at end of box go to next box
at end of last box display Box # and file name of all not found.


Again all advice appreciated.

[June7]

Google: VBA is file in folder

Review this http://www.tek-tips....qs.cfm?fid=4116

[Hellhound4]

Awesome. Now does anyone have a link to a good VBA tutorial? I'd like to review how it handles variable creation and user input.

[June7]

Review this http://office.micros...A010341717.aspx

Check out: Access™ 2007 Programming by Example with VBA, XML, and ASP by Julitta Korol, Wordware Publishing, Inc.

[Hellhound4]

Ok new question. I've looked into vba and here is what I have so far:

Sub SearchForFile()

Dim fso

Dim file As String ' This will store the file path

Dim FileName() As String 'This Array will store the file names

Dim i As Integer ' i will be used to regulate the for loop

Dim LastBox As Integer 'this will control when the for loop stops

i = InputBox("First Box") 'setting i to first box

LastBox = InputBox("Last Box")

While i <= LastBox

    Dim RecordPerBox As Integer

    RecordPerBox = DCount("[ID]", "TestFileFinder", "[Box#] =" & i)

    file = "\\Digi1server\e\Dev_Test" & i & "\Test_File_1.txt\"

    Set fso = CreateObject("Scripting.FileSystemObject")

    If Not fso.FileExists(file) Then

        MsgBox file & " was not found.", vbInformation, "File Not Found"

    Else

        MsgBox file & " was has been found.", vbInformation, "File Found"

    End If

    i = i + 1

Wend

End Sub

What I need now is to use dlookup to populate and array then search for file names until i run out of record in that box. In which case 'i' will increment and we'll play again. I used the code from the link to get started. Hence the odd 'MsgBox' every now and then.

[Hellhound4]

Sorry. Didn't refresh so I posted without seeing yours. My new question is how to pull the information from a table into an array or some other construct so that I can use it. Ideally it would be an array so I can set the path file name to /path/to/file/Array(x).pdf/ but I'm open. Also I noticed some grammar and formatting errors in my post but can't seem to fine the edit button. It is there until I refresh then it leaves and doesn't return...

This post has been edited by Hellhound4: 20 March 2012 - 01:02 PM

[Hellhound4]

So this is where I am now:

Option Compare Database

'The purpose of this module is to search for scanned pdfs inside their variable

'folders and store any not found, or naming conflicts, and output the details for

'manual solutions



Sub SearchForFile()

Dim fso

Dim file As String ' This will store the file path

Dim i As Integer ' i will be used to regulate the for loop

Dim LastBox As Integer 'this will control when the for loop stops

i = InputBox("First Box") 'setting i to first box

LastBox = InputBox("Last Box")

Dim RecordTrack As Long

RecordTrack = 1



Dim NumberOfFilesMissing As Integer

NumberOfFilesMissing = 1



While i <= LastBox

    Dim RecordPerBox As Integer

    RecordPerBox = DCount("[ID]", "TestFileFinder", "[Box#] =" & i)

    Dim FileNameArray() As Variant 'create array to store fileNames

    Dim FileNotFoundArray() As Variant

        While RecordTrack <= RecordPerBox

            ReDim Preserve FileNameArray(1 To RecordPerBox)

            FileNameArray(RecordTrack) = DLookup("[FileName]", "TestFileFinder", "[Box#] = 1" & i & "And [ID] =" & RecordTrack)

            file = "\\Digi1server\e\Dev_Test" & i & "\" & FileNameArray(RecordTrack) & ".txt\"

            MsgBox FileNameArray(1)

            Set fso = CreateObject("Scripting.FileSystemObject")

            If Not fso.FileExists(file) Then

                NumberOfFilesMissing = NumberOfFilesMissing + 1

                ReDim Preserve FileNotFoundArray(1 To NumberOfFilesMissing)

                MsgBox file & " was not found.", vbInformation, "File Not Found"

                FileNotFoundArray(NumberOfFilesMissing) = file

            Else

                MsgBox "Headshot! Tango Down!"

            End If

            RecordTrack = RecordTrack + 1

        Wend

    i = i + 1

Wend

MsgBox FileNotFoundArray() & " were not found.", vbInformation, "File Not Found"

End Sub

I cant seem to get a message box to display any array elements at all. Any help at all is appreciated with this. And June7 I read both of your posts and followed your directions. To put this in perspective I've never used VBA before yesterday.

[June7]

Can't output an entire array, output the elements of the array.

Try like:

For i = 0 To UBound(FileNotFoundArray) - 1
    strNF = strNF & vbCrLf & FileNotFoundArray(i)
Next
MsgBox "Files not found:" & vbCrLf & strNF

Should see an 'Edit' button at lower right of your post.

Impressive effort for a newbie. You have other programming experience?

This post has been edited by June7: 20 March 2012 - 05:35 PM

[Hellhound4]

Thank you I will have to try that tomorrow. I don't have anyway to test it at home. Does the rest of it look good?

[June7]

Nothing looks obviously wrong. Step debug if results are not what you expect. Follow the code as it executes, find where behavior deviates from expected, fix, repeat.

Could take the Set fso line outside the loops. It only needs to be set once.

This post has been edited by June7: 20 March 2012 - 05:54 PM

[Hellhound4]

Ok. I got that running great. I will post my solution tomorrow for anyone else who wants it. I did have and issue while migrating the final product from a test database to our working database. Several of my queries changed and the vba code was a little off. I put it all right but I was wondering if Access references the current table/db/query etc in ways other than by name. My working database is an exact mirror of the test db as far as structure. And example:
SELECT * FROM Sheet1 INTO NewTable

Became

SELECT [*] AS Expr1 FROM [Sheet1] INTO NewTable

[June7]

Nothing in your posted code shows procedure for import or linking to spreadsheet or appending records from imported/linked spreadhseet. I haven't done much appending records from spreadsheet source, don't know why that syntax change. Does it work?

Referencing tables other than directly by name requires use of TableDefs collection and then can use an index reference.

[Hellhound4]

Attribute VB_Name = "Search for File"

Option Compare Database

'The purpose of this module is to search for scanned pdfs inside their variable

'folders and store any not found, or naming conflicts, and output the details for

'manual solutions

'Call SearchForFile





Sub SearchForFile()

Dim fso

Dim file As String ' This will store the file path

Dim i As Variant ' i will be used to regulate the for loop

Dim LastBox As Integer 'this will control when the for loop stops

i = InputBox("First Box") 'setting i to first box

LastBox = InputBox("Last Box") 'set LastBox to the final box

Dim RecordTrack As Long 'this will store a variable to track which record we are on

RecordTrack = 1 'RecordTrack will start at 1

Set fso = CreateObject("Scripting.FileSystemObject")

Dim NumberOfFilesMissing As Integer

NumberOfFilesMissing = 0

Dim x As Integer

Dim LoopCount As Integer





While i <= LastBox 

    LoopCount = 1

    Dim RecordPerBox As Integer

    

    RecordPerBox = DCount("[ID]", "TestFileFinder4", "[Box #] =" & i)

    Dim FileNameArray() As Variant 'create array to store fileNames

    Dim FileNotFoundArray() As Variant

        While LoopCount <= RecordPerBox

            ReDim Preserve FileNameArray(1 To RecordTrack)

            FileNameArray(RecordTrack) = DLookup("[Expr1]", "TestFileFinder4", "[Box #] =" & i & "And [ID] =" & RecordTrack) 

            file = "\\Path\to\file\PDFs\Project" & " " & i & "\" & FileNameArray(RecordTrack) & ".pdf"

            

            If Not fso.FileExists(file) Then

                NumberOfFilesMissing = NumberOfFilesMissing + 1

                If (NumberOfFilesMissing >= 1) Then

                    ReDim Preserve FileNotFoundArray(1 To NumberOfFilesMissing)

                    'MsgBox file & " was not found.", vbInformation, "File Not Found"

                    FileNotFoundArray(NumberOfFilesMissing) = file

                End If

            Else

                'MsgBox "Headshot! Tango Down!"

            End If

            RecordTrack = RecordTrack + 1

            LoopCount = LoopCount + 1

        Wend

    i = i + 1

Wend

If NumberOfFilesMissing = 0 Then

    MsgBox "All Files Were Found."

Else

    x = 1

    For x = 1 To UBound(FileNotFoundArray)

        strNF = strNF & vbCrLf & FileNotFoundArray(x)

    Next

        MsgBox "Files Not Found:" & vbCrLf & strNF

End If

End Sub

The code as promised. A bit late but hey. I could use an alternative to MsgBox here:

Else

    x = 1

    For x = 1 To UBound(FileNotFoundArray)

        strNF = strNF & vbCrLf & FileNotFoundArray(x)

    Next

        MsgBox "Files Not Found:" & vbCrLf & strNF

To avoid the 250 character limit on the off chance more than ~20 files are found missing. Also wouldn't mind if it displayed the number of records checked. Or any file found in the directory but not in the database.

[June7]

Instead of writing to an array, write to a table or a listbox on form every name and the result of search. The number of records to a textbox.