Using the following VBA code should enable you to export the table to excel
Private Sub ExportOneTable() 'EXPORTS TABLE IN ACCESS DATABASE TO EXCEL 'REFERENCE TO DAO IS REQUIRED Dim strExcelFile As String Dim strWorksheet As String Dim strDB As String Dim strTable As String Dim objDB As Database 'Change Based on your needs, or use 'as parameters to the sub strExcelFile = "C:\My Documents\MySpreadSheet.xls" strWorksheet = "WorkSheet1" strDB = "C:\My Documents\MyDatabase.mdb" strTable = "MyTable" Set objDB = OpenDatabase(strDB) 'If excel file already exists, you can delete it here If Dir(strExcelFile) <> "" Then Kill strExcelFile objDB.Execute _ "SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _ "].[" & strWorksheet & "] FROM " & "[" & strTable & "]" objDB.Close Set objDB = Nothing End Sub As for the other problem i thing you will be able to pick up the graphs title to use on the report, i have never done this but assuming you get the export working let me know how you get on, and i can have a further look --- In [email protected], allykatt04 <[EMAIL PROTECTED]> wrote: > > Ritchie (and anyone else with insight)- > > I made second-copies of the queries and converted one set to make- > table queries, put those into excel and made my graph (because you > can't run a report off of an action query.)But I have run into some > new questions. > > 1) Is there a way to export the table from access instead of > importing it from excel? If the data is imported using excel, the > access file cannot be saved under a new name or moved (which is > unrealistic even for construction) > > 2) I need to put the graph in the detail section of the report. (so > it should be different based on which title it is under. Will that > be possible using this method? Can the report filter the excel data? > or will I end up with 40 of the same graph? > > thank you for your time, > ~Allison > > > --- In [email protected], "rchlon9" <ritchie9710@> > wrote: > > > > You may have already found the answers to your problems, if not > > however there are several ways around your problem. > > > > Maybe the easiest would be to make the query which forms your > access > > report into a make table query. Then use ms query in excel to > import > > the table you have just made instead of importing the access query. > > > > Another method would be to remove the User Prompt from your query > > within access and instead set up parameter fields in excel, this > > would the create the same results as what your original access > query > > gave you. > > > > Hope this helps, if you need more details please just let me know. > > Regards > > > > Ritchie > > > > > > --- In [email protected], allykatt04 <no_reply@> > > wrote: > > > > > > Hello, I am a WPI student working on a database project for an > > > internship. I have created a database in ms access 2000, and I > am > > > putting together some reports to display the data. > > > > > > When the report opens, a macro loads to open a form and ask the > > user > > > for a date range. The report is then filtered (because it is > based > > > on queries that need the user-entered form data) and returns only > > > data within the range. > > > > > > Within the report, I need to include a custom bar/line (line for > > > goal%, bar for actual%) graph based on sums and totals from text > > > boxes in the report. I have NO idea how to do this. Access > > doesn't > > > have the right graphs, and only lets you use 6 columns (i need 12- > > > > One for each month of a year with varying start months) > > > > > > I am guessing that I need to export the data into excel (which > has > > > the right graphs, and would display it properly) and then import > > the > > > resulting graph back into access, but I am not sure how to do > this > > or > > > if it's even possible. Because the data is based on a user- > entered > > > date range, I can't figure out how to even link it into excel. (I > > > keep getting a "Too Few Parameters. Expected 2." Error message > from > > > Microsoft Query.) I read something about TransferSpreadsheet > > > Methods, but I am unsure if this is a solution to my problem and > > > how/where/when to use them in the first place. > > > > > > Thank you for your time, > > > ~Allison > > > > > >
