Good morning Clive,
I was able to automate the import wizard using the Saved Import. I will try the naming conventions for multiple files.
The main issue now is to automatically email the accept/reject report to whoever submitted it.
I will submit this as a separate problem.
Thank you very much!
Sally
From: MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com> [mailto:MS_Access_Professionals@yahoogroups.com]<mailto:[mailto:MS_Access_Professionals@yahoogroups.com]> On Behalf Of Clive
Sent: Sunday, April 29, 2012 11:03 AM
To: MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
Subject: [MS_AccessPros] Re: Automatic import/validation/update from Excel spreadsheet to Access
Hi Sally,
Raise the email requirement as a new problem.
You will need to state,
1. The name of your email program.
2. Whether it is in-house or the Internet.
3. The level of security required, encrypted, or TLS.
4. Whether you are using Outlook or not.
You will get more replies raising a single problem
at a time because there are lots of members with
with experience of specific problems.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Your three CommandButtons may be replaceable.
1. If you receive your 'Import' files into a specific
folder then you can use a form with a Timer that runs
every so often, say every five minues more or less.
The Timer_Event routine will run every five minutes
and then go back to sleep if there is/are no file(s)
to be processed. If there are files waiting then the
routine would call the subroutine that reads the
file(s) one by one and do the validation. That is
when you call the Import Wizard if you can handle
the saving of the good data and the rejection of
any bad data without manual intervention.
This is also where you need your emailing to be done
for each file processed.
2. When a file has been read and verified, the file will
need to be renamed to avoid it being read a second time.
3. You must have a naming convention for the Filenames
that allows multiple files to be placed in the same
folder by multiple users. I would expect the file
extension to be the same for all of the files, .xls?,
and to indicate that a file has been processed, the
file could be renamed say
from
2012_04_29_0001_John.xls
to
2012_04_29_0001_John.Done
Please let us know which parts of the above require manual
intervention at the moment.
Regards, Clive.
--- In MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com>, Sally Vega <sally_vega@...<mailto:sally_vega@...>> wrote:
>
> Hi Clive,
>
> Right now I have it down to three buttons:
>
>
> 1) Import (using the import wizard)
>
> 2) Validation/Update/Reject
>
> 3) An email form comes up and you send the accept/reject notice to the user.
>
> Thank you for your input! I did experiment with it and found that you can automatically import an Excel file to Access using a Saved Import and using the Saved Import Event Procedure in a form using the timer interval.
>
> Now, what if there is a reject for some reason? I would like to be able to email the reject/accept notice to the user. Do you know how this can be done?
>
> Thank you,
>
> Sally
>
> From: MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com> [mailto:MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com>] On Behalf Of Clive
> Sent: Friday, April 27, 2012 3:29 AM
> To: MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: [MS_AccessPros] Re: Automatic import/validation/update from Excel spreadsheet to Access
>
>
>
> Good Morning Sally,
>
> After some overnight deep thinks there is a way where
> the processing of the files could be automated from
> Access.
>
> It requires the use of the Timer on a new Form
> dedicated to searching for updates. The Form can
> based on new table, tUpdates, containig all updates
> that have been processed.
>
> The folder for the UpdateFiles could be in a
> subfolder from the database folder say so that you
> could use,
> strUpdate = CurrentProject.Path & "\Updates\*.txt"
> to access the files easily from the Db. When a file
> is processed then the filetype could be changed so
> that it is no longer recognized when using the
> Dir_Function. I don't like the idea of deleting them
> automatically just yet.
>
> The sending of emails or files toi the originator
> could be done as part of the process.
>
> Hope that gives you food for thought.
>
> Regards, Clive
>
> --- In MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com><mailto:MS_Access_Professionals%40yahoogroups.com>, Sally Vega <sally_vega@> wrote:
> >
> > Good morning all-
> >
> > My Boss has requested something that I'm not sure can be done in Excel or Access "automatically".
> >
> >
> > 1.) Is there a way to put an Excel Spreadsheet in a folder and have either Excel or Access import it into Access using a timer. Say every hour?
> >
> >
> > 2.) After it imports the spreadsheet, it needs to be run through a program to validate the data and also check against duplicates both in the imported spreadsheet and in the database. It needs to be able to update the line items that do pass validation and separate those that don't pass validation.
> >
> >
> >
> > 3.) It needs to either send an email or maybe put the error in a file somewhere for the user to be notified of the line items that didn't that didn't upload.
> >
> >
> > I have the three steps programmed so that the users upload the file themselves using the import wizard and does the validation/rejection/upload with a few clicks of a button but I don't know that it can be done "automatically". (I hate that word.)
> >
> > Has anyone ever done anything like this? Any ideas would be greatly appreciated.
> >
> > Thank you,
> >
> > Sally Vega
> > Supply Chain Data Analyst
> > Super First Class Products
> > B/E Aerospace, Inc.
> > 1851 S. Pantano Rd.
> > Tucson, AZ 85710 USA
> > (520) 239-4812
> >
> > This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
> This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
>
>
> [Non-text portions of this message have been removed]
>
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar