Need create optional import list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a db that imports a .txt file. So far the name of the file has been
consistent. The download has changed to a filename with a character fllwed
by the date the file was made, which means it is now a variable name. It
will always end with .dat and instead of one file there are now mulitple
files.

I want to list the files and give the user an option to import the file or
not using a checkbox. I know it can be done, just don't know how.

Any suggestions?
 
Mikel,
There are a few ways you could go about doing this. You know the path and
the file name template as something if I follow as *.DAT, therefore, you can
use the file system object to get all the files in the directory that have
the .dat extension, then do what you want with them. This could amount to
putting them in a table and working from there, perhaps...

strDir = "c:\my path\"
Set fs = CreateObject("Scripting.FileSystemObject")

'get all the files in the directory
'store the ones you want in array
intcount = 0

strFileName = Dir(strDir)

Do While Len(strFileName) > 0
tmpVal = InStr(1, strFileName, ".dat", vbTextCompare)
if tmpVal>0 then
fnames(intcount) = strFileName
intcount = intcount + 1
end if
strFileName = Dir
Loop


-Doug
 
Doug,
I need to do something similar to this. At present I have a macro which uses
the Transfer Database action to import data from Tables in another database.
I have to hardcode the filename into the macro and rename my database one at
a time - trouble is I have approx 150 databases to import 4 tables from and
append to 4 existing tables in a master.

I have tried your example code out but I get an error - fnames not defined?
I would also appreciate some further advice on how to handle the files once
I have them in an array - so I can import four tables from each (all with
same table names).

Thanks in advance for any help!
Sue
 
Doug,

Sorry it took so long to get back to the note you have.

I am also having problems with the "fnames" not being defined. I need
further help!

Mikel
 
Doug,

Disregard my last note, it worked fine after I remembered to define an array
out of fnames. I must have not had enough coffee!

Mike
 
Doug I need your help again.

I did as you suggested on a computer at the house and the suggestion works
like a charm. Happy with my work, I burnt a copy onto a cd, took it to the
office and copy it to the harddrive there. Redid the links to the backend
and it was ready to go!

The problem is that when I run the thing, I get a runtime error 28 out of
stack space error. I believe it occurs at the set fs command as that is
where the debug goes.

As far as I know, nothing has changed other than the location of the files
and the new path.

Pls help!

Mike
 
Doug:

I need you help again. Your suggestion worked like a charm on the computer
as my house. So I burnt a copy onto a cd and copied it to the hard drive at
the office, updated the links to the backend and thought I had the world.

However, when I try the code at the office I get an "Out of stack space"
error and am dumped out of the program all together.

I believe the error occurs at the "set fs" command as that is where the
debug sends me. The only difference between the two is the file location and
I changed the path.

Is there any help to be had!

Mike
 
Back
Top