Test file names against table.

Page 1 of 1

13 Replies - 2108 Views - Last Post: 26 March 2012 - 09:52 PM

#1 Hellhound4

Reputation: 1
• Posts: 47
• Joined: 30-July 09

Test file names against table.

Posted 19 March 2012 - 01:25 PM

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.

Is This A Good Question/Topic? 0

Replies To: Test file names against table.

#2 June7

Reputation: 104
• Posts: 904
• Joined: 09-December 08

Re: Test file names against table.

Posted 19 March 2012 - 05:35 PM

Google: VBA is file in folder

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

#3 Hellhound4

Reputation: 1
• Posts: 47
• Joined: 30-July 09

Re: Test file names against table.

Posted 20 March 2012 - 07:06 AM

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.

#4 June7

Reputation: 104
• Posts: 904
• Joined: 09-December 08

Re: Test file names against table.

Posted 20 March 2012 - 10:24 AM

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.

#5 Hellhound4

Reputation: 1
• Posts: 47
• Joined: 30-July 09

Re: Test file names against table.

Posted 20 March 2012 - 10:56 AM

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

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.

#6 Hellhound4

Reputation: 1
• Posts: 47
• Joined: 30-July 09

Re: Test file names against table.

Posted 20 March 2012 - 12:50 PM

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

#7 Hellhound4

Reputation: 1
• Posts: 47
• Joined: 30-July 09

Re: Test file names against table.

Posted 20 March 2012 - 04:20 PM

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)

FileNotFoundArray(NumberOfFilesMissing) = file

Else

End If

RecordTrack = RecordTrack + 1

Wend

i = i + 1

Wend

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.

#8 June7

Reputation: 104
• Posts: 904
• Joined: 09-December 08

Re: Test file names against table.

Posted 20 March 2012 - 05:31 PM

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


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

#9 Hellhound4

Reputation: 1
• Posts: 47
• Joined: 30-July 09

Re: Test file names against table.

Posted 20 March 2012 - 05:36 PM

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?

#10 June7

Reputation: 104
• Posts: 904
• Joined: 09-December 08

Re: Test file names against table.

Posted 20 March 2012 - 05:52 PM

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

#11 Hellhound4

Reputation: 1
• Posts: 47
• Joined: 30-July 09

Re: Test file names against table.

Posted 21 March 2012 - 03:37 PM

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

#12 June7

Reputation: 104
• Posts: 904
• Joined: 09-December 08

Re: Test file names against table.

Posted 22 March 2012 - 01:24 PM

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.

#13 Hellhound4

Reputation: 1
• Posts: 47
• Joined: 30-July 09

Re: Test file names against table.

Posted 26 March 2012 - 03:11 PM

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)

FileNotFoundArray(NumberOfFilesMissing) = file

End If

Else

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

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



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.

#14 June7

Reputation: 104
• Posts: 904
• Joined: 09-December 08

Re: Test file names against table.

Posted 26 March 2012 - 09:52 PM

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.