Reputation: 1 Apprentice
- Active Posts:
- 47 (0.03 per day)
- 30-July 09
- Profile Views:
- Last Active:
- Mar 26 2012 03:20 PM
- Dream Kudos:
19 Mar 2012 - 13:43
Posts I've Made
Posted 26 Mar 2012
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.
Posted 21 Mar 2012Ok. 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
SELECT [*] AS Expr1 FROM [Sheet1] INTO NewTable
Posted 21 Mar 2012[code].DatabaseName = "\\BOK-PC\Z:\Public\database\login.mdb"[\code]
Take the colon out after Z. And POSSIBLY make it lower case.
If that doesnt work remove z altogether and use:
Without more information I can only shout suggestions...
Posted 20 Mar 2012I really cant.. as I dont know if you're trying to install this from the server or a workstation. And I dont know your server name or how the network is configured. But I can try. Right click on the database folder you would like to save your file into. Click on the "Proerties" option at the bottom. There should be something that says path or location. Copy and paste that here.
Posted 20 Mar 2012This link is a great resource for Perl beginners and the introduction answers your question thoroughly. Though the above post pretty much covered it.
- Member Title:
- New D.I.C Head
- Age Unknown
- Birthday Unknown
- Click here to e-mail me
Hellhound4 hasn't added any friends yet.