Export Formatted Report To Excel

555Rage

New member
Local time
Today, 07:52
Joined
Sep 19, 2012
Messages
1
I have an Access report with several text boxes that are bounded to text boxes in my form. I want to export the data in the text of the report into specific cells in excel. Sorry for my vague explanation I have attached an zip file with 2 pictures of what i'm describing.
Thanks!!
 

Attachments

Last edited:
I just want to say I have the same problem, haven't been able to find a resolution either, this seems like something someone, somewhere must have tried before!

I just found this thread, which may help:

access-programmers.co.uk/forums/showthread.php?t=143884&page=2
 
Last edited:
I dont know too much about importing into excel at the moment, its something I have been looking into myself... I was given advice to record a macro in excel for the formatting aspects and then use the code from the VB window in excel to shape your code in access.
I dont know if it helps but I have copied some code below that I use to get some of my data into excel.... perhaps it will be useful??
Private Sub CmdHeardAbout_Click()
' set references for recordset and creating an instance of excel
Dim recHeardAbout As DAO.Recordset
Dim lsSQL As String
Dim XL As Excel.Application
Dim XLBook As Excel.Workbook
Dim XLSheet As Excel.Worksheet
Dim xlChart As Excel.Chart
' get info for report from database
lsSQL = ""
lsSQL = " SELECT [HeardAbout], [CountofCompany] FROM qry_HeardAbout "
' create a recordset to be exported into excel
Set recHeardAbout = CurrentDb.OpenRecordset(lsSQL)
' create a new instance of excel and open at a new workbook, sheet1
Set XL = New Excel.Application
Set XLBook = XL.Workbooks.Add
XL.Visible = True
XLBook.Windows(1).Visible = True
Set XLSheet = XLBook.Worksheets(1)

' Create headings for data that is being exported
XLSheet.Range("A3") = "Heard About us From..."
XLSheet.Range("B3") = "No. of Agencies"

' format the properties of the cells to how you want them to appear, e.g. bold, font size, alignment etc.
XLSheet.Range("A3", "B3").Font.Bold = True
XLSheet.Columns.Range("A:A", "B:B").EntireColumn.HorizontalAlignment = xlCenter

' populate excel with data from SQL statement
XLSheet.Range("A5").CopyFromRecordset recHeardAbout

' re-size the columns to fit data that has just been imported
XLSheet.Columns.Range("A:A", "B:B").EntireColumn.AutoFit

' Add the title of the spreadsheet - ***this is done at the end, after the autofit to avoid column width being set according to long title
XLSheet.Range("A1") = "Where are leads coming from?"
XLSheet.Range("A1").Font.Size = 16
XLSheet.Range("A1").Font.Bold = True
XLSheet.Range("A1").HorizontalAlignment = xlLeft
XLSheet.Range("A1").Font.Underline = xlUnderlineStyleSingle

' reset
recHeardAbout.Close
Set XL = Nothing
Set XLBook = Nothing
Set XLSheet = Nothing
End Sub
 
Hi Access7,

Thanks for the tip for using the Macro function, I have manually exported data from a report with excel, then recorded the macro and found the code, could you highlight the part of this code where you have done so?

Haven't been able to get this to export the data! :S
 
Hi 555rage,

I assume that your report is based on a query that pulls all the information that you want.

Below is some code I have which I was able to produce with help from the experts on this site, that might be of some assistance in formatting the contents of your report into excel, it does not actually use the report object, it actually uses the query object. I've filled it with lots of comments that I hope will assist you in understanding how it was put together. I know that there are some experts out there that will frown on the amount of comments I have, but hopefully you will find this useful and it will help you to build your own version for your application.

The first block of comments are a standard set of references I use with my code modules and not all of them will be what you require for your project, so feel free to ignor those references that serve no purpose for your project.

Code:
[COLOR=green][FONT=Times New Roman]'-------------------------------------------[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'References:[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'Visual Basic For Applications[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'Microsoft Access 9.0 Object Library[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'OLE Automation[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'Microsoft ActiveX Data Objects 2.1 Library[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'Microsoft Scripting Runtime[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'Microsoft DAO 3.6 Object Library[/FONT][/COLOR]
[FONT=Times New Roman][COLOR=#008000]'Microsoft Excel 11.0 Object Library[/COLOR][/FONT]
[FONT=Times New Roman][COLOR=#008000]'Microsoft Outlook 14.0 Object Library[/COLOR][/FONT]
[COLOR=green][FONT=Times New Roman]'-------------------------------------------[/FONT][/COLOR]
 
[FONT=Times New Roman]DoCmd.Echo [COLOR=blue]False[/COLOR], "Running Program"         [COLOR=green]'Indicates in the progress bar the program is running[/COLOR][/FONT]
[FONT=Times New Roman]DoCmd.Hourglass [COLOR=blue]True[/COLOR]                                      [COLOR=green]'Turn on the Hourglass[/COLOR][/FONT]
[FONT=Times New Roman]DoCmd.SetWarnings [COLOR=blue]False[/COLOR]                                [COLOR=green]'Turn off warnings[/COLOR][/FONT]
 
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] ExcelFile [COLOR=blue]As[/COLOR] String                                       [COLOR=green]'Declare the ExcelFile variable of string type[/COLOR][/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] ExcelWorksheet [COLOR=blue]As[/COLOR] String                         [COLOR=green]'Declare the ExcelWorksheet variable of string type[/COLOR][/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] FEDB [COLOR=blue]As [/COLOR]String                                            [COLOR=green]'Declare the FEDB variable of string type[/COLOR][/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] QueryName [COLOR=blue]As [/COLOR]String                 [COLOR=green]'Declare the QueryName variable of string type[/COLOR][/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] objDB [COLOR=blue]As[/COLOR] Database                                     [COLOR=green]'Declare the objDB variable of Database type[/COLOR][/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] MyDate                                                         [COLOR=green]'Declare the MyDate variable[/COLOR][/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] MyWeekDay                                                [COLOR=green]'Declare the MyWeekDay variable[/COLOR][/FONT]
 
[FONT=Times New Roman]MyDate = Date                                                     [COLOR=green]'Assign the current date to MyDate variable[/COLOR][/FONT]
[FONT=Times New Roman]MyWeekDay = Weekday(MyDate)                 [COLOR=green]'MyWeekDay represents the current day in the week[/COLOR][/FONT]
 
[FONT=Times New Roman][COLOR=blue]If[/COLOR] MyWeekDay = 6 [COLOR=blue]Then[/COLOR]                                    [COLOR=green]'If today is Friday [day 6] then[/COLOR][/FONT]
[FONT=Times New Roman]   [COLOR=green]'Assign the location to export the Biasi excel file to and give the current date[/COLOR][/FONT]
[FONT=Times New Roman]   ExcelFile = "G:\Biasi\Biasi sent to Pauline F\WC" & "_" & Format(Date - 4, "ddmmyy") & ".xls"   [/FONT]
[FONT=Times New Roman]   ExcelWorksheet = "WC " & Format(Date - 4, "ddmmyy")                        [COLOR=green]'Assign Biasi as the name of the worksheet in the excel file[/COLOR][/FONT]
[FONT=Times New Roman]   FEDB = "G:\eFlowStatsFrontEnd.mdb"                                                       [COLOR=green]'Assign the name and path of the database to export the table from[/COLOR][/FONT]
[FONT=Times New Roman]   QueryName = "qryExportToExceltblBiasi"                                  [COLOR=green]'Assign the name of the table to be exported to the Excel file[/COLOR][/FONT]
 
[FONT=Times New Roman]   [COLOR=blue]Set[/COLOR] objDB = OpenDatabase(FEDB)                                                              [COLOR=green]'Set the objDB to open the eFlowStatsFrontEnd database[/COLOR][/FONT]
 
[FONT=Times New Roman]   [COLOR=blue]If[/COLOR] Dir(ExcelFile) <> "" [COLOR=blue]Then[/COLOR] Kill ExcelFile                                     [COLOR=green]'If the Excel file already exists, you can delete it here[/COLOR][/FONT]
 
[FONT=Times New Roman]   [COLOR=green]'Excute the creation of the Excel file[/COLOR][/FONT]
[FONT=Times New Roman]   objDB.Execute "Select*Into[Excel 8.0;Database=" & ExcelFile & "].[" & ExcelWorksheet & "] From " & "[" & QueryName & "]"[/FONT]
[FONT=Times New Roman]   objDB.Close                                                                                                      [COLOR=green]'Close the eFlowStatsFrontEnd database[/COLOR][/FONT]
[FONT=Times New Roman]   [COLOR=blue]Set[/COLOR] objDB = [COLOR=blue]Nothing[/COLOR]                                                                                       [COLOR=green]'Set the objDB to nothing[/COLOR][/FONT]
 
[FONT=Times New Roman]   [COLOR=green]'Append the contents of the tblBiasiProcessHistory table to the tblBiasiHistoryLongTerm table[/COLOR][/FONT]
[FONT=Times New Roman]   DoCmd.OpenQuery "qryApptblBiasiProcessHistoryToLongTerm", acNormal, acEdit[/FONT]
[FONT=Times New Roman]   DoCmd.OpenQuery "qryDeltblBiasiProcessHistory", acNormal, acEdit                [COLOR=green]'Delete the contents of the tblBiasiProcessHistory table[/COLOR][/FONT]
 
[FONT=Times New Roman]   [COLOR=blue]Dim[/COLOR] ObjExcel [COLOR=blue]As[/COLOR] Object                                                                                  [COLOR=green]'Declare the Excel Object[/COLOR][/FONT]
[FONT=Times New Roman]   [COLOR=blue]Set[/COLOR] ObjExcel = CreateObject("Excel.Application")                                                     [COLOR=green]'Create an instance of Excel[/COLOR][/FONT]
[FONT=Times New Roman]   ObjExcel.Visible = [COLOR=blue]True[/COLOR]                                                                                   [COLOR=green]'Make Excel visible[/COLOR][/FONT]
 
[FONT=Times New Roman]   [COLOR=green]'Open the Excel workbook Biasi with the current week commencing date[/COLOR][/FONT]
[FONT=Times New Roman]   ObjExcel.Workbooks.Open "G:\Biasi\Biasi sent to Pauline F\WC" & "_" & Format(Date - 4, "ddmmyy") & ".xls"[/FONT]
 
[FONT=Times New Roman]   [COLOR=blue]Set[/COLOR] objsheet = ObjExcel.ActiveWorkbook.Worksheets(1)                                       [COLOR=green]'Set the objsheet to active worksheet in the active workbook[/COLOR][/FONT]
 
[FONT=Times New Roman]   [COLOR=blue]With[/COLOR] objsheet                                                                                                                   [COLOR=green]'With the active worksheet[/COLOR][/FONT]
[FONT=Times New Roman]       .Rows("1:1").Font.Bold = [COLOR=blue]True[/COLOR]                                                                  [COLOR=green]'Set the first row font to bold[/COLOR][/FONT]
[FONT=Times New Roman]       .Rows("1:1").Font.Underline = xlUnderlineStyleSingle                                         [COLOR=green]'Underline the text in each cell of the first row[/COLOR][/FONT]
[FONT=Times New Roman]       .Rows("1:1").Select                                                                                                      [COLOR=green]'Insert a new row[/COLOR][/FONT]
[FONT=Times New Roman]       ObjExcel.Selection.Insert Shift:=xlDown[/FONT]
[FONT=Times New Roman]       ObjExcel.Range("A1").Select                                                                                     [COLOR=green]'Select Cell A1[/COLOR][/FONT]
[FONT=Times New Roman]       ObjExcel.ActiveCell.FormulaR1C1 = "BIASI"                                                         [COLOR=green]'Assign the text "BIASI" to this cell[/COLOR][/FONT]
[FONT=Times New Roman]       ObjExcel.Range("B1").Select                                                                                     [COLOR=green]'Select Cell B1[/COLOR][/FONT]
[FONT=Times New Roman]       ObjExcel.ActiveCell.FormulaR1C1 = "Week Commencing:"                 [COLOR=green]'Assign the text "Week Commencing:" to this cell[/COLOR][/FONT]
[FONT=Times New Roman]       ObjExcel.Range("D1").Select                                                                                     [COLOR=green]'Select Cell D1[/COLOR][/FONT]
[FONT=Times New Roman]       ObjExcel.ActiveCell.FormulaR1C1 = MyDate – 4                                   [COLOR=green]'Assign the current date minus 4 days to give the beginning of the week[/COLOR][/FONT]
[FONT=Times New Roman]       [COLOR=blue]With[/COLOR] ObjExcel.Selection[/FONT]
[FONT=Times New Roman]           .HorizontalAlignment = xlLeft                                                                                [COLOR=green]'Left align the text[/COLOR][/FONT]
[FONT=Times New Roman]           .VerticalAlignment = xlBottom                                                                               [COLOR=green]'Bottom align the text[/COLOR][/FONT]
[FONT=Times New Roman]       [COLOR=blue]End With[/COLOR][/FONT]
[FONT=Times New Roman]       .Columns("A:Z").Select                                                                                              [COLOR=green]'Select Columns A to Z[/COLOR][/FONT]
[FONT=Times New Roman]       .Columns("A:Z").EntireColumn.AutoFit                                                  [COLOR=green]'Autofit the columns to their contents[/COLOR][/FONT]
[FONT=Times New Roman]   [COLOR=blue]End With[/COLOR][/FONT]
 
[FONT=Times New Roman]   ObjExcel.ActiveWorkbook.Close [COLOR=blue]True[/COLOR]                                                                        [COLOR=green]'Close the Active Workbook[/COLOR][/FONT]
[FONT=Times New Roman]   ObjExcel.Quit                                                                                                                    [COLOR=green]'Close the Excel Application[/COLOR][/FONT]
 
[FONT=Times New Roman]   [COLOR=blue]Dim[/COLOR] olApp [COLOR=blue]As[/COLOR] Outlook.Application                                                                             [COLOR=green]'Declare the outlook application variable[/COLOR][/FONT]
[FONT=Times New Roman]   [COLOR=blue]Dim[/COLOR] olMail [COLOR=blue]As[/COLOR] MailItem                                                                                  [COLOR=green]'Declare the Mail Item variable[/COLOR][/FONT]
 
[FONT=Times New Roman]   [COLOR=blue]Set[/COLOR] olApp = New Outlook.Application                                                                        [COLOR=green]'Set the olApp to a new outlook application[/COLOR][/FONT]
[FONT=Times New Roman]   [COLOR=blue]Set[/COLOR] olMail = olApp.CreateItem(olMailItem)                                                 [COLOR=green]'Set the olMail to create an outlook mail item[/COLOR][/FONT]
 
[FONT=Times New Roman]   [COLOR=green]'Create and send the email with an excel attachment[/COLOR][/FONT]
[FONT=Times New Roman]   [COLOR=blue]With[/COLOR] olMail[/FONT]
[FONT=Times New Roman]   .To = "Pauline.Fiddimore@domesticandgeneral.com"                                              [/FONT]
[FONT=Times New Roman]   .CC = "maria.leonard@domesticandgeneral.com"[/FONT]
[FONT=Times New Roman]   .BCC = "elaine.boulton@domesticandgeneral.com"[/FONT]
[FONT=Times New Roman]   .Subject = "Biais Weekly Excel Spreadsheet"[/FONT]
[FONT=Times New Roman]   .Body = "Please find attached the weekly Excel Spreadsheet."[/FONT]
[FONT=Times New Roman]   .Attachments.Add "G:\Biasi\Biasi sent to Pauline F\WC" & "_" & Format(Date - 4, "ddmmyy") & ".xls"[/FONT]
[FONT=Times New Roman]   .Send[/FONT]
[FONT=Times New Roman]   [COLOR=blue]End With[/COLOR][/FONT]
 
[FONT=Times New Roman]   [COLOR=blue]Set[/COLOR] olMail = [COLOR=blue]Nothing[/COLOR]    [COLOR=green]'Set the olMail to nothing[/COLOR][/FONT]
[FONT=Times New Roman]   [COLOR=blue]Set[/COLOR] olApp = [COLOR=blue]Nothing[/COLOR]     [COLOR=green]'Set the olApp to nothing[/COLOR][/FONT]

Regards

John Lee
 

Users who are viewing this thread

Back
Top Bottom