 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Importing / Linking / September 2005Auto import data from spreadsheet
Alright I am trying to set up a database for work for my coworkers to use. That means I have to make it as simple as possible for them to understand how to use it. What I would like to do is this: Import data from an excell sheet into an access data base. (Can I use a form to do this instead of the file -> import external data -> etc ?) Also is it possible to import all the worksheets of a excel spreadsheet at once? (Each sheet will have its own database.) Lastly when I import data from an excel spreadsheet can I have access add a column onto the end with the date? I might be making this harder than it needs to be so. Any advice/sudgestions are appreciated. >Alright I am trying to set up a database for work for my coworkers to use. >That means I have to make it as simple as possible for them to understand how [quoted text clipped - 3 lines] >Import data from an excell sheet into an access data base. (Can I use a form >to do this instead of the file -> import external data -> etc ?) Yes. Use the code at http://www.mvps.org/access/api/api0001.htm to display the File|Open dialog so the users can select the file. Then pass this name to the DoCmd.TransferSpreadsheet statement to import. If they also need to select a sheet, there's a bit more work involved. >Also is it possible to import all the worksheets of a excel spreadsheet at >once? (Each sheet will have its own database.) No and yes. You can't import multiple worksheets simultaneously, but it is possible to write code that imports all the worksheets in a workbook one after the other without user intervention. When you say "each sheet will have its own database", do you mean that your workbooks each contains the same set of worksheets, and that there's a table in your database corresponding to each of these? Or are you intending to import every single worksheet to a new table or new .mdb file? If you are, you need to think about what you're doing, because the result won't be a database so much as a mess of data that will be no easier to work with than if you'd left it in Excel. >Lastly when I import data from an excel spreadsheet can I have access add a >column onto the end with the date? Yes. Assuming you're importing from standard worksheets into tables you have already created, add the timestamp column to each table. Then the simplest thing to do is to import the data without adding a date, and after each import run an update query to add the date to the new records, e.g. CurrentDB.Execute "UPDATE MyTable SET TimeStamp = Now() WHERE TimeStamp IS NULL;", dbFailOnError It's also possible to import the data and add the timestamp in one go, by importing with a query instead of with the usual TransferSpreadsheet command. >I might be making this harder than it needs to be so. Any advice/sudgestions >are appreciated. Think hard about your data structure. That's almost always good advice. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. Thanks for the reply John. I took your advice and thought about my data structure again and decided to revise it. I was going to have a table for each sheet (This way there would be minimal changes to each.) However I added a new hidden coloumn to identify the name of each sheet. So I am going to keep working on this and will probably be back for some more help. Thanks Kris > >Alright I am trying to set up a database for work for my coworkers to use. > >That means I have to make it as simple as possible for them to understand how [quoted text clipped - 50 lines] > > Please respond in the newgroup and not by email. I am doing a similar task to KingKong, but I need to be able to import every worksheet in the workbook. Is there some wildcard selection that can be used for the range portion of transferSpreadsheet command? So far I've basically combined the code found at the link mentioned below with the code @ http://www.mvps.org/access/general/gen0008.htm Thanks in advance, mike > >Alright I am trying to set up a database for work for my coworkers to use. > >That means I have to make it as simple as possible for them to understand how [quoted text clipped - 50 lines] > > Please respond in the newgroup and not by email. just an update to the last post, all worksheets are identicle in their layouts. The data is field data from equipment that outputs to excel files. One workbook represents all the field data for one year, with each worksheet representing the data for each site visited. > I am doing a similar task to KingKong, but I need to be able to import every > worksheet in the workbook. Is there some wildcard selection that can be used [quoted text clipped - 59 lines] > > > > Please respond in the newgroup and not by email. No, you have to pass the actual sheet or range names each time. Search http://groups.google.com for <4ofsq01pl9tnnejfjj17ahipmp7u4ohul5@4ax.com> and you'll find a posting from me with instructions on how to find a function that returns a list of the worksheet names and how to use it. >I am doing a similar task to KingKong, but I need to be able to import every >worksheet in the workbook. Is there some wildcard selection that can be used [quoted text clipped - 59 lines] >> >> Please respond in the newgroup and not by email. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. Iam trying to create a link between an Excel spreadsheet and an Access table. I would like it to update the Access database when information is entered in the Excel spreadsheet. I have followed the instructions using the Wizard but when I try to finish the link it says " Can't overwrite table or query 'Assets'. What does this error mean? My problem is that my boss does not know how to use Access and wants to update the assets in our company through excel but I want it in Access so that I can run queries and reports for her. Please help. > No, you have to pass the actual sheet or range names each time. Search > http://groups.google.com for [quoted text clipped - 72 lines] > > Please respond in the newgroup and not by email. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.