Building an array if file exists (1 Viewer)

Thicko

Registered User.
Local time
Today, 16:42
Joined
Oct 21, 2011
Messages
61
Hi Guys,

I'm looking for a way to build an array of file names where a file exists. (I've been advised this is the way to sort my problem)

The list of potential file names will be from a query.

The array will be used to attach PDFs to an e-mail

The problem is that I'm really novice with recordsets, the reading I've done on arrays confuses me and I struggle with anything but the simplest loops.

I'm aware of the FileExists() function

My thoughts are:

Code:
Dim db as Database
Dim rs as Recordset
 
Set mydatabase = CurrentDb
Set rs = mydatabase.OpenRecordset("qryEmailGovernance", dbOpenDynaset)
 
Dim FileLocation as String
 
i=0
Do While rs.AbsolutePosition <> -1
 
If FileExists("FileLocation") Then
?????????????????Stuck?????????????????????????
Else
End If
 
i=i+1
rs.MoveNext
Loop
 
set rs = nothing
set db = nothing

Hope someone can explain.
Thank You
 

Beetle

Duly Registered Boozer
Local time
Today, 10:42
Joined
Apr 30, 2011
Messages
1,808
Just to clarify, you have a query which returns the full path of file names which may or may not actually exist, and you want to loop through this record set and check if each file exists?

If the path is valid then you want to add that path to an array which will be used (elsewhere?) in your code to send an e-mail to (one or more?) users with these files as attachments?

And you don't want to allow the users (via a File Open Dialog) to just select the attachment files themselves?

Is that correct?
 

ghudson

Registered User.
Local time
Today, 12:42
Joined
Jun 8, 2002
Messages
6,195
Check out the Browse [Find a directory or file] sample on how to loop through a directory of files.

Code:
If Dir(FileLocation) <> "" Then
  'the file exists
Else
  'the file does not exists
End If
 
Last edited:

Thicko

Registered User.
Local time
Today, 16:42
Joined
Oct 21, 2011
Messages
61
Beetle,

You've got it exactly right, it would be the full file path name I would be testing.

I don't want to send all paths in the e-mail as if they don't exist it will throw up some errors.

I should also say the list length would be different each month.

Cheers
 

VilaRestal

';drop database master;--
Local time
Today, 16:42
Joined
Jun 8, 2011
Messages
1,046
I'll have a go:

Code:
Dim rs as Recordset
Set rs = CurrentDb.OpenRecordset("qryEmailGovernance", dbOpenDynaset)
Dim FileLocation as String
Dim RealFiles() As String 'Dynamic array of FileLocations that can be found
Dim i As Long
i = 0
rs.MoveFirst
Do While Not rs.EOF
    FileLocation = rs!FileLocation 'Change to correct name of field if not that
    If FileExists(FileLocation) Then
        ReDim Preserve RealFiles(i) 'Redeclares the array to this new dimension preserving existing elements
        RealFiles(i) = FileLocation
        i = i + 1
    End If
    rs.MoveNext
Loop
rs.Close
set rs = nothing
 

Thicko

Registered User.
Local time
Today, 16:42
Joined
Oct 21, 2011
Messages
61
VilaRestal you've made my day. A Big Thank you.

I took your code and with one minor change which was the FileExists line I'd proposed in the first place it works.

I have attached the final working code for anyone with a similar issue.

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("EmailGovernanceAttach", dbOpenDynaset)
Dim ReportLocation As String
Dim RealFiles() As String 'Dynamic array of FileLocations that can be found
Dim i As Long
i = 0
rs.MoveFirst
Do While Not rs.EOF
ReportLocation = rs!ReportLocation 'Change to correct name of field if not that
If Len(Dir(ReportLocation)) > 0 Then
ReDim Preserve RealFiles(i) 'Redeclares the array to this new dimension preserving existing elements
RealFiles(i) = ReportLocation
i = i + 1
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Debug.Print RealFiles(2) 'Just to check it's what I'm expecting

Once again thank you.
 

VilaRestal

';drop database master;--
Local time
Today, 16:42
Joined
Jun 8, 2011
Messages
1,046
Glad to hear it works.

VB's arrays are one of their poorest features. In better languages you can do funky things like:

Array() = {Array(), 5}

to increase the size of the array by one element and put the value 5 in that new element.

In VB you've got all the Redim Preserve lines and if you forget to preserve ... :(

But anyway, moan over, that's how to do dynamic arrays in VB. Not difficult just annoying.
 

Users who are viewing this thread

Top Bottom