COMBO BOX used to view contents of a Directory on C drive in Acces

[AJOLSON]

I hope I am asking this in the right forum.
I am using Access 2007 and what I want to do is use a combo box (or list
box) to show the contents of a directory on the C: Drive. And when the user
selects a file it performs another function. Maybe I don’t need a combo box
to do this but seems like that might be the route to go. Anyhow below is a
more detailed example of what I want to do.
Example - I have a folder in the C directory “C:\Dfiles†and within that
directory I have three files File1.xls …… File2.xls….. File3.xls. What I
want is for the user to click on a combo box and when the combo box loads the
user will see:
File1.xls
File2.xls
File3.xls
Then they can select whatever file then need.
Does anyone have any code that might do this for me? I have been
unsuccessful with Application.Filesearch
What ultimately will happen is when the user selects the file (clicks on it)
other code will execute and load the file into a table within the DB.
However, I have that code working

 

[Jim B]

Use the function below as the Row Source Type for the combo box (just enter
ListFiles in the Row Source Type of the combo box on the form) It will call
this function to populate the combobox. Substitute your directory for the
strGlobalPath variable:

Function ListFiles(F As Control, ID, Row, Col, Action)
Dim strGlobalPath as string
Dim strExtension as string

' Set strGlobalPath to your Dir
' Set strExtension to whatever filetype your looking for

strGlobalPath = "C:\" ' Use your directory here
strExtension = "\*.XLS"

Static MDBS(), FileCount
Dim FileName
Select Case Action
Case acLBInitialize
FileCount = 0
FileName = Dir(strGlobalPath & strExtension)
Do Until FileName = ""
FileCount = FileCount + 1
FileName = Dir
Loop
ReDim MDBS(FileCount)
FileCount = 0
MDBS(FileCount) = Dir(strGlobalPath & strExtension)
Do Until MDBS(FileCount) = ""
FileCount = FileCount + 1
MDBS(FileCount) = Dir
Loop
ListDatabaseFiles = FileCount
Case acLBOpen
ListDatabaseFiles = Timer
Case acLBGetRowCount
ListDatabaseFiles = FileCount
Case acLBGetColumnCount
ListDatabaseFiles = 1
Case acLBGetColumnWidth
ListDatabaseFiles = -1
Case acLBGetValue
ListDatabaseFiles = MDBS(Row)
Case acLBEnd
Erase MDBS
End Select
End Function

 

[Larry Linson]

I am using Access 2007 and what I want to do is use a combo box (or list
box) to show the contents of a directory on the C: Drive. And when the user
selects a file it performs another function.

I fear you are mixing your view of what the solution should be with a
statement of what the problem is.

I understand your goal is to allow the user to select a file.

The article, and freely downloadable code, at
http://www.mvps.org/access/api/index.html enable using the very familiar
Windows Common Dialog to select a file. It includes example code for using
it, too, and many copy, read the instructions, and make it work the first
time...

Larry Linson
Microsoft Office Access MVP

 

[AJOLSON]

What a nightmare this has been. I can't even get the functions to run. How
do I get any funtion or code that is not Sub Command13_Click() or got focus
yada yada yada. ??

I tired copying and pasting the code on that web page but again it will not
execute. even set up macros to run the functions but nope All I get there is
a compile error.

I know it has to be easy but man !!!!!

 

[James A. Fortune]

I hope I am asking this in the right forum.
I am using Access 2007 and what I want to do is use a combo box (or list
box) to show the contents of a directory on the C: Drive. And when the user
selects a file it performs another function. Maybe I don’t need a combo box
to do this but seems like that might be the route to go. Anyhow below is a
more detailed example of what I want to do.
Example - I have a folder in the C directory “C:\Dfiles†and within that
directory I have three files File1.xls …… File2.xls….. File3.xls. What I
want is for the user to click on a combo box and when the combo box loads the
user will see:
File1.xls
File2.xls
File3.xls
Then they can select whatever file then need.
Does anyone have any code that might do this for me? I have been
unsuccessful with Application.Filesearch
What ultimately will happen is when the user selects the file (clicks on it)
other code will execute and load the file into a table within the DB.
However, I have that code working

Try:

http://groups.google.com/group/microsoft.public.access/msg/46e590088285f26c

It should convert to Access 2007.

James A. Fortune
(e-mail address removed)

 

[AJOLSON]

Ok I finally quite acting like I was clueless looked at the code and got
this to work. Guess sometimes ya get thick headed about code and need to
step back and the come back at it a few days later. Again Thanks for the
help all working exactly like I had hoped.

Andy

 

[dhstein]

Jim,

I came across this post and I'm trying to get it to work. But the combo
box does not get populated. I have Access 2007. Does that make any
difference? Thanks.

David