saving access report using excel

supremer

New Member
Joined
Nov 1, 2011
Messages
2
Hi there,

I've been trying to save down access reports as PDFs programmably from excel vba, the access report is based on a parameter driven Access Query. The code runs fine if I run the .DoCmd.OpenReport without the where condition and the 'Enter Parameter Value' dialogue boxes would just pop up and I'd be able to enter the start and end dates and get the reports saved for the date range. However, if I have the where condition it would save a report with no what appears to be no data selected since the values are all zero.

(code)
Sub ReportSave(ByRef strReport As String)

Dim objAccess As Object
Dim objDB As Object
Dim strDest As String
Dim strDB As String
Dim strMonth As String
Dim startDate As String
Dim endDate As String

startDate = Format(Sheets("Settings").Range("B6").Value, "dd/mm/yyyy")
endDate = Format(Sheets("Settings").Range("B7").Value, "dd/mm/yyyy")

strDB = Sheets("Settings").Range("B2").Value
strDest = Sheets("Settings").Range("B3").Value
strMonth = Sheets("Settings").Range("B5").Value

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase strDB, False
'Use objAccess for the DoCmd

objAccess.DoCmd.OpenReport strReport, acViewReport, , "[Start Date]=#" & startDate & "# And [first day in new period]=#" & endDate & "#"

objAccess.DoCmd.OutputTo acOutputReport, , acFormatPDF, strDest & strReport & "_" & strMonth & ".pdf"

objAccess.Quit
Set objAccess = Nothing

End Sub
(code)
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
Welcome to the forum,

I have been using this code for a while which does bring the query data into Excel but it works a treat. See if you can use it.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> RunParameterQuery()<br><br><SPAN style="color:#007F00">'Step 1: Declare your variables</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> MyDatabase <SPAN style="color:#00007F">As</SPAN> DAO.Database<br><SPAN style="color:#00007F">Dim</SPAN> MyQueryDef <SPAN style="color:#00007F">As</SPAN> DAO.QueryDef<br><SPAN style="color:#00007F">Dim</SPAN> MyRecordset <SPAN style="color:#00007F">As</SPAN> DAO.Recordset<br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><br><SPAN style="color:#007F00">'Step 2: Identify the database and query</SPAN><br><SPAN style="color:#00007F">Set</SPAN> MyDatabase = DBEngine.OpenDatabase("M:\Access Files\Test ME Today.mdb")<br><SPAN style="color:#00007F">Set</SPAN> MyQueryDef = MyDatabase.QueryDefs("qryGoToExcel") <SPAN style="color:#007F00">'Query name in the database</SPAN><br><br><SPAN style="color:#007F00">'Step 3: Define the Parameters</SPAN><br><SPAN style="color:#00007F">With</SPAN> MyQueryDef<br>.Parameters("[Enter Department]") = Range("D3").Value <SPAN style="color:#007F00">'From parameter field in access</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br><SPAN style="color:#007F00">'Step 4: Open the query</SPAN><br><SPAN style="color:#00007F">Set</SPAN> MyRecordset = MyQueryDef.OpenRecordset<br><br><SPAN style="color:#007F00">'Step 5: Clear previous contents</SPAN><br>Sheets("Sheet1").Select<br>ActiveSheet.Range("A6:K10000").ClearContents<br><br><SPAN style="color:#007F00">'Step 6: Copy the recordset to Excel</SPAN><br>ActiveSheet.Range("A7").CopyFromRecordset MyRecordset<br><br><SPAN style="color:#007F00">'Step 7: Add column heading names to the spreadsheet</SPAN><br><SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> MyRecordset.Fields.Count<br>ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i - 1).Name<br><SPAN style="color:#00007F">Next</SPAN> i<br>Cells.EntireColumn.AutoFit<br><br>MsgBox "Query has been successful", vbInformation, "Sample"<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,421
Messages
5,596,033
Members
414,039
Latest member
southike

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top