Code to Print Excel Sheet from Access Module 2

[Quehay]

I have an export button to send a query into Access and would also like to automate printing that same sheet with the default printer.  I haven't found a code example yet, but guess it will need  1. check if XL is in RAM 2. open if not 3. use worksheets.print method.  Does anyone have expertise with this?  Thanks

 

[jimmythegeek]

Have you checked the TransferSpreadsheet Method in help.<br>This will export a query or a table as an .xls file which can be opened later.<br><br>If you have to actually open the spreadsheet and plug information into it, you have to use both VBA (both Access and Excel).<br><br>The TransferSpreadsheet is easier (code wise), cause it just takes a single line of code (There is also a macro command if you don't code). The VBA is flexible and cool to watch, but requires both some knowledge of Excel VBA and it's objects, properties and methods.<br><br>Let us know which you need to do. <p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development

 

[Quehay]

Thanks JtheG--I was psyched to get the notification that you had responded to the query!&nbsp;&nbsp;I have the transfer spreadsheet in place already and it works fine at getting the data in a sheet.&nbsp;&nbsp;What I'm trying to do is add more code--Excel objects--to give the user a one button export and print results in preformatted XL template option.&nbsp;&nbsp;This is a report that is more readable if it appears in a grid, hence the desire to have it come out in XL.&nbsp;&nbsp;It's no biggie to get users to open XL and then print the results but a &quot;one-click&quot; solution would allow me to swagger through the data entry cubicles like John Wayne.&nbsp;&nbsp;I'm not familiar enough with the Excel object structure yet (getobject or applicationobject?) and was hoping to borrow an existing code--seems like this would be a common procedure.&nbsp;&nbsp;Thanks again.

 

[famousb]

another option instead of the TransferSpreadsheet method is the OutputTo method, this works exactly the same as if you went through the toolbar and chose Analyze with Microsoft Excel - it produces a slightly cleaner, more readable format.&nbsp;&nbsp;It automatically can put the data in a preformatted template, and open Excel if you want. Here is an example of the code:<br><b><br>DoCmd.OutputTo acOutputQuery, &quot;<font color=red>YourQueryName</font>&quot;, &quot;Microsoft Excel (*.xls)&quot;, &quot;c:\&quot; & &quot;<font color=red>WhateverNameYouWant</font>&quot; & &quot;.xls&quot;</b><br><br>just change the red to fit your app. <br>then to print in Excel, you'd have to have Print Macro set up in there, and you could use Access to run that macro as well.&nbsp;&nbsp;Personally, I prefer not to automatically set things to print, it can be a waste of paper, that extra key stroke doesn't really bother me too much (unless you're doing alot of these). <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[Quehay]

Thanks Brian, I'll give it a shot--having Excel open would make the printing simple enough.

 

[jimmythegeek]

Following is an example of Code to Open Excel, and populate a particular template. You can actually select which file, sheets and cells to populate via VBA. This works well if you have a template that you want to fill in and save as a .xls file. That way you keep all the formatting of the template. <br><br>The code is lengthy, just paste the whole thing into a blank module, and then it will be easier to read (especially the comments). Obviously, this was designed to be used with the tables and data in my database, so you will have to modify it completely to work for you. This is just a sample of what is possible with VBA. If you need clarification, let me know.<br><br>==========<br>' Constants<br>Private Const XLS_LOCATION As String = &quot;C:\My Documents\Spreadsheets\Vault.xlt&quot;<br>Private Const XLT_LOCATION As String = &quot;C:\Windows\Vault.xlt&quot;<br>Private Const MC_START_ROW As Integer = 299<br>Private Const MC_END_ROW As Integer = 100<br>Private Const VISA_START_ROW As Integer = 999<br>Private Const VISA_END_ROW As Integer = 800<br><br>Public Sub PopulateExcel()<br>On Error GoTo Populate_Err<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim db As Database<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim rs As Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim objXL As Object, objSheet As Object, objRange As Object<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim strSaveAs As String, strVISA As String, strMC As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim x As Integer, intRow As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.Hourglass True<br>&nbsp;&nbsp;&nbsp;&nbsp;Set db = CurrentDb()<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' Set the SQL strings for the two recordsets that will be opened<br>&nbsp;&nbsp;&nbsp;&nbsp;strVISA = &quot;SELECT [Card Style], [Start Inventory] FROM [qryworking inventory start] WHERE [Plastic Type] = 'VISA'&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;strMC = &quot;SELECT [Card Style], [Start Inventory] FROM [qryworking inventory start] WHERE [Plastic Type] = 'MC'&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' Open, and make visible the Excel Template (Vault.xlt) which resides on the desktop<br>&nbsp;&nbsp;&nbsp;&nbsp;Set objXL = GetObject(XLT_LOCATION)<br>&nbsp;&nbsp;&nbsp;&nbsp;objXL.Application.Visible = True<br>&nbsp;&nbsp;&nbsp;&nbsp;objXL.Parent.Windows(1).Visible = True<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' Open the VISA recordset, and activate the VISA sheet in the template<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rs = db.OpenRecordset(strVISA, dbOpenSnapshot)<br>&nbsp;&nbsp;&nbsp;&nbsp;Set objSheet = objXL.worksheets(&quot;Visa&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;objSheet.Activate<br>&nbsp;&nbsp;&nbsp;&nbsp;rs.MoveFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;x = 4<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' Insert the data from the VISA recordset into the VISA worksheet<br>&nbsp;&nbsp;&nbsp;&nbsp;Do Until rs.EOF<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objXL.activesheet.cells(x, 1).Value = rs![Card Style]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objXL.activesheet.cells(x, 2).Value = rs![Start Inventory]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;x = x + 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rs.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;Loop<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' Delete all unnecessary rows making the VISA worksheet only as long as it needs to be<br>&nbsp;&nbsp;&nbsp;&nbsp;intRow = VISA_START_ROW<br>&nbsp;&nbsp;&nbsp;&nbsp;With objSheet<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.select<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Do Until intRow = VISA_END_ROW<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If .Range(&quot;A&quot; & intRow).Value = &quot;&quot; Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Set objRange = .Range(&quot;A&quot; & intRow & &quot;:B&quot; & intRow & &quot;:C&quot; & intRow & &quot;&quot; & intRow & &quot;:E&quot; & intRow _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& &quot;:F&quot; & intRow & &quot;:G&quot; & intRow & &quot;:H&quot; & intRow & &quot;:I&quot; & intRow & &quot;:J&quot; & intRow _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& &quot;:K&quot; & intRow & &quot;:L&quot; & intRow & &quot;:M&quot; & intRow & &quot;:N&quot; & intRow & &quot;:O&quot; & intRow & &quot;&quot; & intRow)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objRange.Delete 'Shift:=objXLUp<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;intRow = intRow - 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Loop<br>&nbsp;&nbsp;&nbsp;&nbsp;End With<br>&nbsp;&nbsp;&nbsp;&nbsp;rs.CLOSE<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' Open the MC recordset, and activate the MC sheet in the template<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rs = db.OpenRecordset(strMC, dbOpenSnapshot)<br>&nbsp;&nbsp;&nbsp;&nbsp;Set objSheet = objXL.worksheets(&quot;MC&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;objSheet.Activate<br>&nbsp;&nbsp;&nbsp;&nbsp;rs.MoveFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;x = 4<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' Insert the data from the MC recordset into the MC worksheet<br>&nbsp;&nbsp;&nbsp;&nbsp;Do Until rs.EOF<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objXL.activesheet.cells(x, 1).Value = rs![Card Style]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objXL.activesheet.cells(x, 2).Value = rs![Start Inventory]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;x = x + 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rs.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;Loop<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' Delete all unnecessary rows making the MC worksheet only as long as it needs to be<br>&nbsp;&nbsp;&nbsp;&nbsp;intRow = MC_START_ROW<br>&nbsp;&nbsp;&nbsp;&nbsp;With objSheet<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.select<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Do Until intRow = MC_END_ROW<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If .Range(&quot;A&quot; & intRow).Value = &quot;&quot; Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Set objRange = .Range(&quot;A&quot; & intRow & &quot;:B&quot; & intRow & &quot;:C&quot; & intRow & &quot;&quot; & intRow & &quot;:E&quot; & intRow _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& &quot;:F&quot; & intRow & &quot;:G&quot; & intRow & &quot;:H&quot; & intRow & &quot;:I&quot; & intRow & &quot;:J&quot; & intRow _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& &quot;:K&quot; & intRow & &quot;:L&quot; & intRow & &quot;:M&quot; & intRow & &quot;:N&quot; & intRow & &quot;:O&quot; & intRow & &quot;&quot; & intRow)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objRange.Delete<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;intRow = intRow - 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Loop<br>&nbsp;&nbsp;&nbsp;&nbsp;End With<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' Calculate totals on spreadsheet<br>&nbsp;&nbsp;&nbsp;&nbsp;objXL.Application.calculate<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' Set the save string, and save the spreadsheet<br>&nbsp;&nbsp;&nbsp;&nbsp;strSaveAs = &quot;C:\Windows\Desktop\&quot; & Format(DATE, &quot;mmddyyyy&quot & &quot;.xls&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;objXL.SaveCopyAs strSaveAs<br><br>&nbsp;&nbsp;&nbsp;&nbsp;' Quit Excel<br>&nbsp;&nbsp;&nbsp;&nbsp;objXL.Application.displayalerts = False<br>&nbsp;&nbsp;&nbsp;&nbsp;objXL.Application.Quit<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set objXL = Nothing<br>&nbsp;&nbsp;&nbsp;&nbsp;Set objSheet = Nothing<br>&nbsp;&nbsp;&nbsp;&nbsp;Set objRange = Nothing<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rs = Nothing<br><br>Populate_Exit:<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.Hourglass False<br>&nbsp;&nbsp;&nbsp;&nbsp;Exit Sub<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>Populate_Err:<br>&nbsp;&nbsp;&nbsp;&nbsp;MsgBox Err.Number & &quot;: &quot; & Err.Description<br>&nbsp;&nbsp;&nbsp;&nbsp;GoTo Populate_Exit<br>End Sub<br>========== <p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development

 

[Quehay]

Thanks for the code Jim.&nbsp;&nbsp;It looks like just the thing, with some str substitutions.&nbsp;&nbsp;This would be a worthy FAQ submission!