Access Report to Excel Template

[MrDeeds]

I want to take a report from Access and push it to an Excel template. The Excel template has logos etc in rows 1-10. I want the Access report to begin data on Row 11. Then I want the Save as box to pop up to force user to save the file as an xls. So the template is always there to receive the Access data. Any ideas? Thanks.

 

[mr2driftking]

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.

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.

What about if the query/report takes in a user defined input value?

 

[mr2driftking]

What about if the query/report takes in a user defined input value?

is there a way to pass in the query parameter into the vba function call for the button?

 

[smz11]

just for debugging

replace the rng.resize line with

rng.value = vdata

After running the code there should be 1 cell filled with a value from the recordset.
Other, if you try what Norie suggested, you need to remark the next lines

vdata = .getrows()
.close

in the section where the recordset is opened.

Hi! I came upon this thread and happen to notice it described my scenario.

I have was able to do this and successfully retrieved 1 cell filled with a value from the record set,

replace the rng.resize line with
rng.value = vdata

But was unable to do,
Other, if you try what Norie suggested, you need to remark the next lines

vdata = .getrows()
.close

Can you show me where in the sample code that was provided to insert the above line? Thanks all!