Importing an excel spreadsheet into and existing Access tbl

[amypaulsen]

I'm trying to pull in data from excel and dump it into a table within Access that is linked to queries. I'll need to do this once a month so I need to have it pull into the appropriate Table. I tried an append query and cant get it to work.

HELP!

 

[jmersing]

Re: Importing an excel spreadsheet into and existing Access

I think you should use the "transfer spreadsheet" function. It is an option if you create a macro. You just have to list some information on the location of the file and the range you want to import. Once the data is imported, then write an appendd query to take only the records you need and place them into your table.

If you need help with that process, just re-post.

I'm not sure if you were trying to run the append query against a spreadsheet or if the spreadsheet was linked as a table in your db?

 

[amypaulsen]

Re: Importing an excel spreadsheet into and existing Access

I am a new user at Access but an expert at Excel. It's a bit different and is frustrating the heck out of me.

I have created an append query, but everytime I test it, it adds new records. The ultimate goal is to have a spreadsheet that automatically gets updated into a file that updates a query and in turn, updates a different excel file using SQL. Each append query should only be used once throughout the year...but if the user accidentally hits the run button, it adds files.

HELP!

 

[Joe4]

Re: Importing an excel spreadsheet into and existing Access

Are you "weeding out" any of the records you are importing into Access and that is why you are attempting to do an Append Query?

If you simply want to include all the records, you should be able to import them directly into the Access table (no need for an Append Query).

 

[amypaulsen]

Re: Importing an excel spreadsheet into and existing Access

I'm trying to make the process as automated as possible and therefore requiring the user only to import a file. They replace an existing file which is attached to an append file, which updates the main table which in turn, updates the main Excel file. Hope this makes sense. I'm sure there is a better way to do this I just don't know access well enough to have a clue.

Thanks

 

[Joe4]

Re: Importing an excel spreadsheet into and existing Access

therefore requiring the user only to import a file. They replace an existing file which is attached to an append file, which updates the main table which in turn, updates the main Excel file.

Are you importing a file from Excel into Access, and then spitting it back out to Excel?

Could you map out the steps, explaining in detail the reason/criteria for each step (i.e. if you are using a query to "weed out" records, please indicate it at that step). I don't quite understand why you are doing the process like this.

Without understanding the process and the goal, it is very difficult to help.

 

[amypaulsen]

Re: Importing an excel spreadsheet into and existing Access

Sorry...not trying to be difficult...I need to do a weekly/and seperately, a monthly report. The managers in charge want the end result to come out in Excel, but the data housed in Access. The entire thing works well in Excel but takes longer than necessary, uses up too much space, and requires multiple sheet updates if an account is added. Access will allow this all in one step.

Once a week, the field enters their weekly numbers (which are preliminary) into the access database. A roll up is done and printed out in Excel (this includes about 180 accounts with 20 items recorded on each...40 some pgs of output). Once a month, I am required to do a period update that reflects final numbers. This requires a pull from another system (essbase) that will only dump out into excel and then I have to put it into the database while using each preliminary week + monthly finals for QTD & YTD numbers.

I hope this makes sense...I know it's crazy but I am not the decision maker...only the "doer"...LOL

 

[Joe4]

Re: Importing an excel spreadsheet into and existing Access

I am still not sure I see the need for an Append Query.

I think you will have a large table, and you will keep importing the data every month (or week) directly into this table (you may need another table for the data from your other database). You can then use queries to select which data you want to work with/extract.

A few notes/hints which may or not pertain to your situation:

1. You don't necessarily need to import Excel data into Access to work with it in Access. You can simply "link" the table into Access. The advantage to doing this is that any changes you make to the data (in Access or Excel) with automatically be reflected in the other program.

2. As jmersing alluded to, you can set up automatic import/export functionalities by the using the TransferSpreadsheet Action under Macros. You will need to set up File Specifications for importing/exporting in order to do this (simply do a manual import/export and Save the specifications).

Hope this helps you devise a plan of attack. Post back if you need more assistance.

 

[amypaulsen]

Re: Importing an excel spreadsheet into and existing Access

maybe this is a better question...

I need to import a spreadsheet once a month from Excel that will automatically update 8 queries within Access. I don't want to have to go into each query and add the table name and the information that I need, is there a way to pull in the table and have it recogize the table name & automatically update each query?

 

[Joe4]

Re: Importing an excel spreadsheet into and existing Access

Queries, by nature, are run on tables, and are automatically updated (once refreshed) whenever any of their source data (tables/queries) are updated.

So if the data source for your query is "Table A", and you import new data into "Table A", when you re-open your query it will automatically reflect those updated changes to your table without any intervention from you.