testn1
asked on
Import from excel
Hello,
I have about 1000 excel sheets i would like to import and create 1 access database. How can i do this?
thank you.
I have about 1000 excel sheets i would like to import and create 1 access database. How can i do this?
thank you.
Well that is not a lot of detail.
You can link the sheets or import the sheets but the main question is what are you trying to achieve. Should all sheets goto 1 table or in the extreme to a 1000 tables?
Could you give some explanation of what you are trying to do?
You can link the sheets or import the sheets but the main question is what are you trying to achieve. Should all sheets goto 1 table or in the extreme to a 1000 tables?
Could you give some explanation of what you are trying to do?
ASKER
i'm doing a web project with the resulting database, so really it would need to be only 1 table
the excel sheets have the same fields, it;s just that they are in different files..
is there any way to do this automatically?
thank you.
the excel sheets have the same fields, it;s just that they are in different files..
is there any way to do this automatically?
thank you.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@perkc, did not see your comment before I posted mine.
ASKER
hello,
so which one works better? is this a VB project or this is for a macro?
so which one works better? is this a VB project or this is for a macro?
I haven't tested heer's method but they should both work.
Just create a blank(unbound) form with one command button. Add the code I posted to the onclick event of the form.
Then just open the form and click on the button to import your data.
Make sure that you change the table name that the code is importing into and that the table is already created in the proper format. You can manually import one of the files, then delete the records to get the proper format.
perkc
Just create a blank(unbound) form with one command button. Add the code I posted to the onclick event of the form.
Then just open the form and click on the button to import your data.
Make sure that you change the table name that the code is importing into and that the table is already created in the proper format. You can manually import one of the files, then delete the records to get the proper format.
perkc
Just try them both.
perkc method depends on the file scripting object which I normally avoid. Mine just uses plain VBA, both are procedures that should be stored in a module.
Database window -> Insert -> Module
make sure you save the module with a different name than the procedure.
perkc method depends on the file scripting object which I normally avoid. Mine just uses plain VBA, both are procedures that should be stored in a module.
Database window -> Insert -> Module
make sure you save the module with a different name than the procedure.
ASKER
hello,
your code it's very good. But, what do i do if the files are from the root dir spread into different folders? would it work?
your code it's very good. But, what do i do if the files are from the root dir spread into different folders? would it work?
Yes but it requires a little different approach. If the directory names are know in advance you can call the function a number of times for each directory. If not you should change the code to travel subdirectories.
ASKER
Thanks a lot!
ASKER