Check each filename in an array to see if file exists

[dannewestis]

Hi,

I'm rather new to the VBA part of Access and hope that maybe someone can help me out. This is what I'm trying to achieve:

1. Find records that meet certain criteria, through an SQL statement (I have a query, but not sure how to do it in VBA)
2. For each record in the array, check if the filename exists in a specified path.
3. Print a list of filenames that do not exist (and thus need to be transferred).
4. If the file does not exist, possibly transfer the file from another path to the specified path. This step could be done manually, however.

Any hints on how this could look?

Thanks!

/Daniel

 

[RuralGuy]

Big list! I'll help with #2 by saying you will probably use the Dir() function for the verification.

 

[The_Doc_Man]

To execute SQL in VBA, you can look at .Execute (method) if it is an action query, or you can open the query as a recordset if it is a SELECT query.

Help Search Keywords: "Execute (method)" "Action query" "Recordset"

To actually print something, you can use VBA to open a file and use the print verb to write lines of text into the file. Of course, you must close the file when done.

Help Search Keywords (in VBA context): "Open (file)" "Print" "Close"

I'll let others contribute to this one, too.

 

[WayneRyan]

Daniel,

Make a Public Function in a module to do this.
Call it from your query, by adding a new column:

NewColumn: ProcessFile([FileName], "C:\TheEndPath", "C:\TheSourcePath")

Public Function ProcessFile(strFileName As String, strEndFilePath As String, strInitFilePath As String) As String

' strFileName      <-- The Filename
' strEndFilePath   <-- Where you want it to reside
' strInitFilePath  <-- Where it might be now

Dim strLook As String
'
' Check to see if it exists in the desired directory
'
strLook = Dir(strEndFilePath & "\" & strFileName)
If strLook <> "" Then
   ProcessFile = "File Present."
   Exit Function
End If
'
' Check to see if it exists in the source directory
'
strLook = Dir(strInitFilePath & "\" & strFileName)
If strLook = "" Then
   ProcessFile = "File Not Available."
   Exit Function
End If
'
' Copy the File
'
FileCopy strInitFilePath & "\" & strFileName, strEndFilePath & "\" & strFileName
ProcessFile = "File Copied."
End Function

Then, you can export the query to Word/Excel, etc. to document your work.

hth,
Wayne

 

[dannewestis]

Excellent! Thanks!

I think this should do what I want.