I am getting started with exports using VBA and I am having trouble finding documentation "for beginners" where it explains step by step how to export a report to Excel and how to format it, as the default Export that Access offers does not fit the needs of my project.
More specifically, in my database I have registered a list of events that contain different items and I have managed to create a report that shows the information related to the event on which I click (I linked the report with the field that gets clicked on with a Macro code that I found on Microsoft Support's site).
I would like to export this report to Excel, so that I can have one Excel document for each event.
Thank you very much
some Formatting on report may Not come Out when you export to excel.
to export report use DoCmd.Outputto (google).
some Formatting on report may Not come Out when you export to excel.
to export report use DoCmd.Outputto (google).
This is the code I have at the moment:
Private Sub ExportOverview_Click()
On Error GoTo SubError
DoCmd.OutputTo acOutputReport, "EventSummary", acFormatXLS, "\\path\Events archive\" & [Event] & ".xls"
MsgBox "File exported succesfully", vbInformation + vbOKOnly, "Export success"
SubExit:
Exit Sub
SubError:
MsgBox "Error number: " & Err.Number & "*" & Err.Description, vbCritical + vbOKOnly, "An error occurred"
GoTo SubExit
End Sub
The problem I have is that it records in Excel all the events with their information instead of just the event that is shown in the report: I get one document with the name of the event I selected but the information is a list of all the events, one after the other.
Regarding the formatting, I just want to choose the cells in which each piece of information should be recorded and maybe some formatting for headers and borders.
you need to Open the Report (with filter and hidden) first.
Private Sub ExportOverview_Click()
On Error GoTo SubError
docmd.OpenReport "EventSummary",acViewPreview,, "[eventID]=" & me.eventid, acHidden
DoCmd.OutputTo acOutputReport, "EventSummary", acFormatXLS, "\\path\Events archive\" & [Event] & ".xls"
MsgBox "File exported succesfully", vbInformation + vbOKOnly, "Export success"
DoCmd.Close acReport, "EventSummary"
SubExit:
Exit Sub
SubError:
MsgBox "Error number: " & Err.Number & "*" & Err.Description, vbCritical + vbOKOnly, "An error occurred"
GoTo SubExit
End Sub
I see that you have your question answered.
I'm posting a link to some VBA code for anyone else who sees your question because I have found it very helpful in some products I have developed.
I believe the code is from a former member here "Bob Larson" and is currently held on the BTAB Developments site website here:-
Copy this code into a standard (not form or report) module. Name the module something other than this function name and make sure to set a reference to DAO if you don't already have one. Public Function SendTQ2Excel(strTQName As String, Optional strSheetName As String) ' strTQName is the name of.
btabdevelopment.comI see that you have your question answered.
I'm posting a link to some VBA code for anyone else who sees your question because I have found it very helpful in some products I have developed.
I believe the code is from a former member here "Bob Larson" and is currently held on the BTAB Developments site website here:-
Copy this code into a standard (not form or report) module. Name the module something other than this function name and make sure to set a reference to DAO if you don't already have one. Public Function SendTQ2Excel(strTQName As String, Optional strSheetName As String) ' strTQName is the name of.
btabdevelopment.comThanks. I am still having trouble to format the Excel once it is created. Do you know how can I adapt this code that you shared to modify my Excel?
I answered a question on Access World Forums (AWF) using the code, and I made a blog about it on my website here:-
Excel Sheets From Access Table OP "avalve" asked this question on Access World Forums:- "Howdy! Let me make a small scenario. Let's say I have a table with fields CarMake/CarModel/Year/Color. What I am trying to do is to export the table to a single Excel Workbook with each CarMake having their.
www.niftyaccess.com
This is a link to an article by Doug Steele where Doug explains what you can do with VBA and Excel in great detail:-
Doug Steele This month, Doug Steele looks at several techniques to use Automation from within Access to interact with Excel. By the time he's done, Doug has exported.
www.vb123.com.auA few years ago I was working on a project where I constantly was writing code to output Access data to Excel.
I realized that in 90% of these cases, I wanted to do the same things to the resulting Excel file, stuff like:
1) Bold headers, specific fonts
2) Column autofit - but then shrink super wide ones back to ~50
3) Freeze top row
4) Re-name tab
So I wrote a procedure that was re-usable. This is a good idea to do every time you identify something that you're going to have to do > once, like this, write code you can re-use as a function or sub with parameters.
I dug into my archives and found this that I used to use. Hope it helps or gives some ideas
Sub FormatExcelOutput(strExcelPath As String, lngWorksheetPos As Long, blLeaveOpen As Boolean) On Error GoTo errhandler Dim newapp As Object Dim wb As Object Dim rng As Object Dim ws As Object Dim lngLastCol As Long Dim x As Long Set newapp = CreateObject("excel.application") Set wb = newapp.workbooks.Open(strExcelPath) Set ws = wb.sheets(lngWorksheetPos) ws.Cells.wraptext = False ws.rows(1).Font.Bold = True ws.PageSetup.Orientation = 2 ws.PageSetup.Zoom = False ws.PageSetup.FitToPagesTall = False ws.PageSetup.FitToPagesWide = 1 ws.Columns.AutoFit lngLastCol = ws.Cells(1, ws.Columns.Count).End(-4159).Column For x = 1 To lngLastCol - 1 If ws.Columns(x).ColumnWidth > 35 Then 'reduce and wrap ws.Columns(x).ColumnWidth = 35 ws.Columns(x).wraptext = True End If Next x For Each rng In ws.usedrange 'this change was required due to jessica's request that i change the commission fields to text . 'apparently IsDate() thinks that "2.5" is a date. Ridiculous on the part of VBA, but also a very reckless 'change requested by the client.. Fortunately we were able to fix this one, I notified Susan of my stance on the issue 'and I suggest next time we not re-purpose an existing field with already data. If IsDate(rng) And IsNumeric(rng) = False Then 'If IsDate(rng) Then rng = Format(rng, "mm/dd/yyyy") rng.numberformat = "mm/dd/yyyy;@" End If rng.HorizontalAlignment = -4131 Next rng With ws.PageSetup .PrintTitleRows = "$1:$1" .PrintTitleColumns = "" End With With ws.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .PrintHeadings = False .PrintGridlines = True '.PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = 2 .Draft = False .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = True .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With If blLeaveOpen = True Then wb.Save newapp.Visible = True Else wb.Close (True) newapp.DisplayAlerts = False newapp.Quit End If Exit Sub errhandler: 'AnyProgressLabelFormName.Visible=False MsgBox "The following error has occurred in the function 'FormatExcelOutput': " _ & vbNewLine & vbNewLine & "If reporting this error, please STOP and " _ & "include a screenshot of this error" _ & vbNewLine & "as well as the entire screen/program" _ & vbNewLine & vbNewLine & "Error description: " & Err.Description _ & vbNewLine & "Error number: " & Err.Number, vbCritical, " " Exit Sub End Sub