File List Creator

[jwmckin]

Is there a way to have Access look inside a folder or folders and with
a query create a list of files within these folders? Thanks in advance

[Jim Allensworth]

On 6 Jul 2004 05:33:58 -0700, jw*****@yahoo.com (jwmckin) wrote:

Is there a way to have Access look inside a folder or folders and with
a query create a list of files within these folders? Thanks in advance

Not in a query directly. They would have to be in a table to query
them. However, you could get a list with a function. The following
function will return a list separated by semi-colons ready for a
listbox or combobox rowsource.

==========================
Public Function fFilesInDir(strDir As String) As String
'The input string strDir needs to be structured like:
'C:\MyFolder\
'Depending on your usage you might need to manage this.

Dim strListSrce As String
Dim strFile As String

strListSrce = Dir(strDir & "*.*")
If Len(strListSrce) = 0 Then
MsgBox "Empty directory!"
Exit Function
End If
Do
strFile = Dir
If Len(strFile) = 0 Then
Exit Do
Else
strListSrce = strListSrce & ";" & strFile
End If
Loop
fFilesInDir = strListSrce

End Function
==========================

- Jim

[jason mckinney]

Hi Jim,

Thanks for your fast reply. I have limited experience with Access and
its been several years since I've touched VB and have never used VBA
with Access so I'm having a little trouble following you. If you could
dumb this down for me any more than you have then I would greatly
appreciate it. Also I have multiple folders that I have to look in to
generate my list. This is going to save me a week's worth of typing if I
can get it to work quickly. Thanks a lot!

Jason

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Jim Allensworth]

On 06 Jul 2004 16:56:48 GMT, jason mckinney <jw*****@yahoo.com> wrote:

Hi Jim,

Thanks for your fast reply. I have limited experience with Access and
its been several years since I've touched VB and have never used VBA
with Access so I'm having a little trouble following you. If you could
dumb this down for me any more than you have then I would greatly
appreciate it. Also I have multiple folders that I have to look in to
generate my list. This is going to save me a week's worth of typing if I
can get it to work quickly. Thanks a lot!

Jason

Hi Jason,

To get this to work copy the function and paste it into a standard
module.

Select the modules tab on the DB window, click New and paste it. Save
it as something other than the name of the function - maybe "basDir" .

To use the function supply it with the path of the folder like..

fFilesInDir("C:\FolderName\")

If you had a folder on your C: drive named "FolderName" then you would
get back the semi-colon delimited list.

You can test this in the debug window. Press <Ctrl><g> to open it and
then type:
?fFilesInDir("A_Path_To_A_Folder")

Then press <Enter>
Note that the semi-colon list is set to be used as a RowSource of a
List box or Combo box on a form. For example to use it as the
RowSource of a List Box; create a new listbox on a form (lets call it
"lstTest"), open the properties window for it, select the Data tab and
set Row Source Type to "Value List". Now in the properties for the
form click the Event tab and select the On Open event, click the
ellipsis button to the right [...], open the forms module to the open
event type in like...

Private Sub Form_Open(Cancel As Integer)
Me.lstTest.RowSource = fFilesInDir("C:\MyFolder\")
End Sub

Now to test this a different way you could change the code to printout
to the debug window the files. Change it to...
========================
Public Function fFilesInDir(strDir As String) As String
Dim strListSrce As String
Dim strFile As String

strListSrce = Dir(strDir & "*.*")
Debug.Print strListSrce
If Len(strListSrce) = 0 Then
MsgBox "Empty directory!"
Exit Function
End If
Do
strFile = Dir
If Len(strFile) = 0 Then
Exit Do
Else
strListSrce = strListSrce & ";" & strFile
Debug.Print strFile
End If
Loop
fFilesInDir = strListSrce

End Function
====================

Depending on what you want to do; the code could be altered some more.

- Jim

[jason mckinney]

I tried that and I'm getting these compile errors...

Code
Public Function fFilesInDir(C:\Documents and Settings) As String

Error
Expected: list seperator or )

It seems to have problems with the semicolon in C:\

What could be the issue here?

I've also tried

Code
Public Function fFilesInDir("C:\Documents and Settings") As String

Public Function fFilesInDir("C:\Documents and Settings" as String) As
String

Public Function fFilesInDir(strDir "C:\Documents and Settings" as
String) As String
Thanks again!

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Jim Allensworth]

On 06 Jul 2004 20:16:32 GMT, jason mckinney <jw*****@yahoo.com> wrote:

I tried that and I'm getting these compile errors...

Code
Public Function fFilesInDir(C:\Documents and Settings) As String

Error
Expected: list seperator or )

It seems to have problems with the semicolon in C:\

What could be the issue here?

I've also tried

Code
Public Function fFilesInDir("C:\Documents and Settings") As String

Public Function fFilesInDir("C:\Documents and Settings" as String) As
String

Public Function fFilesInDir(strDir "C:\Documents and Settings" as
String) As String
Thanks again!

I'm a little confused on what you are doing here.
Did you copy the entire function - the lines between the ==========='s
and paste into a a new module and save the module? If you copied and
pasted it properly then it should compile.

If so then test it from the debug window.

? fFilesInDir("C:\Documents and Settings\")

Put that in and press <Enter>

Also, note the back slash at the end of the path argument. That's all
you need,