By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,089 Members | 1,952 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,089 IT Pros & Developers. It's quick & easy.

Import Excel file which has Dynamic name

P: n/a
Hi All,
I have an Excel file being delivered to a shared drive where I only
have read access on a daily basis.

The files arrive in the following format 'Filename+timestamp.xls'
Example: ThisFile2007-02-01 11.02.00.421.xls

Is there a way a can create some sort of link that matches a pattern?

If file name starts with 'ThisFile'
Import data

Thanks all
Steve

Feb 1 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a

Steve wrote:
Hi All,
I have an Excel file being delivered to a shared drive where I only
have read access on a daily basis.

The files arrive in the following format 'Filename+timestamp.xls'
Example: ThisFile2007-02-01 11.02.00.421.xls

Is there a way a can create some sort of link that matches a pattern?

If file name starts with 'ThisFile'
Import data

Thanks all
Steve
Steve,
Not 100% sure about periods in a filename in Windoze, but... the rest
is easy.
If the file is always in the same directory, and always has the same
structure, you can create an import specification by importing the
file manually - do the whole File->Get External Data-dance and
create the import specification.

If the file(s) is/are always in the same directory, you can use Dir()
to loop through the contents and then use TransferSpreadsheet to
import the individual files. Once you get the import spec working,
you're good to go.

I like to use the BrowseFolder API at AccessWeb so the user can
specify where the file(s) are. If you only ever import a single file
at a time, you _can_ use the OpenSaveFile API in the same place.
(www.mvps.org/access) in the APIs section, and even set the filters
etc, but I'm a lazy bugger and like to "tell" Access to look for the
files in the same place (store the path in a table if you want), and
then just process them all. Really handy if you've neglected several.

you can also use the scheduler utility there to run the imports at a
specific time, or you could use the Timer event of a form to execute
the code every fixed number of minutes/hours. Then if your import
spec is working, you won't really have to do anything. The code in an
open (and maybe hidden) form will take care of the work for you.

Feb 1 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.