Import from floppy A, using drop down list

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

Is there a way to automate importing excell files of various names, lets say
from a dropdown list on a form. The dropdown would list the contents of the
floppy drive A. The user would select the file to import. The file would
then be imported into the appropriate table...I tried a macro, but a
specific filename must be listed. I have various file names. Am I just
dreaming or is there a way...Thanks...Randy
 
Randy,

You are not dreaming, there are more than one ways to do this, but not in a
macro; you will have to use VB code.
One way is to use the Common Dialog activeX control, which will present the
user with the standard Windows Fole Open dialog. This method is easy, but
I've seen in these newsgroups that it may cause problems when you upgrade to
a newer Access version (I'm using it in A2K). The same can be achieved using
WinAPI calls, which involves more code, but doesn't have the above problem.
A third way to do it is to use a standard listbox instead, and use some
simple code to read all the filenames in a folder and populate the listbox
with those specifying certain criteria (e.g. .xls). Here's some sample code
to do this:

Set fs = CreateObject("Scripting.FileSystemObject")
Set fldr = fs.GetFolder("A:\")
Set fls = fldr.Files
For Each fl In fls
If Right(fl.Name, 4) = ".xls" Then
lst = lst & fl.Name
If Len(lst) > 0 Then lst = lst & ";"
End If
Next fl
lst = Left(lst, Len(lst) - 1)
Me.List0.RowSource = lst
Me.List0.Requery

where the listbox name is assumed to be named List0.

This code could be fired by the form's On Open event, but would err if no
disk was found in the drive, so it might be better to use a "Read Files"
command button on the form to fire it.

Then you would probably use a seciond command button to fire the rest of the
code that does what your macro currently does; convert your macro to code
and paste that code in the button's code, substituting the fixed Excel file
name with the one selected in the listbox. Assuming you have
"A:\SomeFile.xls" in your macro as the file to import, that string would
appear in the converted code in quotes; substitute with:
"A:\" & Me.List0

(make sure to use the actual listbox name).

HTH,
Nikos
 
Hello Nikos, I tried your suggestion of putting a listbox on a form and
firing the form to collect the files. What happens is when I open the form,
the listbox is blank. No errors or anything just a blank listbox. Any
ideas?
 
Randy,

You need to use an appropriate event to run the piece of code which collects
the filenames and populates the listbox. You could use the form's On Open
event, or you could put a command button on the form to fire it; the latter
has the advantage that you don't get an error message if there is no disk in
the drive when the form opens.
Behind what event did you put the code?

Nikos
 
Back
Top