natedawg1415
New Member
- Joined
- Mar 1, 2017
- Messages
- 1
Here is some code you can use.
Replace the template name and query name in the code and set the starting range (now A1).
This will create a new workbook based on your template (which actually doesn't need to be a real template, you can use any excel file) and then prompts the user to save the file after pasting the data in the disered range.
Code:Public Sub CreateExcelInfo() 'Set reference to Microsoft Excel Object library 'Set reference to Microsoft ActiveX DataObject 2.x Const sFileNameTemplate As String = "PathAndNameOfYourTemplateXLSX" Dim oExcel As New Excel.Application Dim WB As New Excel.Workbook Dim WS As Excel.Worksheet Dim rng As Excel.Range Dim objConn As New ADODB.Connection Dim objRs As New ADODB.Recordset Dim vData As Variant Dim sSQL As String Set objConn = CurrentProject.Connection sSQL = "Select * from qTheQuery" 'This has to be the name of the query your report is using to display data With objRs .Open sSQL, objConn, adOpenStatic, adLockReadOnly vData = .GetRows() .Close End With With oExcel .Visible = True 'Create new workbook from the template file Set WB = .Workbooks.Add(sFileNameTemplate) With WB Set WS = WB.Worksheets("Sheet1") 'Replace with the name of actual sheet With WS Set rng = .Range("A1") 'Starting point of the data range rng.Resize(UBound(vData, 2) + 1, UBound(vData, 1) + 1).Value = oExcel.WorksheetFunction.Transpose(vData) End With End With .Quit End With 'clean up Set oExcel = Nothing Set objRs = Nothing Set objConn = Nothing Set vData = Nothing End Sub
You can create a button on the report to trigger this code.
When I do this I get a Run-time Error 13 type mismatch. Could this be to me using office 2016? when I click the debug icon it highlights the line "rng.Resize(UBound(vData, 2) + 1, UBound(vData, 1) + 1).Value = oExcel.WorksheetFunction.Transpose(vData)" What could cause this?