Exporting multiple reports to one Excel Spreadsheet

Is there a way to export multiple reports from Access into ONE Excel Spreadsheet? I have a database that houses supplemental health insurance information. The company wants a "final report" that will include information from prior years for quoting purposes. The "final report" currently resides in Word and has references to an Excel Spreadsheet, so is there a way to set up some code to export the reports all into one spreadsheet? The reports have vastly different information in them so there is no way to have it all in one big report.

Also, is there an easy way to import a Word document into Access as a report and keep the formatting (such as the bullet points)?

Thanks for any help you can give!!!


Post your answer or comment

comments powered by Disqus
Hi all,

I am wondering if anyone can help me figure out what I should be doing in order for me to export multiple queries to one single excel file.

Currently, I have this EXPORT button in my main form where when I click on it, it ask me to save the file/export to my computer. The exported file thus consists of only ONE queries but I wanted more queries to be exported in different worksheet under the same excel file.

So far my codes are:

Public Sub ExportXLS()

#If Not CC_Debug Then
On Error GoTo ErrProc
#End If

Const cQuery As String = "qryExportMetrics"


Dim fc As FileChooser
Dim strFileName As String

Set fc = New FileChooser
fc.DialogTitle = "Select file to save"
fc.OpenTitle = "Save"
fc.Filter = "Excel Files (*.xls)"
strFileName = Nz(fc.SaveFile, "")
Set fc = Nothing

' If user selected nothing or canceled, quit
If Len(strFileName) = 0 Then
Exit Sub
' If file already exists, delete it
ElseIf Len(Dir(strFileName)) > 0 Then
Kill strFileName
End If

DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel9, _
cQuery, _
strFileName, _
HasFieldNames:=True




ExitProc:
Exit Sub
ErrProc:
ErrMsg Err, Err.Description, Err.Source
Resume ExitProc
End Sub


Any feedback?

Thanks in advance

Is there a to convert multiple reports to one PDF with VBA? I have a large amount of pdfs to produce around 4000. One of my other large problems I can not download any tools to help with this. I am using access 2010 and I have Adobe PDF Writer installed.

Hi.

I want to export my report to an excel file exactly the same as the inbuilt method, but with a fixed path to save it...is this possible??

Code currently using...

stDocName = "saveReportFormat"
DoCmd.OutputTo acReport, stDocName

is there a way i can do this, but not have the save window pop up and have a hard coded file path??

also i dont want to use...

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, reportName, theFilePath, True

because this removes all formatting and only works on query's...unless there is a way to manipulate this code to save reports to file instead of queries?

thanks in advance

All,

I have seen it somewhere and have searched all of these forums and couldn't find the answer. The question: I have two reports that currently are set to print when a button is clicked.


Dim strDocName1 As String
Dim strWhere1 As String
Dim strDocName2 As String
Dim strWhere2 As String
strDocName = "Report"
strWhere = "[ID]=" & Me!ID
strDocNames = "Synopsis"
strWheres = "[ID]=" & Me!ID

DoCmd.OpenReport strDocName1, acViewNormal, , strWhere1
DoCmd.OpenReport strDocName2, acViewNormal, , strWhere2

I want to be able to write these two reports to one rtf file? Also, I want the name of the file to be the date and time or combonation there of that will always be unique, as I am going to have this write to the users desktop. Any help is appreciated.

Hey,

Here is my situation. I have queries that displays email addresses of our employees in each department. Due to employees leaving from time to time, these queries are updated regularly to accomodate for those changes. I also store these email records in a large Email Contact workbook in Excel with each department having its own worksheet tab. When data is updated in access, I want it updated in the excel workbook as well so I can send it off to certain people. My first worksheet in my excel workbook is where I make Counts of all of the records in each department (tab). When updated, the number will change according to the number of records in each tab.

*Each tab's title is identical to my query's name. So I want it to overwrite.

What I have been doing is creating a macro using the TransferSpreadsheet action and exporting the queries to individual Excel spreadsheets in a directory. Then, I would import those individual spreadsheets into the large workbook and making changes that way. I was hoping there is a more efficient way of doing this.

So I have been trying to work this out and I have started to get close to where I want but I am having trouble with something. I have gone away from using macros so I created several modules for each export query. Here is the code:


	Code:
	Public Sub ExportQuery1ToExcel()
Const FILE_PATH As String = "C:Directory"
Dim FullPath As String
    strFullPath = FILE_PATH
    DoCmd.TransferSpreadsheet acExport, , "QUERY_NAME", strFullPath & "EXCEL WORKBOOK", False
    MsgBox ("Export is complete.")
End Sub

I would use this same code for each query export, changing the query name in the code.

The problem I am encountering is that rather than overwriting the worksheets in the excel workbook, it would just create a new worksheet tab with the title, "QUERY_NAME_1".

So, it exports the queries to the existing excel workbook but it won't overwrite the same worksheets. Any ideas?

Thanks, much appreciated

Hi,
I do know how to export a query to excel, Is there a way to export several queries to one excel sheet in Access 2007?
Thanks in advance

Only other problem Im having is the above.

I can setup a macro to print the reports and I can convert each one to PDF on its own.

Can you Output all reports to one PDF?

Thanks in advance

Sam

Hi all, I have a report which based on a crosstab query, and I add a grouping by using the group&sort button on the design toolbar. The grouping is on one of my crosstab query row head.

Now I want to export report to excel with each grouping as an individual worksheet in one excel file, which means multiple worksheets in one excel but based on only one crosstab query and grouping report. Can anyone give me some thought about how to achieve this?

Thanks a lot!!!

Hello,

Here is my setup. I have one Master Table containing data from multiple departments. I have 5 queries, one for each department, that sort my data. My goal is to take the average of one column from each query and export them to an excel spreadsheet.

The spreadsheet would look something like this:

Date | Avg Department #1 | Avg Department #2 | etc etc etc.


I'm looking at bob larson's website but I'm not well versed in VBA coding. I've also tried to put all 5 averages onto one query but it's just too complicated of a procedure. Any more than 3 of the 5 values causes the code to run forever.

Any help would be much appreciated. Thanks!

-Dane

Edit: I forgot to mention that I also want to do this process once a week, every Monday. This means when the file exports, I need excel to insert the data into a different row each time.

I'm trying to export a group of four reports to an Excel spreadsheet and its workbooks, but having absolutely no luck. Does anyone have a sample piece of code that might show how to export various table fields to a spreadsheet's workbook, then switch to the second workbook to export some more fields and so on?

Thank you for your help in advance!

Hi,

I want to export a report to excel. Can I do that with a macro? I'm looking through the choices and the Transfer Spreadsheet option is asking me for a table, not a report.

Thanks

Eddie

I have form to generate a report, where the user selects certain parameters for which I use VBA to filter the report on, ultimately using the OpenReport method. No problem, as I wind up with something like this:

DoCmd.OpenReport "rptName", acViewPreview, , "City = 'Springfield'"

But, I now want to be able to export the report to Excel. The OutputTo and TransferSpreadsheet methods don't seem conducive to using filters. Is there a simple way to export the report I opened above into an Excel spreadsheet without jumping through too many hoops?

Thanks in advance.

I have a database that produces timesheets for employees based on employee information and grouped by department information in tblDetail. I have a macro that exports the report Timesheets into excel. I need one Timesheet report to for each department to be exported to excel. So I think I need to create a module that contains a loop that looks in the tblDistribution for the Department id the exports each report to excel. I am stuck on how to do this. Please help. I need to make this as easy as possible for the payroll person. At the moment they are printing reports and manually keying in all the information one person at a time. I have the two table names below...


tblDetail.EmpliD, Pay Group, fileNbr, Period End Date, Pay Date, Day, Hours, Code, Shift, Amt, Temp Dept, Dept ID, Department Name, Department Mgr, Deptsupv, Std Hours, Name, Mgr Name, Mgr Email

tbl_Distribution.Dept ID, Dept Timesheet Manager, Department TimesheetManager Email, Department Name

Thanks,
Juliana

Dear All,

I need to export multiple queries into a single spreadsheet in different range of cells. Means one query need to be exported from B2:E2 and second query need to be exported from B10:E10. In this way need to export 18 queries' result into one sheet only on different name range.

If anyone has sample VBA code then please help to post it.

Any help would be appreciated. I am using Access 2007 and need to export data into Excel 2003 format.


Thanks a lot for the help.

Regards
Ria

Team,

How do I get multiple reports to export & save as a pdf then attach to a single email?

I'm using the code below, but I have no idea how to tailor it to handle more than 1 report.


	Code:
	Private Sub Email_Decorations_Click()
Dim strRep As String
Dim strDPath As String
Dim strFName As String
' What report to send
strRep = "Decorations Pending"
' Initial Path
strDPath = "Path name here"
' Filename
strFName = "Decorations.pdf"
' Output report as pdf
DoCmd.OutputTo acOutputReport, strRep, "PDFFormat(*.pdf)", strDPath & strFName, False, "", 0
' Send the report to whoever
Send_Dec (strDPath & strFName)
End Sub
Private Sub Send_Dec(strDoc As String)
Dim sTo As String
Dim sCC As String
Dim sBCC As String
Dim sSub As String
Dim sBody As String
Dim strCC As String
Dim OutApp As Object
Dim OutMail As Object
Dim varPress As Variant
    ' Get the email address from the current form control
    sTo = ""
 
    '  Set the subject
    sSub = "Evals & Decs"
    ' Build the body of the email
    sBody = "Team," & vbCrLf & vbCrLf
    sBody = sBody & "Here is the current decorations list for action."
 
    ' Create the email
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    sCC = ""
    sBCC = ""
 
 
    With OutMail
        .To = sTo
        .CC = sCC
        .BCC = sBCC
        .Subject = sSub
        .Body = sBody
        .attachments.Add (strDoc)
        .display
 
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub



I’m using Access 2002. I can generate a nice report in Access, with color background for the header and each section. But when I export the report to Excel, (or analyze with Excel), the nice format are lost in Excel file. Each section’s header is moved to the first few columns and no color background anymore, just plain data.

Is this common? How to make the report looks the same in Excel? Do it manually?
Thanks much!!!

I can export the report to excel on other machines. But when I try it on mine the export, excel icon is not highlighted. There is something that is not check on my machine. Have any clues?

The db is split and running 2007. I am the admin so I should have full rights.
.

I've searched the forum alread to see if this is possible and maybe it's just my choice of search criteria, but I haven't found anything.

I am looking for a way to export data from different queries to one excel spreadsheet, but it needs to be able to export the different query info to different sheets.

Any Ideas, or point me to the right previous post.

This will be done through code because it will require user input to determine what information to export.

Hi guys and gals,
I need help with the following:

Original Data:
Name Bank Name George Citibank George Wamu George BankofNY John Chase John Wamu Victor Dime

Processed data:
Name Bank Name George Citibank, Wamu, BankofNY John Chase, Wamu Victor Dime

OK so I did all of this.
The original data is stored in a table, I used VB code (found on this site) to process the data and put it into a report. Now I need to export the report into an Excel spreadsheet, I tried many different ways even with VB code (found on this site) but I keep on getting the same error, Subscript out of range

Anyone have any ideas???

What is the code that would have a command button export all the data from a form to an Excel Spreadsheet. The users do not have the option to choose "File" and "Export" the data from the form. I am just assuming that a command button is the best way to do this. If a macro would be better, then how would I do that? I don't know VBA or macro's. Thanks for your help.

What would the VBA command line look like to export a report to Excel using a command button ?

I am wondering if anyone can help me figure out what I should be doing in order for me to export multiple queries to multiple ranges in one single excel file.

I have 3 queries, each of which returns records from a different table. I would like to export the results from Query 1 to range A1:J7500 of a worksheet, the results from Query 2 to range K1:O7500 in the same worksheet, and the results from Query 3 to range P1:S7500 in the same worksheet.

I have created a macro with 3 TransferSpreadsheet actions, each referring to a query and the desired range. When I run the macro, the results from Query 1 get exported to A1:J7500, but an error message displays when Access tries to export the results from Query 2 into range K1:O7500. The error message is "Table K1:O7500 already exists".

Is it possible to do what I want to achieve using the TransferSpreadsheet action, or should I be approaching this from a different angle? I have limited VBA skills, so would prefer to avoid having to code something if possible.

I have a report which is based on a crosstab query for showing averages over the year; I have no problem outputting this report to an excel spreadsheet. I then added to the report another 12 text boxes to show me moving averages over the year but when i now output this to excel it does not show the figures obtained from the calculations done within these new text boxes.
Hope this makes sense to someone
Go raimh maith agat........Christy

[This message has been edited by christy (edited 10-04-2000).]

Hi,

I have imported all my Access 2003 db objects into a blank Access 2010 db. I'm now going through to make sure everything works as expected, in 2010.

I am puzzled that, in my macro (which I include in a Switchboard board option) that allows users to choose a file type to export their report to, only old versions of Excel are available (i.e. Excel 97-2003, or Excel 5/95)! Why would they not have in their pull-down menu the new (*.xlsx) Excel file version? I want users to be able to export their Reports to the new Excel file type.....

Am attaching a jpg file that shows the macro and the selections it is giving me.


thanks in advance for any insights....
Sarah


Not finding an answer? Try a Google search.