ExpExchHelp
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
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
You can loop through the directory for all xlsm files - see http://support.microsoft.com/kb/139724 for sample code.
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
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.
ASKER
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
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.
ASKER
ThomasMcA2:
Thanks for chiming in... do you have a recommendations for such script?
EEH
Thanks for chiming in... do you have a recommendations for such script?
EEH
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you... I appreciate your providing the link to the other solution.