saving access report using excel

[supremer]

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)

 

[Trevor G]

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>

 

[supremer]

Thanks! I think that should do it! ^^