Use a form to select and import excel spreadsheet from c:\"locatio

[EddieL]

I need to import an excel speadsheet on a daily basis from a location on the
c:\ drive. Is there a method that I can use to select the relevant file from
a form in the database and automate the import procedure following a command
button on click event?. I have used the import feature but this is not
''automated''. I also have to do this retrospecively for many months and for
it to be ''simple to use'' by others hence my need to automate.... isn't that
(one) reason for access databases!

I have search many on-line resources only to find partial answers and I am
not talented enough t be able to combine the ''partial soulutions". Thanks
in advance.

 

[Mr. B]

EddieL,

There are several questions that will need to be address to even start to
try to assist you in accomplishing your goals.

First, are you comfortable with writing VBA code? While macors can do a
lot, the real power of an Access application in in using VBA code.

Another thing that might make things a little simpler for a beginner is if
you can have all of the spreadsheets to be loacated in the same folder,
perhaps a shared network drive created just for that purpose. The you would
already know the path and not have to have you or your users to loacate the
folder or the file. If you implement this type of file storage method, it is
possible to then iterate throught all .xls file in that folder and process
(import) each.

One more question: Is the format of all of the spreadsheets the same? If
so, you can define an Import Definition and use that definition to import all
of the data from the spreadsheets.

I realize that I did not provide a lot of "coding" help, but before you
start to code, you need to know as much about the details about what you are
going to have to do as possible.

Please post back and provide us with a little feedback and I am sure that
some of the experts on here will be albe and glad to assist you.

 

[Ken Snell MVP]

Browse to a single EXCEL File and Import Data from that EXCEL File via
TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpBrowseFile


Browse to a single Folder and Import Data from All EXCEL Files in that
Folder via TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpBrsFldFiles

 

[Mr. B]

Thanks, Ken, for supplying those links. They are quite helpful. I have
bookmarked then.

Mr. B