Link to home
Start Free TrialLog in
Avatar of ExpExchHelp
ExpExchHelpFlag for United States of America

asked on

MS-Access -- "Saved Import" procedure

Experts:

We're currently conducting a survey (where users will return a standardized spreadsheet).

For importing purposes, I've created an import procedure ("External" tab | "Saved imports") that will facilitate importing the spreadsheet data into a database.

At this time, the saved procedure/operation points at the designated file path and -- once it finds the designated filename -- will successfully import the data.

Here's the challenge:   In order for the MS-Access procedure to work, the filename must be identical.   For example, the procedure refers to file path:  H:\...\...\...\Survey.xlsm

As you can imagine, this will require to import each spreadsheet one after the other (while also ensuring to rename the XLS from whatever filename a user may have chosen to "Survey.xlsm".

I tried to change the procedure's file path to: H:\...\...\...\*.xlsm
... unfortunately, that doesn't work.

Thus, how can I run an Access procedure and import dozens/hundreds of XLS spreadsheet at once without having to rename each XLS to "Survey.xlsm"?

Thanks,
EEH
Avatar of Phillip Burton
Phillip Burton

You can loop through the directory for all xlsm files - see http://support.microsoft.com/kb/139724 for sample code.
Avatar of ExpExchHelp

ASKER

Phillip:

Thanks -- potentially the way forward.  However, the "External tab" (saved procedure does NOT list all of the steps required for importing/linking XLS worksheet and cell to the associated table/field).

That said, I am looking for a way that allows me to modify the Saved Import procedure vs. the need of having to recode all of the import process.

Any additional thoughts... e.g., where I could locate the code of the Saved Import procedure?

Thanks,
EEH
I will suggest in the Click code for a button on a form.
I'm not tracking... are you suggesting to create a blank form?   Or is there a shortcut (somewhere in the menu) that allows me to bring up the code for the stored procedure?

EEH
Instead of renaming every survey and processing them one at a time, build a script that combines all of the surveys into a single Survey.xlsm file.
If I understand the question correctly, yes, I suggest creating a blank form with a button so that the user can start the process.
ThomasMcA2:

Thanks for chiming in... do you have a recommendations for such script?

EEH
ASKER CERTIFIED SOLUTION
Avatar of Simon
Simon
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you... I appreciate your providing the link to the other solution.