I need to export a table from Access to an Excel spreadsheet. I have two questions though:
1- is there a way to have it export to the same worksheet every time? I have a
2-is there a way to have it go to a different Excel template other than just the standard workbook? I have a template that I use with all sorts of formulas and what not in it, and I’d love to be able to export from Access directly to this workbook. Problem is the formulas refer to cells on a specific worksheet, and when the table exports to the Excel sheet, it just creates a new sheet. I want it to just go to the same sheet every time.
Perhaps you can set up a live link to the Access data? I haven’t done it with Access much, mostly SQL Server, but I just connected to an Access table with Excel by using the “Import External Data/New Database Query” command under “Data”. You’ll need to have MS Query installed to use the command (an add-in included with Excel).
It’s pretty obvious from there, but let me know if you need more help.
You can totally do what you want to do. I do it everyday. The trick/catch is how much work you want to do in order to get it done.
If all you want to do is click menu items, then I’m afraid you will be stuck with basically “mass” exports: whole tables to workbooks, etc. Even if you use “macros” all you’re really doing is automating menu clicks and its very “broad” in the way it works.
What you need is called Visual Basic for Applications (VBA) also call Office Automation. It allows you to take complete control of Office applications and plant data exactly where you want it, whenever you want it. Among other glorious things.
There’s alot to learn, but its not hard. Too much to teach here. I’d be more than happy to help you. Send me a note at jamesdcarroll@hotmail.com with a title that says “SD Access Excel Help” and I’ll help you in any way that I can.
Take care.
I really appreciate the help–and I will be sending you that e-mail. Thanks!
A simple workaround may be to export the query or table to one particular workbook (which should get overwritten with each export) using a macro for future automation. Then go into your good formula-laden worksheet and link the cells to the corresponding cells in the export workbook. When you open the good formula workbook, a window will popup and ask you if you want to update from external sources. Choose yes and the formula sheet will grab the correct values from the exported sheet each time it’s opened.
If you want more flexibility I would certainly suggest the VBA approach mentioned by JamesCarroll, that’s definitely the way to go for custom Access/Excel exports. The only downside with this method is that VBA code runs extremely slow (eating up 100% of the CPU time), which won’t be a problem with a small export but will turn a 2,000 record, 50 field export into a 2 hour job even on a fast computer. There are certainly ways to optimize your VBA code, however I’m a firm believer in KISS: Keep It Simple, Silly.
thanks Mirage.
I had considered your solution (and I still am), but I’d rather that the individual who is going to be using this each day not have to open the spreadsheet at all. She will just run the report, which actually places the file on a network drive, and then notify her manager that the file is on the network waiting for her.
The file is generally under 100 lines , with about six or seven columns, so I don’t think it will be a huge problem for her. Having her CPU tied up for a while so a program can is preferable to her manually tying up the CPU for hours trying to figure out how to do all of the importing and exporting this project will require. I need this to be very simple for her to run.
If you use SQLServer in your office, you might check out Data Transformation Services (DTS). Its a nice tool that can import data from one application and export to another. The tool is part of SQL Server, but the data doesn’t have to be put in a SQL Server table, it can go directly from one application to another. You can save the task, and run it manually each time you want it, or you can set it to run automatically at a certain time each day. And, if you want, you can set up automatic email notification as part of the task. You can set it to send a particular message on success, or a different one if the task finishes with an error. Its probably going to be a lot easier than learning VBA.
thanks Peter. We are not running SQL server here; the data is actually coming from someplace that does run it, but “security issues” prevent us from having access to the actual SQL. An SQL query is what generates the text file I am starting with, but I have looked into something similar in the past and was told NO, NO, NO we are not allowed access to that.
No big deal though–I’ve been wanting to learn visual basic for some time and this is going to hopefull force me to.