Export query to excel
I've done some searching around and found similar posts online but I'm having a tough time getting my head round this one.
I have a query called "QryMonthlySales" with about 10 fields and 400 rows of data. I want to export the query to excel, saving the excel file to a folder location chosen by the user via a form.
Before exporting the query to excel, I want to give the user the option to specify the time period. I have a form which allows the user to select a start and end date. Before exporting the query, I will need to select the data via a where clause, something like Period >= txtStartDate and Period <= txtEndDate.
Also, when exporting the data, the column headings are given as the actual field names not as the caption. This looks quite ugly and will be annoying for the user to have to rename
Thank you for taking the time to report an issue.
What's wrong... Please write below.
IS there a way to export to excel only what is displayed on the Query & not on what is hard coded
But when I try to use the TransferSpreadsheet macro action or command, it pops up the "Enter Paramter Value" window (same occurs if I use the excel export button and do not select the option "Export Data with Formating and Layout"). I tried to check the apprently wrong expressions but the queries work fine and I guess it is related to the fact that the records are too large. My question is, how can I still use a macro to automate the export? I do not want to do the export manually as I have over 30 queries to export to different excel worksheets.
Lastly, the format of the data (pivot table, datasheet.) should also be optional.
I know access must be capable of this, because pushing the "Export to excel" button does exactly what I'm looking for, with any active query or table. Is there any way to harnessthe automation of the "Export to Excel" button through VB?
Everywhere I look seems only to have ways to export an existing query to an existing workbook.
into [excel 8.0;database=C:\Documents and Settings\bconner\Desktop\Access to Excel.xls].[Tri AR By Rej]
When using the above after the first time it says the the table already exists and then bombs.
Is there an alternative way of exporting an Access query to a specific worksheet within an excel workbook?
On average, the query returns 30k-60k records so I am dumbfounded as to why it will not export.
Simply put, I want to export a query from Access 2007 to Excel 2007 to a .xlsm file.
When I have a query open and click [External Data]>[export to Excel] the xlsm file format is not even in the list.
But it doesn't happen when I use
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strDocName, strFullName, True
I have cleaned the code from some tests but the export looks like this:
Does someone know why this happens and how I can prevent it?
1) DoCmd.TransferSpreadsheet(acExport, , [Queryname], [FileName], True)
but the excel design is different from how to query's looks like. for example, but in the excel file it's from left-to-right.
how can I preserve my design settings in the excel file?